Character Format bcp

The -c parameter  (or char value for the DATAFILETYPE clause of the BULK INSERT statement) uses the character (char) data format for all columns, providing tabs between fields and a newline character at the end of each row as default terminators. Storing information in character format is useful when the data is used with another program, such as a spreadsheet, or when the data needs to be copied into Microsoft® SQL Server™ from another database. Character format tends to be used when copying data from other programs because they have the functionality to export and import data in plain text format.

For example, the command to bulk copy the publishers table in the pubs database to the Publ.txt data file using character format is:

bcp pubs..publishers out publ.txt -c -Sservername -Usa -Ppassword

The contents of the Publ.txt file:

To use field and row terminators other than the default provided with character format, specify the following.

For example, the command to bulk copy the publishers table in the pubs database to the Publ.txt data file using character format, with a comma as a field terminator and the newline character (\n) as the row terminator is:

bcp pubs..publishers out publ.txt -c -t , -r \n -Sservername -Usa -Ppassword

The contents of the Publ.txt file:

0736,New Moon Books,Boston,MA,USA

0877,Binnet & Hardley,Washington,DC,USA

1389,Algodata Infosystems,Berkeley,CA,USA

1622,Five Lakes Publishing,Chicago,IL,USA

1756,Ramona Publishers,Dallas,TX,USA

9901,GGG&G,München,Germany

9952,Scootney Books,New York,NY,USA

9999,Lucerne Publishing,Paris,France

Important Using character mode, bcp, by default, always converts characters from the data file to ANSI characters before bulk copying them into SQL Server, and converts characters from SQL Server to OEM characters before copying them to the data file. This can cause loss of extended character data during the OEM to ANSI or ANSI to OEM conversions. To prevent loss of extended characters, use Unicode character format, or specify a code page for the bulk copy operation using -C (or the CODEPAGE clause for the BULK INSERT statement).

See Also