INF: Using BCP When Data is in Quotation Marks

  - Microsoft SQL Server, versions 4.21a, 6.0 and 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

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:

   ARTICLE-ID: Q73182
   TITLE     : INF: Using BCP When Data Is Within Quotation Marks