KB132463 - INF: Using BCP When Data is in Quotation Marks

Article ID: 132463

Article Last Modified on 11/14/2003

Microsoft SQL Server 4.21a Standard Edition
Microsoft SQL Server 6.0 Standard Edition
Microsoft SQL Server 6.5 Standard Edition

This article was previously published under Q132463

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:

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

Additional query words: sql6 quotes Windows NT

Keywords: KB132463