INF: Using BCP When Data Is Within Quotation Marks

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