Re: BCP import

Tech-Archive recommends: Speed Up your PC by fixing your registry

(blindsey@xxxxxxxxxx) writes:
Trying to import a csv file using BCP.

It's comma-separated and each data-value is enclosed in double-
quotation marks, which BCP insists on importing as part of the field.

How can I tell BCP to ignore the surrounding double-quotation marks?

You need to use a format file where you specify the double quotes as
part of the delimiters. Assuming that the first column is enclosed by
double-quotes, you need a little trick that originally learnt from former
SQL Server MVP Linda Weirzbiecki. Say that you have this table:

CREATE TABLE demo (a varchar(23) NOT NULL,
b varchar(23) NOT NULL,
c int NOT NULL,
d char(8) NOT NULL)

In the data file, the integer column is not enclosed in quotes. The format
file should look like this:

8.0
5
1 SQLCHAR 0 0 "\"" 0 "" ""
2 SQLCHAR 0 0 "\",\"" 1 a ""
3 SQLCHAR 0 0 "\"," 2 b ""
4 SQLCHAR 0 0 ",\"" 3 c ""
5 SQLCHAR 0 0 "\"\r\n" 4 d ""

What happens here is that you define to have a fifth initial empty field
which you don't import, by specifying a 0 in the column-number field of
the format file. (1 stands for the first column and so on. The field for
column names is ignored.)

Note that if the quotes are not used consistently, you are out of luck.
Also, if the data includes "" that should be interpreted as a single
double quote, BCP will import it as "", that is two double qoutes. BCP
is a program that reads a stream or bytes, and is not text-centered tool.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: On to Bulk Insert issues
    ... tell MS SQL Server that the fields are optionally enclosed by quotes. ... format file where you specify each field. ... Second column is data type of the field ... SQL Server developers something, ...
    (comp.databases.ms-sqlserver)
  • Re: On to Bulk Insert issues
    ... in what situations does the sql server 2000 return with such an error? ... tell MS SQL Server that the fields are optionally enclosed by quotes. ... format file where you specify each field. ... row terminator is really only the terminator for the last field. ...
    (comp.databases.ms-sqlserver)
  • Re: BCP Troubles
    ... The SQL server we are tying to BCP into is SQL 2000. ... > way I got it to work was to run BCP and have it create a ForMaT file. ... So it sounds like the first time BCP is not executed at ...
    (comp.databases.ms-sqlserver)
  • Re: BCP and CSV - my conclusions
    ... For a start, numeric fields should not have quotes around them, so ... your format file is going to have to be crafted so that each field ... Secondly, any double-quotes characters ... BCP is a fairly squared tool. ...
    (microsoft.public.sqlserver.tools)
  • Re: BCP Troubles
    ... But please put the sample data and format file in a zip ... > file first prior to doing the BCP in.. ... BCP's error messages are sometimes very obscure. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)