Search

Flat File Source incorrectly parsing data when using Comma as the column delimiter by Eric Flores

Active

6
0
Sign in
 to vote
Type: Bug
ID: 127114
Opened: 5/12/2006 12:04:36 PM
Access Restriction: Public
Primary Feedback Item: 127244
0
Workaround(s)
7
User(s) can reproduce this bug
Flat File source is incorrectly parsing out data where it is using comma as a column delimiter.    This could also affect other delimiters if those delimiters are used as part of the data. I have attached the screen shot of what the Flat File Source does with my case.
Details (expand)
Product Language
English
Version
SQL Server 2005 - Developer Edition (32)
Category
DTS
Operating System
Windows XP SP2 Professional
Operating System Language
English
Steps to Reproduce
Create a Data flow task where the source is a flat file. This flat file must have a row of data where a comma is part of the data and not used as a column delimiter. The flat file source must use comma as a column delimiter.
Example of data
"City","State"
"""KAILUA KONA,HI""","CA"
Actual Results
Using " as a text qualifer
The city column = ""KAILUA Kona
The State Column = HI""

Data : CA
pushed to the next column
Expected Results
city column = "KAILUA KONA, HI"
State column = CA
Platform
 
File Attachments
0 attachments
Sign in to post a comment.
Posted by thisisfutile1 on 10/20/2011 at 9:34 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 Joseph Leathlean on 5/20/2010 at 11:55 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:38 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 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 ChiefSoftwareNoobie on 6/12/2009 at 10:54 AM
Still open? Come on. Excel and Access can handle this.
Posted by ManServ on 8/14/2008 at 3:39 PM
RFC 4180 still not achieved with SQL 2008 RTM. You may also vote here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281398
Posted by Microsoft on 12/5/2007 at 1:13 PM

Thank you for your submission. This is an update to notify you that this issue has been closed. We appreciate your input and support of the SSIS product.
Posted by Microsoft on 5/24/2006 at 12:20 PM
This is by design behavior because you have embedded qualifiers. We're considering improving our text file handling in a future release.
Sign in to post a workaround.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement