Muokkaa

Create a format file with bcp (SQL Server)

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

This article describes how to use the bcp utility to create a format file for a particular table. The format file is based on the data-type option specified (-n, -c, -w, or -N) and the table or view delimiters.

When you bulk import into a SQL Server table or bulk export data from a table, you can use a format file as a flexible system for writing data files. Format files require little or no editing to comply with other data formats, or to read data files from other software programs.

Limitations

The version of the bcp utility (bcp.exe) used to read a format file must be the same as, or later than, the version used to create the format file. For example, SQL Server 2025 (17.x) bcp can read a version 16.0 format file, which is generated by SQL Server 2022 (16.x) bcp, but SQL Server 2022 (16.x) bcp can't read a version 17.0 format file, which is generated by SQL Server 2025 (17.x) bcp.

Note

This syntax, including bulk insert, isn't supported in Azure Synapse Analytics. In Azure Synapse Analytics and other cloud database platform integrations, accomplish data movement via the COPY statement in Azure Data Factory, or by using T-SQL statements such as COPY INTO and PolyBase.

Create format files

SQL Server supports two types of format file: non-XML format and XML format. The non-XML format is the original format supported by earlier versions of SQL Server.

Generally, XML and non-XML format files are interchangeable. However, we recommend that you use XML syntax for format files, because they provide several advantages over non-XML format files.

The code samples in this article use the AdventureWorks2025 or AdventureWorksDW2025 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page. Adventure Works Cycles is a fictional manufacturing company used to demonstrate database concepts and scenarios.

Create a format file

To use bcp to create a format file, specify the format argument and use nul instead of a data-file path. The format option always requires the -f option.

To create an XML format file, you must specify the -x option, such as bcp <table_or_view> format nul -f <format_file_name> -x.

To distinguish an XML format file, use .xml as the file name extension, for example, MyTable.xml.

For information about the structure and fields of XML format files, see XML format files.

Examples

This section contains examples that show how to use bcp to create a format file. The HumanResources.Department table contains four columns: DepartmentID, Name, GroupName, and ModifiedDate.

A. Create a format file for character data

The following example creates a format file for the HumanResources.Department table using character data formats.

Create an XML format file named Department-c.xml. The format file uses a non-default field terminator (,). The following section shows the contents of the generated format file.

The bcp command contains the following qualifiers.

Qualifiers Description
format nul -x -f <format_file> Specifies the XML format file.
-c Specifies character data.
-t, Specifies a comma (,) as the field terminator.

Note: If the data file uses the default field terminator (\t), the -t switch is unnecessary.
-T Specifies that bcp connects to SQL Server with a trusted connection using integrated security. If you don't specify -T, you must specify -U and -P to sign in successfully.

At the Windows command prompt, enter the following bcp command:

bcp AdventureWorks2025.HumanResources.Department format nul -c -x -f Department-c.xml -t, -T

The generated format file, Department-c.xml, contains the following XML elements:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>

For information about the syntax of this format file, see XML format files. For information about character data, see Use character format to import or export data.

B. Create a format file for native data

The following example creates a format file for the HumanResources.Department table using native data types.

Create an XML format file named Department-n.xml. The following section shows the contents of the generated format file.

The bcp command contains the following qualifiers.

Qualifiers Description
format nul -x -f <format_file> Specifies the XML format file.
-n Specifies native data types.
-T Specifies that bcp connects to SQL Server with a trusted connection using integrated security. If you don't specify -T, you must specify -U and -P to sign in successfully.

At the Windows command prompt, enter the following bcp command:

bcp AdventureWorks2025.HumanResources.Department format nul -x -f Department-n.xml -n -T

The generated format file, Department-n.xml, contains the following XML elements:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
  <FIELD ID="1" xsi:type="NativeFixed" LENGTH="2"/>
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="NativeFixed" LENGTH="8"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>

For information about the syntax of this format file, see XML format files. For information about how to use native data, see Use native format to import or export data.

C. Create a non-XML format file for Unicode native data

To create a non-XML format file for Unicode native data for the HumanResources.Department table, use the following command:

bcp AdventureWorks2025.HumanResources.Department format nul -T -N -f Department-n.fmt

For more information about how to use Unicode native data, see Use Unicode Native Format to Import or Export Data.

D. Create a non-XML format file for Unicode character data

To create a non-XML format file for Unicode character data for the HumanResources.Department table that uses default terminators, use the following command:

bcp AdventureWorks2025.HumanResources.Department format nul -T -w -f Department-w.fmt

For more information about how to use Unicode character data, see Use Unicode character format to import or export data.

E. Use a format file with the code page option

When you create a format file using bcp format, it writes information about the collation and code page to the format file.

The following example format file, for a table with five columns, includes the collation.

13.0
5
1  SQLCHAR         0       0       "\t"             1     c_0          Cyrillic_General_CS_AS
2  SQLCHAR         0       0       "\t"             2     c_1          Cyrillic_General_CS_AS
3  SQLCHAR         0       3000    "\t"             3     c_2          Cyrillic_General_CS_AS
4  SQLCHAR         0       5       "\t"             4     c_3          ""
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4          ""

If you try to import data into SQL Server by using bcp in -c -C65001 -f format_file ... or with BULK INSERT or OPENROWSET with FORMATFILE='format_file' CODEPAGE=65001 ..., the collation or code page information in the format file takes priority over the 65001 option.

Therefore, if you generate a format file, you must manually delete the collation information from the generated format file before you start importing data back into SQL Server.

The following example shows the format file without the collation information.

13.0
5
1  SQLCHAR         0       0       "\t"             1     c_0              ""
2  SQLCHAR         0       0       "\t"             2     c_1              ""
3  SQLCHAR         0       3000    "\t"             3     c_2              ""
4  SQLCHAR         0       5       "\t"             4     c_3              ""
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4              ""

Map data fields to table columns

A format file created by bcp describes all the table columns in order. You can modify a format file to rearrange or omit table columns. You can customize a format file to a data file whose fields don't map directly to the table columns. For more information, see the following articles:

Related content

  • Last updated on