Use character format to import or export data (SQL Server)

Raksts
04/04/2023

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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.

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 Use Unicode Character Format to Import or Export Data (SQL Server).

In this Topic:
Considerations for Using Character Format
Command Options for Character Format
Example Test Conditions ● Sample Table ● Sample Non-XML Format File
Examples ● Using bcp and Character Format to Export Data ● Using bcp and Character Format to Import Data without a Format File ● Using bcp and Character Format to Import Data with a Non-XML Format File ● Using BULK INSERT and Character Format without a Format File ● Using BULK INSERT and Character Format with a Non-XML Format File ● Using OPENROWSET and Character Format with a Non-XML Format File
Related Tasks

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 Specify Field and Row Terminators (SQL Server).

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.

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 in the statement. 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 options:

Command	Option	Description
bcp	-c	Causes the bcp utility to use character data.*
BULK INSERT	DATAFILETYPE ='char'	Use character format when bulk importing data.
OPENROWSET	N/A	Must use a format file

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

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 (SQL Server).

Example Test Conditions

The examples in this topic are based on the table, and format file defined below.

Sample Table

The script below creates a test database, a table named myChar and populates the table with some initial values. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myChar ( 
   PersonID smallint NOT NULL,
   FirstName varchar(25) NOT NULL,
   LastName varchar(30) NOT NULL,
   BirthDate date,
   AnnualSalary money
   );

-- Populate table
INSERT TestDatabase.dbo.myChar
VALUES 
(1, 'Anthony', 'Grosse', '1980-02-23', 65000.00),
(2, 'Alica', 'Fatnowna', '1963-11-14', 45000.00),
(3, 'Stella', 'Rossenhain', '1992-03-02', 120000.00);

-- Review Data
SELECT * FROM TestDatabase.dbo.myChar;

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:

bcp TestDatabase.dbo.myChar format nul -f D:\BCP\myChar.fmt -T -c 

REM Review file
Notepad D:\BCP\myChar.fmt

Important

Ensure your non-XML format file ends with a carriage return\line feed. Otherwise you will likely receive the following error message:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

Examples

The examples below use the database, and format files created above.

Using bcp and Character Format to Export Data

-c switch and OUT command. Note: the data file created in this example will be used in all subsequent examples. At a command prompt, enter the following command:

bcp TestDatabase.dbo.myChar OUT D:\BCP\myChar.bcp -T -c

REM Review results
NOTEPAD D:\BCP\myChar.bcp

Using bcp and Character Format to Import Data without a Format File

-c switch and IN command. At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myChar;"

REM Import data
bcp TestDatabase.dbo.myChar IN D:\BCP\myChar.bcp -T -c

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myChar;"

Using bcp and Character Format to Import Data with a Non-XML Format File

-c and -f switches and IN command. At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myChar;"

REM Import data
bcp TestDatabase.dbo.myChar IN D:\BCP\myChar.bcp -f D:\BCP\myChar.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myChar;"

Using BULK INSERT and Character Format without a Format File

DATAFILETYPE argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

TRUNCATE TABLE TestDatabase.dbo.myChar; -- for testing
BULK INSERT TestDatabase.dbo.myChar
	FROM 'D:\BCP\myChar.bcp'
	WITH (
		DATAFILETYPE = 'Char'
		);

-- review results
SELECT * FROM TestDatabase.dbo.myChar;

Using BULK INSERT and Character Format with a Non-XML Format File

FORMATFILE argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

TRUNCATE TABLE TestDatabase.dbo.myChar; -- for testing
BULK INSERT TestDatabase.dbo.myChar
   FROM 'D:\BCP\myChar.bcp'
   WITH (
		FORMATFILE = 'D:\BCP\myChar.fmt'
		);

-- review results
SELECT * FROM TestDatabase.dbo.myChar;

Using OPENROWSET and Character Format with a Non-XML Format File

FORMATFILE argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

TRUNCATE TABLE TestDatabase.dbo.myChar;  -- for testing
INSERT INTO TestDatabase.dbo.myChar
	SELECT *
	FROM OPENROWSET (
		BULK 'D:\BCP\myChar.bcp', 
		FORMATFILE = 'D:\BCP\myChar.fmt'  
		) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myChar;

To use data formats for bulk import or bulk export

Kopīgot, izmantojot

Use character format to import or export data (SQL Server)

Considerations for Using Character Format

Command Options for Character Format

Example Test Conditions

Sample Table

Sample Non-XML Format File

Examples

Using bcp and Character Format to Export Data

Using bcp and Character Format to Import Data without a Format File

Using bcp and Character Format to Import Data with a Non-XML Format File

Using BULK INSERT and Character Format without a Format File

Using BULK INSERT and Character Format with a Non-XML Format File

Using OPENROWSET and Character Format with a Non-XML Format File

See Also

Atsauksmes

Papildu resursi

Kopīgot, izmantojot

Use character format to import or export data (SQL Server)

Considerations for Using Character Format

Command Options for Character Format

Example Test Conditions

Sample Table

Sample Non-XML Format File

Examples

Using bcp and Character Format to Export Data

Using bcp and Character Format to Import Data without a Format File

Using bcp and Character Format to Import Data with a Non-XML Format File

Using BULK INSERT and Character Format without a Format File

Using BULK INSERT and Character Format with a Non-XML Format File

Using OPENROWSET and Character Format with a Non-XML Format File

Related Tasks

See Also

Atsauksmes

Papildu resursi