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],"\"","")

Import Data from Text file

I am importing data from a text file. The file is simple, only 9 fields.
Each field has quotation marks around the data. During the import it
creates a table with varchar fields and length of 8000!!
The only problem with this is that I merge this data and other inforation to
a permenant table with normal field sizes. With this I start getting errors
that the data will be truncated.
Second problem. The code below:
--
Declare @.TableName nvarchar(10)
Declare @.SQLCmd nvarchar(1000)
set @.TableName = 'RS201'
Set @.SQLCmd = '
Insert Into MasterJobLog
(RSID,
Code,
DateStamp,
DocumentName,
Pages,
Cost,
Client,
PatronID,
Printer,
DocumentType)
Select '+ Char(39)+ @.TableName + char(39) + ' ,
replace(Code,Char(34),Null),
replace(DateStamp,Char(34),Null),
replace(DocumentName,Char(34),Null),
replace(Pages,Char(34),Null),
replace(Cost,Char(34),Null),
replace(Client,Char(34),Null),
replace(PatronID,Char(34),Null),
replace(Printer,Char(34),Null),
replace(DocumentType,Char(34),Null)
From ' + @.TableName
print @.Sqlcmd
--
The code produces:
Insert Into MasterJobLog
(RSID,
Code,
DateStamp,
DocumentName,
Pages,
Cost,
Client,
PatronID,
Printer,
DocumentType)
Select 'RS201' ,
replace(Code,Char(34),Null),
replace(DateStamp,Char(34),Null),
replace(DocumentName,Char(34),Null),
replace(Pages,Char(34),Null),
replace(Cost,Char(34),Null),
replace(Client,Char(34),Null),
replace(PatronID,Char(34),Null),
replace(Printer,Char(34),Null),
replace(DocumentType,Char(34),Null)
From RS201
This works with the Print statement.
However when used with the Execute statement, I get:
---
Server: Msg 203, Level 16, State 2, Line 31
The name '
Insert Into MasterJobLog
(RSID,
Code,
DateStamp,
DocumentName,
Pages,
Cost,
Client,
PatronID,
Printer,
DocumentType)
Select 'RS201' ,
replace(Code,Char(34),Null),
replace(DateStamp,Char(34),Null),
replace(DocumentName,Char(34),Null),
replace(Pages,Char(34),Null),
replace(Cost,Char(34),Null),
replace(Client,Char(34),Null),
replace(P...
---
It truncates the SQL string.
Any Ideas!
ArthurI have the solution.
Apparently the contructed SQL command is to long for the execute to handle
with one variable. The on-line books said to break it up into 2 variables
and concatenate the 2 command strings. Thus, Execute (@.Sqlcmd1 + @.SQLCmd2).
This works! So I think that the 8000 byte field size is affecting this.
"Arthur C" <arthur.christy@.tamut.edu.delete.me> wrote in message
news:OaWJoJ$lDHA.2424@.TK2MSFTNGP10.phx.gbl...
> I am importing data from a text file. The file is simple, only 9 fields.
> Each field has quotation marks around the data. During the import it
> creates a table with varchar fields and length of 8000!!
> The only problem with this is that I merge this data and other inforation
to
> a permenant table with normal field sizes. With this I start getting
errors
> that the data will be truncated.
> Second problem. The code below:
> --
> Declare @.TableName nvarchar(10)
> Declare @.SQLCmd nvarchar(1000)
> set @.TableName = 'RS201'
> Set @.SQLCmd = '
> Insert Into MasterJobLog
> (RSID,
> Code,
> DateStamp,
> DocumentName,
> Pages,
> Cost,
> Client,
> PatronID,
> Printer,
> DocumentType)
> Select '+ Char(39)+ @.TableName + char(39) + ' ,
> replace(Code,Char(34),Null),
> replace(DateStamp,Char(34),Null),
> replace(DocumentName,Char(34),Null),
> replace(Pages,Char(34),Null),
> replace(Cost,Char(34),Null),
> replace(Client,Char(34),Null),
> replace(PatronID,Char(34),Null),
> replace(Printer,Char(34),Null),
> replace(DocumentType,Char(34),Null)
> From ' + @.TableName
> print @.Sqlcmd
> --
> The code produces:
> Insert Into MasterJobLog
> (RSID,
> Code,
> DateStamp,
> DocumentName,
> Pages,
> Cost,
> Client,
> PatronID,
> Printer,
> DocumentType)
> Select 'RS201' ,
> replace(Code,Char(34),Null),
> replace(DateStamp,Char(34),Null),
> replace(DocumentName,Char(34),Null),
> replace(Pages,Char(34),Null),
> replace(Cost,Char(34),Null),
> replace(Client,Char(34),Null),
> replace(PatronID,Char(34),Null),
> replace(Printer,Char(34),Null),
> replace(DocumentType,Char(34),Null)
> From RS201
> This works with the Print statement.
> However when used with the Execute statement, I get:
> ---
> Server: Msg 203, Level 16, State 2, Line 31
> The name '
> Insert Into MasterJobLog
> (RSID,
> Code,
> DateStamp,
> DocumentName,
> Pages,
> Cost,
> Client,
> PatronID,
> Printer,
> DocumentType)
> Select 'RS201' ,
> replace(Code,Char(34),Null),
> replace(DateStamp,Char(34),Null),
> replace(DocumentName,Char(34),Null),
> replace(Pages,Char(34),Null),
> replace(Cost,Char(34),Null),
> replace(Client,Char(34),Null),
> replace(P...
> ---
> It truncates the SQL string.
>
> Any Ideas!
> Arthur
>