INFO: Using BCP When Data Is Within Quotation Marks (73182)

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:

1. 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  ...
   
   						

2. 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: