|
|
Posted by Erland Sommarskog on 08/09/06 21:57
Ted (r.ted.byers@rogers.com) writes:
> I used bcp to produce the apended format file.
>
> How can it be modified to recognize the quotes that surround the text
> fields and not insert the quotes along with the text? Invariably, the
> first four columns have text surrounded by quotes and are terminated by
> tabs. If the first column has "abc", only abc ought to be inserted
> into that field in the table.
What you have in your post is a format file for native format, and
that's not what your into. Below I repeat the quick guide to format
files, that I posted a few days ago.
And, yeah, you had a format file in XML which Microsoft seem to think
we should use these days. But there is no new functionality in the
new format what I have been able to find, so I stick to the old format.
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.
--
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]
|