Import issue for CSV file with quotes around all data fields

Hello!

I have a CSV file that encloses all the data fields with quotation marks. Here is a sample:

"08/01/2007","3","021200012","123","0.03"

Is there any way in SSIS that I can tell the Flat File wizard to ignore the quotation marks? I don't want to import the quotes in the database since that will really mess up other applications that need to use the data.

Thanks in advance,

Harry

I'm running into the same issue. I was going to post and I saw your posting. Though mine is slightly different. I have several fields on my .csv file, some fields have quotes some do not and some data values have quotes and some do not. So my line looks like this:

NISSAN,"NEW", "Smith, John"

or some look like this

NISSAN, NEW, Smith, Michelle

So i need something to remove the quotes as well so I don't see them in my table, only the data

|||

Got the answer...put the " (quotes) in the text qualifier instead of the default of <none>.

|||

Big H wrote:

Got the answer...put the " (quotes) in the text qualifier instead of the default of <none>.

I have that but because not all of my data is surrounded by quotes, its still failing for me on the insert into the table

|||

Hi,

You can put a script component and then clean the fields that you need to clean by using any string methods.

Hope that helps

Cheers

Rizwan

|||

How? I'm new to this SSIS process and I'm learning as I go. So how would a script componet 'clean' the fields?

|||

IGotyourdotnet wrote:

How? I'm new to this SSIS process and I'm learning as I go. So how would a script componet 'clean' the fields?

Isn't all of your "text" data surrounded by quotes? "Proper" CSV formatting would have text fields surrounded by quotes and numeric fields not surrounded by quotes.

If you can, move away as fast as you can from CSV files. It's a terrible format, especially if you run into situations where you have embedded quotes in your text fields. Tab delimited or fixed width are better alternatives. Or even XML.

|||

Phil Brammer wrote:

IGotyourdotnet wrote:

How? I'm new to this SSIS process and I'm learning as I go. So how would a script componet 'clean' the fields?

Isn't all of your "text" data surrounded by quotes? "Proper" CSV formatting would have text fields surrounded by quotes and numeric fields not surrounded by quotes.

If you can, move away as fast as you can from CSV files. It's a terrible format, especially if you run into situations where you have embedded quotes in your text fields. Tab delimited or fixed width are better alternatives. Or even XML.

Isn't all of your "text" data surrounded by quotes? No, its generated by another process (I believe an Oracle process)and the SSIS (former DTS) packages grabs the files and inserts the data into the SQL tables.

|||

IGotyourdotnet wrote:

Isn't all of your "text" data surrounded by quotes? No, its generated by another process (I believe an Oracle process)and the SSIS (former DTS) packages grabs the files and inserts the data into the SQL tables.

But *some* of your text data has quotes?|||

correct, it could be all of it at times or some of it at times. So I could see it like

row 1 NISSAN, Smith John, "NEW"

row 2 NISSAN, "Smith Michelle", NEW

or

row 1 "NISSAN", "Smith John", NEW

row 2 "NISSAN", Smith John, NEW

or

row 1 "NISSAN", "Smith" John, NEW"

row 2 "NISSAN", "Smith John", "NEW"

so far I've see all of the above in this file.

|||

You should be able to use a Derived Column transform to strip the quotes off.

Code Snippet

REPLACE([Column 0],"\"","")