INF: Using BCP When Data Is Within Quotation Marks

Last reviewed: June 16, 1997
Article ID: Q73182

The information in this article applies to:

  - Microsoft SQL Server versions 4.2 for OS/2
  - Microsoft SQL Server versions 4.2

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:

   ARTICLE-ID: Q132463
   TITLE     : 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

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 16, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.