INF: Using BCP When Data is in Quotation Marks

Article translations Article translations
Article ID: 132463 - View products that this article applies to.
This article was previously published under Q132463
Expand all | Collapse all

On This Page

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

Properties

Article ID: 132463 - Last Review: November 14, 2003 - Revision: 3.0
APPLIES TO
  • Microsoft SQL Server 4.21a Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
Keywords: 
KB132463
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com