Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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.
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 2016 (13.x) bcp can read a version 12.0 format file, which is generated by SQL Server 2014 (12.x) bcp, but SQL Server 2014 (12.x) bcp can't read a version 13.0 format file, which is generated by SQL Server 2016 (13.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.
SQL Server support 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 AdventureWorks2022
or AdventureWorksDW2022
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.
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 always requires the -f
option, and to create an XML format file, you must also specify the -x
option, such as bcp <table_or_view> format nul -f <format_file_name> -x
.
To distinguish an XML format file, we recommend that you 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 (SQL Server).
This section contains the following examples that show how to use bcp commands to create an XML format file. The HumanResources.Department
table contains four columns: DepartmentID
, Name
, GroupName
, and ModifiedDate
.
The following example creates an XML format file, Department.xml
, for the HumanResources.Department
table. The format file uses character data formats and a non-default field terminator (,
). The contents of the generated format file are presented after the command.
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 the bcp utility connects to SQL Server with a trusted connection using integrated security. If -T isn't specified, you must specify -U and -P to successfully sign in. |
At the Windows command prompt, enter the following bcp
command:
bcp AdventureWorks2022.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 (SQL Server). For information about character data, see Use character format to import or export data (SQL Server).
The following example creates an XML format file, Department-n.xml
, for the HumanResources.Department
table. The format file uses native data types. The contents of the generated format file are presented after the command.
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 the bcp utility connects to SQL Server with a trusted connection using integrated security. If -T isn't specified, you must specify -U and -P to successfully sign in. |
At the Windows command prompt, enter the following bcp
command:
bcp AdventureWorks2022.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 (SQL Server). For information about how to use native data, see Use native format to import or export data (SQL Server).
As created by bcp, a format file describes all the table columns in order. You can modify a format file to rearrange or omit table rows. 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:
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Work with tables in Dynamics 365 Business Central - Training
Do you want to know how to create new tables in Business Central? If so, this module is for you. This module focuses on the different table types in Business Central and show you how to create new tables. Additionally, you learn how to use Visual Studio Code snippets to create a table, fields, and keys.
Documentation
Format files to import and export data - SQL Server
When you bulk import to a SQL Server table or bulk export from a table, a format file can store field format information for a data file relative to a table.
Specify file storage type with bcp - SQL Server
Use bcp to export data to a file as its database table type, in its character representation, or as a data type that supports implicit conversion.
Use a format file to bulk import data - SQL Server
In SQL Server, you can use a format file in bulk-import operations. A format file maps the fields of the data file to the columns of the table.