The file I am attempting to bulk insert has " " around all the text
fields, Bulk Insert is trying to insert the double quotes with the
text, and the error message says:

Error: Bulk Insert fails. Column is too long in the data file for row
1, column 1. Make sure the field terminator and row terminator are
specified correctly. (State:37000, Native Code: 1302)


Any help would be greatly appreciated

(robp***@gmail.com) writes:
As any other tool, BULK INSERT does what you tells it to. If you say
that you have a text file with comma as field delimiter, that means a
file with comma as field delimiter, and quotes are obviously not part
of the text.

If the format is something like this:

   "a field","another field","next field",2,2

you need to use a format file. A format file for the above would look
like, assuming that field order in file agress with column order:

   8.0
   6
   1 SQLCHAR 0 0 "\""     0 "" ""
   2 SQLCHAR 0 0 "\",\""  1 col1 ""
   3 SQLCHAR 0 0 "\",\""  2 col2 ""
   4 SQLCHAR 0 0 "\","    3 col3 ""
   5 SQLCHAR 0 0 ","      4 col4 ""
   6 SQLCHAR 0 0 "\r\n"   5 col5

If the first field is not quoted, you should skip that first line, which
defines an empty dummy field, to get the first quote out of the way.

Note that the column names in the file does not matter, it's the column
numbers that count, and 0 means that that field is not imported.

If you want to know more gory details, search for posts from me and BCP
or "format file" in the text on Google news.

Or if you don't want to use a format file as Erland suggested, you can try
the DTS Import/Export Wizard to import the csv file to your table. The wizard
can handle the double quoatation marks correctly if you specify the data
source to be Text File, and the text qualifier to tbe Double Quote. Most
likely, as soon as you pick the text file as the data source, SQL Server (or
rather the Import wizard) can correctly pick the text qualifier for you.

Linchi

Show quoteHide quote

SQL Server 2005 Management Studio vs. 2000 Enterprise Manager/Query Analyzer
SIMPLE command to convert string to number? Not CAST or CONVERT.
Modifying Data Directly through Studio Manager
Table Truncated (Urgent)
SQL 2000 Database corrupt