Use Character Format to Import or Export Data (SQL Server)

Character format is recommended when you bulk export data to a text file that is to be used in another program or when you bulk import data from a text file that is generated by another program.

Character format uses the character data format for all columns. 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 SQL Server from another database vendor such as Oracle.

By default, before the bulk export or import of character-mode data, the following conversions are performed:

Direction of bulk operation

Conversion

Export

Converts data to character representation. If explicitly requested, the data is converted to the requested code page for character columns. If no code page is specified, the character data is converted by using the OEM code page of the client computer.

Import

Converts character data to native representation, when necessary, and translates the character data from the client's code page to the code page of the target column(s).

To prevent loss of extended characters during conversion, either use Unicode character format or specify a code page.

Any sql_variant data that is stored in a character-format file is stored without metadata. Each data value is converted to char format, according to the rules of implicit data conversion. When imported into a sql_variant column, the data is imported as char. When imported into a column with a data type other than sql_variant, the data is converted from char by using implicit conversion. For more information about data conversion, see Data Type Conversion (Database Engine).

The bcp utility exports money values as character-format data files with four digits after the decimal point and without any digit-grouping symbols such as comma separators. For example, a money column that contains the value 1,234,567.123456 is bulk exported to a data file as the character string 1234567.1235.

Sample Non-XML Format File

SQL Server support two types of format file: non-XML format and XML format. The non-XML format is the original format that is supported by earlier versions of SQL Server. Please review Non-XML Format Files (SQL Server) for detailed information. The following command will use the bcp utility to generate a non-xml format file, myChar.fmt, based on the schema of myChar. To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. The format option also requires the -f option. In addition, for this example, the qualifier c is used to specify character data, and T is used to specify a trusted connection using integrated security. At a command prompt, enter the following command: