Character Format
The -c switch (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 an instance of Microsoft® SQL Server™ from another database. Character format tends to be used when copying data from other programs that 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 following table shows 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 |
To use field and row terminators other than the default provided with character format, specify the following.
| Terminator | bcp utility switch | BULK INSERT clause |
|---|---|---|
| Field | -t | FIELDTERMINATOR |
| Row | -r | ROWTERMINATOR |
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
Here are 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 an instance of SQL Server, and converts characters from SQL Server to OEM characters before copying them to the data file. Extended character data can be lost 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).
sql_variant data stored in a character mode file is stored without any meta data. Each data value is converted to char following the rules of implicit data conversion. When it is bulk copied into a sql_variant destination column, the data is imported as char. When it is bulk copied into a destination column with a data type other than sql_variant, the values are converted from char following the rules of implicit conversion.
Note The bcp utility exports money values in character format data files without digit grouping symbols such as comma separators, but with four digits after the decimal point. For example, a money column containing the value 1,234,567.123456 is bulk copied to a data file as the character string 1234567.1235.