BCP import - remove quotes from CSV File

My bcp process is now working. FINALLY.
But, my import data file is comma-delimited and everything is surrounded
by quotes; therefore, they data goes into the SQL databases with quotes.
Any suggestions on how I can remove these quotes, either before,during
or after the BCP process?
Sample CSV file:
"204980","33404","Cindy W
Crutcher","O","502-839-9822","A","502-680-9822","H","502-839-5679","4295
00535","Exit Realty Crutcher Team","502-839-9822","53501","Mark
Crutcher","502-839-9822","Residential","05/17/02","","","186900.00","05/
21/02
*** Sent via Developersdex http://www.examnotes.net ***As you specified the column delimiter as “comma”, you can specify the te
xt
qualifier as “quotes”. It will solve the problem.
"Joey Martin" wrote:

> My bcp process is now working. FINALLY.
> But, my import data file is comma-delimited and everything is surrounded
> by quotes; therefore, they data goes into the SQL databases with quotes.
> Any suggestions on how I can remove these quotes, either before,during
> or after the BCP process?
> Sample CSV file:
> "204980","33404","Cindy W
> Crutcher","O","502-839-9822","A","502-680-9822","H","502-839-5679","4295
> 00535","Exit Realty Crutcher Team","502-839-9822","53501","Mark
> Crutcher","502-839-9822","Residential","05/17/02","","","186900.00","05/
> 21/02
> *** Sent via Developersdex http://www.examnotes.net ***
>|||How would I do that with the bcp function? Or is it something that I
would do later?
*** Sent via Developersdex http://www.examnotes.net ***|||If you need to use the bcp you should create a bcp format file.
something like the following:-
6.0
4
1 SQLDATETIME 0 8 "\t" 1 ErrorDate
2 SQLCHAR 0 255 "\t" 3 ErrorMsg
3 SQLCHAR 0 255 "\t" 2 ErrorCaption
4 SQLCHAR 0 255 "\n" 4 ErrorClass
The problem is on the bcp utility you are allowed to only to specify the
field delimiter using [/t field_term] option.
I think the best way to do it to create a DTS package transfer the data from
your csv file to a table on SQL server. On the DTS package you can specify
the text
qualifier as “quotes”. Then you have an option to save it as a DTS packa
ge
or VBS.
I think the DTS is better than creating the bcp format file.
-Nader
"Joey Martin" wrote:

> How would I do that with the bcp function? Or is it something that I
> would do later?
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Joey Martin (joey@.infosmiths.net) writes:
> My bcp process is now working. FINALLY.
> But, my import data file is comma-delimited and everything is surrounded
> by quotes; therefore, they data goes into the SQL databases with quotes.
> Any suggestions on how I can remove these quotes, either before,during
> or after the BCP process?
> Sample CSV file:
> "204980","33404","Cindy W
> Crutcher","O","502-839-9822","A","502-680-9822","H","502-839-5679","4295
> 00535","Exit Realty Crutcher Team","502-839-9822","53501","Mark
> Crutcher","502-839-9822","Residential","05/17/02","","","186900.00","05/
> 21/02
As Nader said, you need to use a format file. Here is a sample of how it
would look like:
8.0
4
1 SQLCHAR 0 0 "\"" 0 "" ""
2 SQLCHAR 0 0 "\",\"" 1 col1 ""
3 SQLCHAR 0 0 "\",\"" 2 col2 ""
4 SQLCHAR 0 0 "\"\n" 3 col3 ""
This is fitted for a CSV file with three fields like:
"Field1","Field2","Field3"
The trick is that we define the file as having four fields. The first
field is just an empty dummy which we don't import. This is why it says
0 in the database-column field.
Also in a format file, what counts is the column numbers. Column names
are just informational, and not used by BCP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx