Monday, March 26, 2012

noobie question on CSV Flat Files

Hi there,

I am connecting to a CSV file. When I look at the raw CSV, most of rows look something like this:

"19056","CD","Rick James"
These rows work fine but I run it to trouble when some rows look like this in the raw form:

"134530","Poster","Elizabeth on the set of "Giant", 1955"

You can see the issue that will arrise, SSIS parses this as 4 columns. Note: In the "Flat File Connection Manager" I have the "text qualifier" set to ".

Any suggestions as to how I might process files that have this type of situation? Maybe some sort of pre-process is necessary?

Other than that I've had great success with the other components and I am enjoying SSIS very much, great product!

Flat File Source does not support Embedded Qualifiers. Parsing columns that contain data with text qualifiers will fail at run time.You can use the Flat File Source to load the data as unqualified data and use a script component later to strip off the qualifiers downstream. You can also write a custom component to do the same. There is an Undouble component available for download from

http://www.microsoft.com/downloads/details.aspx?FamilyID=B51463E9-2907-4B82-A353-E15016486E1D&displaylang=en

which you can use.

|||

Thanks Ranjeeta,

This is kind of what I figured would be the case. I'll ask the publisher of the CSV to perhaps rethink their practice of embedded qualifiers. If I have no luck with that, I'll have to do something similar to what you're describing. Thanks for the link!

No comments:

Post a Comment