SSIS Bug when handling CSV Files with commas embedded in quoted strings
My group and I have been wrangling with this for a couple of days, and we're stumped.Is the csv snippet "This string", 1234, "This, another string", "thatstring" has been throwing errors that look like it is using the comma inside the quoted string "This, another string" as a delimiter, and causing the row to grow by one field.We've looked on the web, and apparently this is a common problem.I just find it hard to believe that something so major is still a bug.We're using SQL 2005 SP1 btw.Help,Doug
July 31st, 2008 6:07pm
Double click Flat File Connection Manager, input "Text qualifier" as ". This can guarantee anything between "" will stay its original meaning.
Free Windows Admin Tool Kit Click here and download it now
July 31st, 2008 7:02pm
Already tried that, the problem persists.You haven't heard of this problem before?
August 1st, 2008 3:30pm
Could you make a sample data and package and send email to me? I will take a look.
And, upgrade to sp2 may be a good choice.
Free Windows Admin Tool Kit Click here and download it now
August 2nd, 2008 6:19pm
Yes. I have. Its apparently a bug. I have seen it posted as a bug in other threads. Here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2384028&SiteID=1
What I would like to know now, is, where can we go to see the actual list of bugs AND something we can show our bosses/clients... I would like to point them to something to read because they're beginning to look at me as if I cant do the work.They want to verify what I am telling them about SQL 2005(SSIS) and its inability to handle simplistic tasks without me having to code several extra hours of "file pre processing" that should (was) already in the product.
This is very frustrating and isnt a great way of being introduced to the new technology. I held off moving to "dot net" for a long time because what I had been using (VB6/SQL 2000) was working perfectly fine and I am going to go back to what works.
I mean, Excel has a checkbox that asks "treat consecutive delimiters as one?". So then why doesnt SSIS? These are from the same company, no? Ugh.
August 6th, 2008 5:14pm
Maybe you can try this one: http://www.microsoft.com/downloads/details.aspx?FamilyID=B51463E9-2907-4B82-A353-E15016486E1D&displaylang=en
Overview
UnDouble is an SSIS dataflow component that:- Removes quotes that bracket input strings if present.- Replaces doubled quotes with single quotes inside the string.The SSIS flat file adapter doesn't process qualified strings where double quotes are used to indicate a single embedded quote.This transform takes a text column, and, removes bracketing quotes if present, plus replaces double quotes inside the text with sinqle quotes.SSIS users that want to duplicate DTS 2000 flat file processing can call their output column unqualified, then place this transform to clear up the quotes.The conversion is performed in-place. Select the desired string column on the "Input ColumnsTab" of the advanced editor, then change its Usage Type to READWRITE.Included in this install is 4columns.txt, a file that contains a variety of quote/character combinations. Use a flat file source to feed this data to the UnDouble component. This component is part of a series of components that illustrate increasingly complex behavior, each one exercising a greater proportion of the SSIS object model. If studying in order, this component follows SeeBuffer, and precedes ValidUnDouble.
And here is another workaround:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1513072&SiteID=1
Free Windows Admin Tool Kit Click here and download it now
August 6th, 2008 5:21pm
Thanks for that! I was in the process of writing a script that would do something similar.I also looked at the other components and downloaded some that might be ofa bitof help.
As is often the case, its just a matter of finding the answer somewhere. Do you knowof any downloadable component, somewhere, that would allow for the importing of a variable record length text file? I have a file which has records that vary in length (127 bytes or 323 bytes) depending.. I wrote my own script to process it but was wonder if there is a download as I like to see other ways of handling it too..
Thanks!
August 6th, 2008 5:58pm
I totally agree with your frustrations as I am having the same problem. Incidentally the Microsoft Access Import wizard deals with embedded quotes just fine, so Microsoft are not being consistent.-- Patrick
Free Windows Admin Tool Kit Click here and download it now
September 24th, 2008 5:02pm
This is still a bug. SQL2008 SP1
Sigh.
Michael
April 4th, 2011 12:51pm
This is still a bug. SQL2008 SP1
Sigh.
Michael
Free Windows Admin Tool Kit Click here and download it now
April 4th, 2011 12:51pm
I don't think anyone has addressed the original question. which is about the embedded comma, not the embedded text qualifier. You can remove/replace embedded text qualifiers, but I am having the problem described by the first poster. I have an
SSIS job which imports a CSV file with embedded commas within qualified text strings. Works fine when started from Visual Studio. But when deployed to SQL Server 2008, the same package completely ignores the text qualifiers and treats embedded
commas as field delimiters. I posted a question on this today at
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/77da8178-cd5d-46aa-8843-70882329333c.
Nearly 3 years later, 2 new releases of SQL Server, and still the same problem! Actually is it 6 years since SQL Server 2005 came out?
I get the impression no-one at Microsoft actually cares about these long-standing problems with SSIS having such a noddy (or shoddy) approach to processing CSV files. You cannot actually import a standard specification CSV file, out of the box.
To succeed, it seems your CSV file must have:
No embedded text qualifiers, no embedded field delimiters, no embedded row delimiters, no line breaks in records. Forget ragged records also. So if you have of these features in your CSV file, you have to develop a "workaround".
DTS, MS Office products, even Visual Studio can make a better job of this, so it's not as if these are new methods.
Faisel
May 11th, 2011 6:32pm
I am encountering the exact same issue, a comma embedded in an address field is interpreted as a field delimiter, thus throwing an exta column in my output CSV file causing other programs that deal with this output file to choke. Hard to believe it is still
a bug in SQL Server 2008 R2 and especially after posts on this issue dating back to 2008Jagannathan Santhanam
Free Windows Admin Tool Kit Click here and download it now
August 13th, 2011 12:22pm
I am encountering the exact same issue, a comma embedded in an address field is interpreted as a field delimiter, thus throwing an exta column in my output CSV file causing other programs that deal with this output file to choke. Hard to believe it is still
a bug in SQL Server 2008 R2 and especially after posts on this issue dating back to 2008
Jagannathan Santhanam
Ditto, so irritating.
August 27th, 2011 8:58am