INF: Using BCP When Data Is Within Quotation Marks

ID: Q73182

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.2
Microsoft SQL Server version 4.2x

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

It is possible to customize the BCP format file to strip off quotation marks that surround character strings in the user's ASCII data file. This process requires modifying the terminators to include a backslash+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:

Data File

"John","Doe",33,"New York"
"Jane","Doe",31,"Seattle"

Format File


4.0
4
1    SYBCHAR   0    12   ","       1    F_NAME
2    SYBCHAR   0    12   ","       2    L_NAME
3    SYBCHAR   0    4    ","       3    AGE
4    SYBCHAR   0    12   "\r\n"    4    BIRTH_PLACE

Perform the numbered steps below to modify the format file to remove the quotation marks:

Increment the data field counter (line two of the format file) and the field number designators (numbers just below) by one. Then add a new first data description line to remove the first quotation mark:
```
      4.0
      4+1
      1    SYBCHAR   0    1    ""        0    FIRST_QUOTE <== NEW LINE
      1+1  SYBCHAR   0    12   ","       1    F_NAME
      2+1  ...
 
```

Next, modify the terminators to include quotation marks:


      Data           Terminator     Appearance in Format File
      ----           ----------     --------------------------

      abc","abc      ","            "\",\""
      abc",123       ",             "\","
      123,"abc       ,"             ",\""
      abc"           "<EOL>         "\"\r\n"

The final version of the BCP format file will resemble the following:


   4.0
   5
   1    SYBCHAR   0    1    ""        0    FIRST_QUOTE
   2    SYBCHAR   0    12   "\",\""   1    F_NAME
   3    SYBCHAR   0    12   "\","     2    L_NAME
   4    SYBCHAR   0    4    ",\""     3    AGE
   5    SYBCHAR   0    12   "\"\r\n"  4    BIRTH_PLACE

NOTE: The first line of the BCP.FMT refers to the version of the program. When running the version 1.1 or 1.11 of BCP this value is 4.0. In SQL Server 4.2 this value is 4.2.

For similar examples with Microsoft SQL Server version 4.21a, 6.0 and 6.5, please see the following article(s) in the Microsoft Knowledge Base:

Q132463 : INF: Using BCP When Data is in Quotation Marks

Additional query words: 4.20 quotes Windows NT

Keywords : kbtool SSrvBCP
Version : 4.2
Platform : OS/2 WINDOWS
Issue type :