Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Problems importing CSV files using SSIS. Expand / Collapse
Author
Message
Posted Tuesday, April 19, 2011 10:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 7,097, Visits: 12,601
asso.al-hamawandi (4/18/2011)
I had the same problem in the SSIS 2008 data flow package . I had a CSV (comma delimited) source data with one of the columns data quoted with a double quote( " ). The import worked fine using the SQL 2000 DTS import as it recognized the Text Qualifier value as a Double Quote {"}, but this was not the case when I started to use the SSIS 2008. The Text Qualifier shows <None> on the Flat file connection manager object (Text Qualifier) property. I tried to change the <None> to what I have in the DTS 2000 Double Quote {"}, but it didn't fix the problem. I kept researching on this until I found the simple answer to fix this is to put only (") in the Text Qualifier Property and this worked fine and I got rid of the double quotes in the data and the data imported successfully as it was in the source even with the data which contained comma in between.

You find below the link where I found my answer from:

http://www.mssqltips.com/tip.asp?tip=1316

You're not serious right? Did you even read this thread? Try processing the example row in the original post using the method outlined in the article and you'll soon realize the underlying issue.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1095807
Posted Saturday, October 22, 2011 11:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 10:44 PM
Points: 2, Visits: 94
I just spent a whole weekend on this problem. My CSV file contains text data contained within double quotes, eg "blah",1,1/10/2011,"blah" and numeric and date data. All fields are delimited by a comma as dictated by the CSV file format. If your field contains a comma but is enclosed by double quotes, then you must specify (") in the Text Qualifier box on the Flat Form Connection Manager Editor form....BUT, if you have already experienced problems with the file import, then you must delete the flat file connection and then create a new instance, otherwise no matter what you do, SSIS will not recognise the text qualifier. It works!!
Post #1194909
Posted Sunday, October 23, 2011 6:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:56 AM
Points: 36,959, Visits: 31,470
John Kotuby (4/13/2011)
So much for the rant.


I'd post a rant of my own but you did a pretty good job of it.

I'm trying to find it but there used to be a "standard" written a long time ago about true Comma Separate Value files including when and how text identifiers where used. It all boiled down to what we all know and what Excel can do. Things with embedded quotes or delimiters must be encapsulated in quotes and the embedded quotes must be doubled up.

It's a real shame they did away with the "Jet Drivers" when MS went to 64 bit. I believe you can recover some of the lost functionality by downloading the "Ace" drivers. I don't have a URL for the MS download for those... should take someone only a minute or two of Googling to find it.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1194999
Posted Tuesday, October 25, 2011 9:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 3:13 PM
Points: 147, Visits: 658
BUT, if you have already experienced problems with the file import, then you must delete the flat file connection and then create a new instance, otherwise no matter what you do, SSIS will not recognise the text qualifier. It works!!


Makes me wonder how many times I've furiously deleted and started over something I "know" should work... and then it does!
Post #1195788
Posted Thursday, December 1, 2011 2:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 9:50 AM
Points: 1, Visits: 13
Thanks for pointing out the need to only use " rather than "" - saved me many hours of frustration
Post #1214997
Posted Wednesday, September 12, 2012 1:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 25, 2013 6:15 PM
Points: 1, Visits: 15
Thanks. This solved my problem!
Post #1358190
Posted Wednesday, January 22, 2014 1:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 1:51 PM
Points: 1, Visits: 1
namakakiweyho (10/22/2011)
I just spent a whole weekend on this problem. My CSV file contains text data contained within double quotes, eg "blah",1,1/10/2011,"blah" and numeric and date data. All fields are delimited by a comma as dictated by the CSV file format. If your field contains a comma but is enclosed by double quotes, then you must specify (") in the Text Qualifier box on the Flat Form Connection Manager Editor form....BUT, if you have already experienced problems with the file import, then you must delete the flat file connection and then create a new instance, otherwise no matter what you do, SSIS will not recognise the text qualifier. It works!!


Awesome dude thanks for posting this!
Post #1533814
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse