Re: On to Bulk Insert issues
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Mon, 7 Aug 2006 22:24:08 +0000 (UTC)
Ted (r.ted.byers@xxxxxxxxxx) writes:
One limitation I encountered is that there doesn't seem to be a way to
tell MS SQL Server that the fields are optionally enclosed by quotes.
That is, text fields are enclosed by quotes while, e.g., numeric
fields, are not, and that these optional quotes are NOT to be included
in the data in the fields.
That's correct, if optionally means just optionally, so that you
could have:
9;Some unquoted data;12;9.234;2004-12-12
19;"Some quoted data";-12;31.4;2003-02-23
But if a text column is consistently quoted, you can handle this with a
format file where you specify each field. A format file that fits the
second row in the example above could look like:
8.0
5
1 SQLCHAR 0 0 ";\"" 1 col1 ""
2 SQLCHAR 0 0 "\";" 2 col2 ""
3 SQLCHAR 0 0 ";" 3 col3 ""
4 SQLCHAR 0 0 ";" 4 col3 ""
5 SQLCHAR 0 0 "\r\n" 5 col3 ""
The first row is the version of the file format. Next is the number of
fields in the file. Following lines describe one field each.
First column is record number. Second column is data type of the field
in the file. For a text file this is always SQLCHAR or always SQLNCHAR
for a Unicode file. Other data types are only used with binary formats.
The third column is prefix-length, used only for binary files. Fourth
column is the length, and is used for fixed-length fields. Fifth field
is the terminator, and it is here you specify the quotes.
Six column is the database column, with 1 denoting the first column. 0
means that this field is not to be imported. Seventh column is the
column name, but it's informational. BCP/BULK INSERT does not use it.
Last colunm is the collation for the data in the file.
Overall, keep in mind that BCP/BULK INSERT reads a binary file and a
row terminator is really only the terminator for the last field.
I do not know what "State 1" vs "State 8" is supposed to mean.
You can consider it as white noise. The state number may tell the
SQL Server developers something, but they are not documented.
The table in question in this example allows nulls in several columns,
and in the flat file, nulls are represented by consecutive tabs. Might
this be causing trouble for the Bulk Insert statement?
That should work fine. However, if fields are missing, so that you
have six fields on one line, and eight on the next, you lose.
--
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
.
- Follow-Ups:
- Re: On to Bulk Insert issues
- From: Ted
- Re: On to Bulk Insert issues
- From: panic attack
- Re: On to Bulk Insert issues
- References:
- On to Bulk Insert issues
- From: Ted
- On to Bulk Insert issues
- Prev by Date: Re: Understanding constraints and binding
- Next by Date: SQL Server 2005 + SQL Server Express
- Previous by thread: On to Bulk Insert issues
- Next by thread: Re: On to Bulk Insert issues
- Index(es):
Relevant Pages
|
