Jaa

Using Character Format to Import or Export Data

  • Artikkeli

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.

Note

When you bulk transfer data between instances of Microsoft SQL Server and the data file contains Unicode character data but not any extended or DBCS characters, use the Unicode character format. For more information, see Using Unicode Character Format to Import or Export Data.

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.

Considerations for Using Character Format

When using character format, consider the following:

  • By default, the bcp utility separates the character-data fields with the tab character and terminates the records with the newline character. For information about how to specify alternative terminators, see Specifying Field and Row Terminators.

  • 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. For more information about using a code page, see Copying Data Between Different Collations.

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

Command Options for Character Format

You can import character format data into a table using bcp, BULK INSERT or INSERT ... SELECT * FROM OPENROWSET(BULK...). For a bcp command or BULK INSERT statement, you can specify the data format on the command line. For an INSERT ... SELECT * FROM OPENROWSET(BULK...) statement, you must specify the data format in a format file.

Character format is supported by the following command line options:

Command Option Description

bcp

-c

Causes the bcp utility to use character data.1

BULK INSERT

DATAFILETYPE ='char'

Use character format when bulk importing data.

1 To load character (-c) data to a format compatible with earlier versions of SQL Server clients, use the -V switch. For more information, see Importing Native and Character Format Data from Earlier Versions of SQL Server.

For more information, see bcp Utility, BULK INSERT (Transact-SQL), or OPENROWSET (Transact-SQL).

Note

Alternatively, you can specify formatting on a per-field basis in a format file. For more information, see Format Files for Importing or Exporting Data.

Examples

The following examples demonstrate how to bulk export character data using bcp and bulk import the same data using BULK INSERT.

Sample Table

The examples require that a table named myTestCharData table be created in the AdventureWorks sample database under the dbo schema. Before you can run the examples, you must create this table. To create this table, in SQL SQL Server Management Studio Query Editor, execute:

USE AdventureWorks;
GO
CREATE TABLE myTestCharData (
   Col1 smallint,
   Col2 nvarchar(50),
   Col3 nvarchar(50)
   );

To populate this table and view the resulting contents execute the following statements:

INSERT INTO myTestCharData(Col1,Col2,Col3)
   VALUES(1,'DataField2','DataField3');
INSERT INTO myTestCharData(Col1,Col2,Col3)
   VALUES(2,'DataField2','DataField3');
GO
SELECT Col1,Col2,Col3 FROM myTestCharData

Using bcp to Bulk Export Character Data

To export data from the table to the data file, use bcp with the out option and the following qualifiers:

Qualifiers

Description

-c

Specifies character format.

-t,

Specifies a comma (,) as the field terminator.

ms190919.note(en-US,SQL.90).gifNote:

The default field terminator is the tab character (\t). For more information, see Specifying Field and Row Terminators.

-T

Specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. If -T is not specified, you need to specify -U and -P to successfully log in.

The following example bulk exports data in character format from the myTestCharData table into a new data file named myTestCharData-c.Dat data file that uses the comma (,) as the field terminator. At the Microsoft Windows command prompt, enter:

bcp AdventureWorks..myTestCharData out C:\myTestCharData-c.Dat -c -t, -T

Using BULK INSERT to Bulk Import Character Data

The following example uses BULK INSERT to import the data in the myTestCharData-c.Dat data file into the myTestCharData table. In SQL Server Management Studio Query Editor, execute:

USE AdventureWorks;
GO
BULK INSERT myTestCharData 
   FROM 'C:\myTestCharData-c.Dat' 
   WITH (
      DATAFILETYPE='char',
      FIELDTERMINATOR=','
   ); 
GO
SELECT Col1,Col2,Col3 FROM myTestCharData;
GO

See Also

Concepts

Copying Data Between Different Collations
Importing Native and Character Format Data from Earlier Versions of SQL Server

Other Resources

bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Data Types (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance