Home Dashboard Directory Help
Search

SSIS flat file parser does not read Column delimiters embedded in text data by Toofan


Status: 

Closed
 as Fixed Help for as Fixed


54
1
Sign in
to vote
Type: Bug
ID: 282396
Opened: 6/11/2007 1:18:22 PM
Access Restriction: Public
Primary Feedback Item: 281398
4
Workaround(s)
view
21
User(s) can reproduce this bug

Description

The SSIS natively and with the added components undouble and undoubleout does not recognise embedded column delimiters in text. For example, if " (double quote) is the text delimiter and , (comma ) is the column delimiter the following data :

"Hello ""Hello,Hello"" ","Hello ""Hello,Hello"" "

should be read as two fields : Hello "Hello,Hello" and Hello "Hello,Hello"

How ever, SSIS provided undouble does not recognise the comma in the text content and discards the row. Please fix the undouble to handle this.
Details
Sign in to post a comment.
Posted by Lee Everest on 1/8/2014 at 8:29 AM
Didn't see any comments, but wante to add that 281398 shows that this was fixed in Denali, and I just ran into this issue and it works perfectly in SQL Server 2012 (using Data Tools).

Tnx
Posted by thisisfutile1 on 10/20/2011 at 9:33 AM
Checkpoint: October 20th, 2011

Created simple Excel 2010 spreadsheet...3 columns, 3rd column contains many words and commas in the cell.
"Save as" CSV (looking at file in Notepad, everything looks JUST like a CSV should look and how they've always looked for many, many decades. 1st 2 columns do NOT have double-quotes around the data because they do NOT contain commas, the 3rd columns does have double-quotes because of the existance of commas...remember this file was generated by Microsoft)
In SQL Server 2008 R2 express > Import wizard > flat file souce > "Delimited" > in "Text Qualifier" setting I've put a double-quote because I have comma's in that 3rd column.
Import process fails.

In SQL 2005 it used to say something like, "Text qualifiers are not supported"...yet it was a configurable setting in the wizard (go figure...at that time we all thought "BUG")
In SQL 2008 it now chokes because the other columns do not have a text qualifiers. SO, I added them, but it chokes on something else (frankly, I don't care what or why...it's failing where SQL 2000 and ALL OTHER DATABASES will import flat files WITH a text qualifier defined.

This is database 101. I hate to sound like those anti-MS ranters out there on the interwebs, but dang it, it sounds like you'd prefer that I use Excel (XLS) files as the source (because those actually work pretty well, although they have their issues too, but that's a topic for another day).

PLEASE, OH PLEASE fix this!
Posted by thisisfutile1 on 10/20/2011 at 9:33 AM
@lynamc

I think what you meant to say was "this appears to workAROUND..."

Frankly, we're not interested.
Posted by lynamc on 6/28/2011 at 7:22 AM
This appears to work...

http://geekswithblogs.net/sathya/articles/how-to-import-and-export-csv-files-directly-in-ssis.aspx
Posted by Krishna Nalluri on 5/16/2011 at 6:43 PM
These little things will definitely hurt Microsoft.
Posted by Joseph Leathlean on 5/20/2010 at 11:49 PM
SQL 2008 R2 RTM also still does not handle this correctly - submitted a new bug: https://connect.microsoft.com/SQLServer/feedback/details/560592/flat-file-connection-manager-not-handling-text-delimiters-in-csv-files

Please go and vote on it...
Posted by ManServ on 4/4/2010 at 1:36 PM
Microsoft posted an update that this will be fixed in the next "major" version in the comments section: https://connect.microsoft.com/SQLServer/feedback/details/281398/sql-2008-flat-file-provider-still-broken
Posted by Stephen Mills on 1/6/2010 at 4:24 PM
I have to admit I don't get Microsoft's attitude. I can understand ignoring a request that they spot in some random forum, but this is a random forum. By ignoring the Developers and Administrators that post bugs here, they are saying that they don't care. If they keep this up, it will bite them. It shouldn't be ALL about the next version with cool features. They need to make sure the current features they have work and if they don't make fixing them a higher priority than the new features. Enterprise customers want all the cool features, but they NEED the features they have to work.

I hope the eventually learn to respect their customers and what the customer needs and wants. If they do this, it will probably reduce the number of new features in new versions slightly, but have customers who are much happier.
Posted by BBrown9876 on 12/30/2009 at 7:27 AM
I see Microsoft has not responded to this recently, the last time was almost 2 years ago. please make the visual basic code available.
Posted by BBrown9876 on 12/10/2009 at 11:25 AM
I see this issue --- "The flat file parser does not support embedding text qualifier in data" is still a problem. I have been trying to obtain code to work around this and so far all I can find is just highlevel workaround like: you can use a script task, or a data flow. Is there more details available and the code also. Please help. Thanks you so much
Posted by hbz2000 on 11/25/2009 at 2:23 PM
This is horrifying. We all know that the folks at Microsoft are not morons, but this is moronic, inexcusable, defective, broken, ...

FIX THIS! It's not that hard to do.
Posted by A.Lockwood on 10/13/2009 at 12:22 PM
Very annoying. Unsurprisingly it's CSV output from *nix systems that SSIS can't import properly. When it's faster to write my own import utility in .NET than use SSIS it just makes me sad.
Posted by ManServ on 8/11/2009 at 11:40 AM
SQL Server 2008 R2 August CTP Flat File Parser still isn't capable of importing RFC 4180 compliant csv files. Also Unicode supplementary characters and line breaks aren't supported with UTF8 files. (Flat File Parser cannot import files with embedded text qualifiers http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=312164)
Posted by Adrian Liles on 6/19/2009 at 8:38 AM
This is really a big blunder from microsoft, how can they take such a big backwards step. This kind of import is very common and to have a bug like really makes my life hard, it is very poor.
Posted by ChiefSoftwareNoobie on 6/12/2009 at 11:07 AM
This is absurd. Its still open.
Posted by BetterToday on 8/27/2008 at 4:27 AM
This bug renders the Flat File Connection Manager feature useless. If I have to write my own source/destination filter, then what's the benefit of SSIS anyway??
Posted by ManServ on 8/14/2008 at 3:35 PM
As announced by Microsoft they didn't manage to fix this in the last 3 years with SQL 2008 RTM.
Posted by Jedak on 7/28/2008 at 11:09 AM
This needs to be fixed. How can you break functionality the worked correctly in a previous version (SQL Server 2000 DTS) and then consider your new product (SSIS) ready for release?!?!? My company uses this and receives files like this all the time.
Posted by Microsoft on 2/20/2008 at 3:25 PM
Please use feedback ID 312164 (http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=312164) to trackt latest progress on this feature request.
Posted by Microsoft on 2/20/2008 at 2:08 PM
Even though we are unable to address this for our upcoming Katmai release, we are planning to provide package samples that use script component to parse flat file data that contains column delimiters as text data.

We will update you once these samples are either in a released CTP or are available on CodePlex.com.

Thanks,
SSIS Team
Posted by ManServ on 12/3/2007 at 1:35 PM
Defective by design. Microsoft isn't able to fix the main text import driver for 2 releases (SQL 2005 an 2008) that was working before (in SQL 2000 DTS)!?
Posted by Microsoft on 12/3/2007 at 12:42 PM
Thanks for your feedback on this issue. This is an excellent suggestion and we believe that it will certainly make the product easier to use. We have tracked this problem to a more generic issue. We are looking to provide a correction as soon. However, we are not able to include this change in the upcoming Katmai release. I have moved the issue to the next phase so that it will be reviewed at that time. Thanks again for your submission.
Posted by bcm6435 on 6/21/2007 at 3:34 PM
We deal a lot with flat files and this is a huge deal in processing those. The undouble workaround is cumbersome at best. I also have the problem of the flat file writer not writing the proper escape sequences, all it does is wrap the field in double quotes. Can anyone else confirm that?
Posted by djpacman on 6/14/2007 at 12:44 PM
I am experiencing this issue as well. The frustrating thing is that this worked correctly in DTS 2000, but no longer works in SSIS.
Posted by Toofan on 6/14/2007 at 6:19 AM
Please change category to DTS from Tools.
Sign in to post a workaround.
Posted by bjtaz on 12/16/2009 at 12:10 PM
switch to oracle!!
Posted by Vladimir9853536 on 8/26/2008 at 12:53 PM
Another work around is to try to use DTS under Legacy in SQL 2005..
All packages, which I cannot convert to SSIS because of that, I saved there, working same way as on SQL 2000..
Posted by alexdon on 3/18/2008 at 4:13 PM
Thanks for the work-around Chick_litt - worked for me to

The only comment I have is in relation to the file location requirement - the Drive and folder location is in relation to the SQL Server you are running on, not the local machine. This is an issue in some corporate environments where the database and file systems are managed separately.
Posted by nwhitfield on 3/13/2008 at 9:30 AM
I was able to use OPENROWSET to query the file directly from the database.
create TABLE <target table> (col001 varchar(255)...col00n varchar(255) )

INSERT INTO <target table> (col001, col002..n)

SELECT * FROM
OPENROWSET ('msdasql',
'driver={microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\Mydirectory\;Extensions=CSV;',
'SELECT * FROM MyFile.csv' )