Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

BCP import - remove quotes from CSV File

2,241 views
Skip to first unread message

Joey Martin

unread,
Nov 30, 2005, 1:29:50 PM11/30/05
to

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.developersdex.com ***

Nader Shahin

unread,
Nov 30, 2005, 1:41:02 PM11/30/05
to
As you specified the column delimiter as “comma”, you can specify the text
qualifier as “quotes”. It will solve the problem.

Joey Martin

unread,
Nov 30, 2005, 2:08:01 PM11/30/05
to
How would I do that with the bcp function? Or is it something that I
would do later?

Nader Shahin

unread,
Nov 30, 2005, 4:16:04 PM11/30/05
to
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 package
or VBS.

I think the DTS is better than creating the bcp format file.

-Nader

Erland Sommarskog

unread,
Dec 1, 2005, 5:09:10 AM12/1/05
to

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, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0 new messages