|
|
Posted by Erland Sommarskog on 08/07/06 22:24
Ted (r.ted.byers@rogers.com) 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@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
Navigation:
[Reply to this message]
|