Character Format

Character Format

SQL Server 2000

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

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

See Also

Copying Data Between Different Collations

ServerBCPDataFileType Property

© 2015 Microsoft