|
|
Posted by Erland Sommarskog on 12/29/05 00:22
(ckirby@mindspring.com) writes:
> I spoke too soon. The name field isn't uniform, some records have the
> double quotes around it, with a comma splitting the name, others don't,
> so those are throwing the columns off by one.
That's when you need a format file. The good news is that the format file
will drop your column headers as well.
The format file for the full file is kind of boring, so I only give an
example for a file with 5 fields, where the first is quoted, and the rest
is not. (Indendation is for formatting of the post only. All text starts
in column 1.)
8.0
6
1 SQLCHAR 0 0 "\"" 0 "" ""
2 SQLCHAR 0 0 "\"," 1 col1 ""
3 SQLCHAR 0 0 "," 2 col1 ""
4 SQLCHAR 0 0 "," 3 col1 ""
5 SQLCHAR 0 0 "," 4 col1 ""
6 SQLCHAR 0 0 "\r\n" 5 col1 ""
The trick here is that we define the file as having six field - there is
an empty field before the first quote. SQLCHAR is the datatype in the
file, and since this is a text file, everything is SQLCHAR. (Or SQLNCHAR
if it is a Unicode file.) The next two columns are for binary files,
and fixed-length fields. The comes the field terminator. Notice that
BCP does not really have a notion of a row terminator; the row terminator
is just the field terminator for the field.
The column that reads 0 1 2 3 4 5 is the mapping to the table columns.
0 means that that field is not imported. 1 is the first field etc. Next
column is for table-column names, but that's informational only. Finally,
the last column is where you can specify collation.
Note that the first record as far as BCP is concerned, consists of the
column headers + the first data row. The first field of the first row,
is everything up to the first ".
--
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]
|