Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jimmyjazz1978
Starting Member
11 Posts |
 Posted - 2012-05-04 : 11:52:12
|
| SSIS falls flat on it back with this scenario .In my flat file, we have Normal looking records like this "1","2","STATUSCHANGED","A","02-MAY-12 21:52:34","","Re","Initial review",""And some like this ; ( record spread over several lines ) "1","2","SALESNOTIFICATIONRESPOND","Ac","02-MAY-12 21:55:19","From: W, Jason Sent: Wednesday, May 08, 2012 2:00 PMTo: XXXX, A; AcostSubject: RE: Notification Id 1219 - Qu ID XXXXXX I got this from earlier today. Our team is reviewing the request. Thanks, Hi,This account belongs to J, please approve/deny.Thanks!Claud","","","Reassign"So looking at the file in NOTEPAD + which is amazing it shows me that within that field that is spread over several line, I should take out all the {CR}{LF} in that field. The row delimiter for this file is LF and the text qualifier is “. So 2 things I need to do on a collection of 200 file ?1. Remove all the {CR}{LF} in the file ?2. Remove any embedded “ in the actual fields as “ is the text qualifier ?Anyone have any idea how to do this in windows , dos or vba for such a large number of files so its automated ?Bizzare |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-04 : 12:06:02
|
| It's probably easier to change your delimiters to something else. My personal preference would be to use tabs instead of commas. Since (I assume) every column is quote delimited, you can replace "," with tabs.For the row delimiter, I would assume that each row ends with "{CR}{LF}, and if that's the case then use that combination as row delimiter. I'm not sure how well SSIS can manage this, but bcp or BULK INSERT should have no trouble.The only problem is the leading quote mark on each line. If you can edit the file then simply remove it.If you know VBScript or Powershell this is a pretty easy script to write. I used to do stuff like this using sed (a UNIX utility that's available for Windows). I don't recommend it though as it's rather archaic if you don't know regular expressions. If you do, it's pretty handy and easy. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-04 : 18:22:05
|
| i would have done this using BULK INSERT with format file to specify correct delimiters for the columns------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
How to deal with Weird records in FLAT FILE ?