INF: Using BCP When Data is in Quotation Marks (132463)

MORE INFORMATION

You can customize the BCP format file to strip off quotation marks that surround character strings in your ASCII data file. This process requires modifying the terminators to include a backslash and 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:

Example 1

For the data file:

"XX","BB","CC"
"TT","QA","RD"

When all of the columns have a character data type, and the data is in quotation marks, use the following BCP-format file (BCP.FMT):

4.2
7
1       SYBCHAR       0       0       "\""      0       dummy1
2       SYBCHAR       0       2       "\""      1       col1
3       SYBCHAR       0       0       ",\""     0       dummy2
4       SYBCHAR       0       2       "\""      2       col2
5       SYBCHAR       0       0       ",\""     0       dummy3
6       SYBCHAR       0       2       "\""      3       col3
7       SYBCHAR       0       0       "\r\n"    0       dummy4
				

Example 2

For the data file:

"XX",20,"CC"
"TT",30,"RD"

When the second column is type integer, the remaining columns have a character data type, and the data is in quotation marks for the character data type. Use the following BCP-format file:

4.2
7
1       SYBCHAR       0       0       "\""      0       dummy1
2       SYBCHAR       0       2       "\""      1       col1
3       SYBCHAR       0       0       ","       0       dummy2
4       SYBCHAR       0       2       ","       2       col2
5       SYBCHAR       0       0       "\""      0       dummy3
6       SYBCHAR       0       2       "\""      3       col3
7       SYBCHAR       0       0       "\r\n"    0       dummy4
				

Note the exact column delimiter for the dummy variables in both the examples.

The first line of the BCP.FMT refers to the version of the BCP utility. When running either version 1.1 or 1.11 of BCP, this value is 4.0. In SQL Server version 4.2, this value is 4.2. In SQL Server version 6.0, this value is 6.0. In SQL Server version 6.5, this value is 6.0.

For similar examples with Microsoft SQL Server version 4.2, please see the following article(s) in the Microsoft Knowledge Base: