INF: Using BCP When Data is in Quotation Marks

ID: Q132463

The information in this article applies to:

Microsoft SQL Server versions 4.2x, 6.0, 6.5

SUMMARY

This article describes how to customize the bulk copy program (BCP) format file to strip off unwanted quotation marks around the character strings in the data file.

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:

Q73182 : INF: Using BCP When Data Is Within Quotation Marks

Additional query words: sql6 quotes Windows NT

Keywords : kbtool SSrvBCP SSrvWinNT
Version : 4.21a 6.0 6.5
Platform : WINDOWS
Issue type :