January 5, 2009 at 7:29 am
Is there any way to tell SSIS to ignore text qualifiers which are not followed by column delimiters...
There is an odd application which generates a CSV File like:
"ID 2";"A description of ID 2";"A long description in a MEMO field where people always have to use words in "quotes" because they "look" cool";"2345 as an order number"{LF} Next Record...
Using SSIS I would like to import this Flat File into an SQL Table.
I cannot script it (or I hope I dont have to) I just want to get the data in to do some data analysis with SQL. I cannot use that crappy SQL Access provides me with. Am I just to stupid or has 2005 a problem with that and SQL2000 did not?
Any help would be greatly appreciated!
Cheers
January 5, 2009 at 7:31 am
Could a MOD shift this into SQL2005 topic? sorry for that
January 6, 2009 at 9:56 am
>>Is there any way to tell SSIS to ignore text qualifiers which are not followed by column delimiters...
I've banged my head on the keyboard for this quite a lot. I never found a way to do it but came up with a workaround:
I preprocess the text files and replace any quote that is not preceded by the separator (and that isn't the first quote in the line) with a set token (I use the HTML encoded quote character, your mileage may vary). Next I load the file with SSIS and finally do a replace on the string to put the proper quotes back in.
You can do this entirely through SSIS or just write a WSH script to preprocess the files (I've used both depending on circumstances).
Hope that helps you,
-Darren
January 11, 2009 at 5:58 am
that's really great. thank you a lot! it worked fine.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply