Posted by Ted on 08/09/06 18:32

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.

Thanks

Ted

==================format file========================
<?xml version="1.0" ?>
- <BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
- <RECORD>
<FIELD ID="1" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="6"
COLLATION="Latin1_General_CI_AI" />
<FIELD ID="2" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="7"
COLLATION="Latin1_General_CI_AI" />
<FIELD ID="3" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="48"
COLLATION="Latin1_General_CI_AI" />
<FIELD ID="4" xsi:type="NativeFixed" LENGTH="4" />
<FIELD ID="5" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="8"
COLLATION="Latin1_General_CI_AI" />
<FIELD ID="6" xsi:type="NativePrefix" PREFIX_LENGTH="1" />
</RECORD>
- <ROW>
<COLUMN SOURCE="1" NAME="f_supplier_code" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="2" NAME="f_product_code" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="3" NAME="f_product_name" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="4" NAME="f_asset_classes_id" xsi:type="SQLINT" />
<COLUMN SOURCE="5" NAME="f_size" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="6" NAME="f_dist_unit" xsi:type="SQLFLT8" />
</ROW>
</BCPFORMAT>

• Next in forum: Re: Tying together tables
• Prev in forum: Re: Using Union and Join in a single view
• Thread view: format files for use with bulk insert

[Reply to this message]