Re: Bulk Insert
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sun, 20 Aug 2006 22:24:00 +0000 (UTC)
(robpatt@xxxxxxxxx) writes:
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)
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.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
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
.
- References:
- Bulk Insert
- From: robpatt
- Bulk Insert
- Prev by Date: Re: MS Access "Upsizing" Tools in SQL Server
- Next by Date: Re: SQL Server 2005 Management Studio vs. 2000 Enterprise Manager/Query Analyzer
- Previous by thread: Bulk Insert
- Next by thread: Re: MS Access "Upsizing" Tools in SQL Server
- Index(es):
Relevant Pages
|
