How to use comma as both column delimiter and text qualifier

0
votes

I am loading a flat file into a SQL database. The flat file is comma delimited. Some of the column values have comma without being encapsulated in double quotes (for e.g - HPPV,TYRE). Now, when I try to use comma as text qualifier, I get a message saying column delimiter and text qualifier cannot be the same.

I want to somehow use comma as text qualifier so that the flat file keeps the value - HPPV,TYRE as on single entity - HPPVTYRE or HPPV TYRE, instead of spilling it over to the next column.

Is there any way we can use comma as text qualifier, it already being a column delimiter ?????

1
ssisssis-2012
Doesn't make sense. Can you not work with whoever supplied this file to you for them to provide an alternative version where they've done something sane? Text delimiters are meant to be used in pairs and are used to indicate that within such a pair, the comma delimiter should not be interpreted as such. If they were the same character, how could you, or I, or a computer, possibly know, when it encounters such a character, either "this is a comma delimiter that is being protected by the text delimiters" or "this is the end of a text delimited section". – Damien_The_Unbeliever
Or to put it another way, I'll assume your proposal works. Here's some data for 3 columns, one of which contains an embedded comma - aaa,bbb,ccc,ddd. If you can tell me which of the columns contains the embedded comma, please explain clearly how that works. – Damien_The_Unbeliever
How big is your source file? How about splitting the source file into good lines and bad lines? Create a source file connector that reads in the entire line. Create a Dataflow with a Flatfile Source that uses this new connection to read the source flatfile as lines. Use a Derived Column to add a CommaCount column LEN([LineColumn]) - LEN(REPLACE([LineColumn],",","")), use a conditional split to send any lines having CommaCount > 10 to a new flatfile destination. Then process these two files differently. – Troy Witthoeft

1 Answers

0
votes

No, I don't think so but I searched and found this article that might help.