ID Number: Q73182
1.10 1.11 4.20
OS/2
Summary
This article describes how to customize the BCP (bulk copy program)
format file to strip off unwanted quotation marks around the character
strings in the data file.
More Information:
It is possible to customize the BCP format file to strip off quotation
marks that surround character strings in the user's ASCII data file.
This process requires modifying the terminators to include a
backslash+quotation mark (\") to interpret the quotation mark as a
literal. If the first field in the data file is within quotation marks,
you must add a new line to the BCP format file to dispose of the first
quotation mark.
This example explains how to use BCP in the data file to exclude
the quotation marks:
Data File
---------
"John","Doe",33,"New York"
"Jane","Doe",31,"Seattle"
Format File
-----------
4.0
4
1 SYBCHAR 0 12 "," 1 F_NAME
2 SYBCHAR 0 12 "," 2 L_NAME
3 SYBCHAR 0 4 "," 3 AGE
4 SYBCHAR 0 12 "\r\n" 4 BIRTH_PLACE
Perform the numbered steps below to modify the format file to
remove the quotation marks:
1. Increment the data field counter (line two of the format file) and
the field number designators (numbers just below) by one. Then add
a new first data description line to remove the first quotation mark:
4.0
4+1
1 SYBCHAR 0 1 "" 0 FIRST_QUOTE <== NEW LINE
1+1 SYBCHAR 0 12 "," 1 F_NAME
2+1 ...
2. Next, modify the terminators to include quotation marks:
Data Terminator Appearance in Format File
---- ---------- --------------------------
abc","abc "," "\",\""
abc",123 ", "\","
123,"abc ," ",\""
abc" "<EOL> "\"\r\n"
The final version of the BCP format file will resemble the following:
4.0
5
1 SYBCHAR 0 1 "" 0 FIRST_QUOTE
2 SYBCHAR 0 12 "\",\"" 1 F_NAME
3 SYBCHAR 0 12 "\"," 2 L_NAME
4 SYBCHAR 0 4 ",\"" 3 AGE
5 SYBCHAR 0 12 "\"\r\n" 4 BIRTH_PLACE
Note: The first line of the BCP.FMT refers to the version of the
program. When running the version 1.1 or 1.11 of BCP this value is
4.0. In SQL Server 4.2 this value is 4.2.
Additional reference words: quotes BCP