Flat File with double quotes as text qualifire, comma delimiter and doube quotes in data

Question

Flat File with double quotes as text qualifire, comma delimiter and doube quotes in data

only4mithunc

SSCrazy Eights

Points: 8434
More actions
December 8, 2018 at 2:45 am

#409369

Dear All,
I have flat file that I need to load to SQL. This file is having a text qualifie double quotes, it is having a column separator as comma, and also it has double quotes as part of the data itself.
Example: Name, ID, Note are the three columns, and the data is coming as
"abc" , "1001", " This ID was "well" maintained"
Here Name and ID columns are fine, but in teh Note colum, there is double quote in the text itself. The final resul what I need in table is abc , 1001, This ID was "well" maintained.
Though I have tried using colum delimiter as comma and text qualifier as double quote, as the data also having double quote the DFT is failiing. Have anyone came across this scenario?
Thanks & Regards,
MC

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16162 More actions · Answer 1

this has been discussed over and over again both in this forum and elsewhere

as that is not a valid CSV file SSIS will not process it correctly.
Ask whoever is generating the file to you to generate it correctly and you will be fine - otherwise there is not easy way to process it.
In some cases it can be done but not without c# or SQL code. In other cases there is no way to ensure the correct results

that line should be generated as
"abc" , "1001", " This ID was ""well"" maintained"

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 2

Thanks for the respose. However, here this is not a problem with file generation, this is valid text. i.e we can expect double quotes in the text which is coming in the Note column, and the requirement is to load the text along withat that double quotes which is part of the text.

Thanks & Regards,
MC

frederico_fonseca SSCoach Points: 16162 More actions · Answer 3

yes - but as your file format is incorrect it needs fixing.

if the quotes are escaped (with another quote) then the file will load correctly, and the final text will contain the double quote you need as is on the input.

CSV format is quite clear on that - if the source is not generated correctly that is where it needs to be fixed.

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 4

Sorry, I didnt get when you said file format is incorrect... Are you saying the source file should have created without double quotes for the columns, i.e with no text qualifier?
Like : abc , 1001, This ID was "well" maintained

Thanks & Regards,
MC

frederico_fonseca SSCoach Points: 16162 More actions · Answer 5

no. what I said, and gave the example, is that any double quote that is part of the source text needs to be escaped with another quote

"abc" , "1001", " This ID was "well" maintained" -- wrong as the inside double quotes are not escaped
"abc" , "1001", " This ID was ""well"" maintained" -- correctly escaped double quotes

so "well" should be ""well"" in order to be correctly loaded

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 6

Thanks for clarifying it. So that means this escape sequence needs to be done at the source end while generating the file itself, and no other solution to laod it correctly if the file received is in this format?
"abc" , "1001", " This ID was "well" maintained"

Thanks & Regards,
MC

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 7

Unfortunately, the above solution with escape using another double quotes like "" Well "" didn't help.
For ow what I did is, in the advanced property of the flat file mapping , for the NOTE field alone I have made the text qualifier as "False" , so that data got loaded to the table as " This ID was "well" maintained" , and then I did an SQL task to remove the first and last double quotes from the data. In this case the final data will appear as This ID was "well" maintained.
I don't think my above solution is the best, but my issue got resolve with this approach. If anyone else got some different idea, then please suggest.

Thanks & Regards,
MC

frederico_fonseca SSCoach Points: 16162 More actions · Answer 8

you changed what you didn't have to change.

you stated that you had already picked " as the text delimiter - that needs to be set
once set and once you change the input data to have the escaped quote it will work.

Edit: your solution will work until such time at the text field also contains a comma - and then it will fail as you didn't fix the source, and didn't specify that the columns had a quote as text delimiter

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 9

frederico_fonseca - Saturday, December 8, 2018 6:19 PM
you changed what you didn't have to change.
you stated that you had already picked " as the text delimiter - that needs to be set
once set and once you change the input data to have the escaped quote it will work.
Edit: your solution will work until such time at the text field also contains a comma - and then it will fail as you didn't fix the source, and didn't specify that the columns had a quote as text delimiter

I always wonder at the insistence of 'fixing' the problems at the destination instead of requiring the source systems to provide correctly formatted files and data. It seems to me that there is a lot of wasted time and effort building 'solutions' to problems that are driven by receipt of badly formatted files - instead of spending that time and effort on insuring correctly formatted data is received.

Invariably - more time and effort are spent on fixing new issues in the files because the sender makes another invalid change. It becomes a constant process of modifying code to pre-process the files before they can be loaded...when the simplest fix is to have the sender fix the issue.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs