• Article

Creating a Format File

When you bulk import into a SQL Server table or bulk export data from a table, you can use a format file to store format information for each field in a data file relative to that table. A format file provides a flexible system for writing data files that requires little or no editing to comply with other data formats or to read data files from other software programs.

SQL Server 2005 and later versions 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.

Generally, XML and non-XML format files are interchangeable. However, we recommend that you use the XML syntax for new format files because they provide several advantages over non-XML format files. For more information, see Introduction to Format Files.

Note

In SQL Server 2008 R2, the version number of the bcp utility (Bcp.exe) is 10.50, but the version number assigned to new format files is 10.0, the same as in SQL Server 2008. The version of bcp utility used to read a format file must be the same as, or a later than the version of the format file. For example, SQL Server 2008 bcp can read a version 9.0 format file, which is generated by SQL Server 2005 bcp, but SQL Server 2005 bcp cannot read a version 10.0 format file, which is generated by SQL Server 2008 or SQL Server 2008 R2 bcp.

This topic 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.

Creating a Non-XML Format File

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, such as:

bcptable_or_viewformat nul -fformat_file_name

Note

To distinguish a non-XML format file, we recommend that you use .fmt as the file name extension, for example, MyTable.fmt.

For information about the structure and fields of non-XML format files, see Understanding Non-XML Format Files.

Examples

This section contains the following examples that show how to use bcp commands to create a non-XML format file:

  • A. Creating a non-XML format file for native data

  • B. Creating a non-XML format file for character data

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

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

The examples use the HumanResources.Department table in the AdventureWorks2008R2 sample database. The HumanResources.Department table contains four columns: DepartmentID, Name, GroupName, and ModifiedDate.

Note

Adventure Works Cycles is a fictional manufacturing company used to demonstrate database concepts and scenarios.

A. Creating a non-XML format file for native data

The following example creates an XML format file, Department-n.xml, for the AdventureWorks2008R2HumanResources.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

formatnul-fformat_file

Specifies the non-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 is not specified, you must specify -U and -P to successfully log in.

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

bcp AdventureWorks2008R2.HumanResources.Department format nul -T -n -f Department-n.fmt

The generated format file, Department-n.fmt, contains the following information:

10.0
4
1       SQLSMALLINT   0       2       ""   1     DepartmentID                 ""
2       SQLNCHAR      2       100     ""   2     Name                         SQL_Latin1_General_CP1_CI_AS
3       SQLNCHAR      2       100     ""   3     GroupName                    SQL_Latin1_General_CP1_CI_AS
4       SQLDATETIME   0       8       ""   4     ModifiedDate                 ""

For more information, see Understanding Non-XML Format Files.

B. Creating a non-XML format file for character data

The following example creates an XML format file, Department.fmt, for the AdventureWorks2008R2HumanResources.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

formatnul-fformat_file

Specifies a non-XML format file.

-c

Specifies character data.

-T

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

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

bcp AdventureWorks2008R2.HumanResources.Department format nul -c -f Department-c.fmt -T

The generated format file, Department-c.fmt, contains the following information:

9.0
4
1       SQLCHAR       0       7       "\t"     1     DepartmentID                 ""
2       SQLCHAR       0       100     "\t"     2     Name                         SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     "\t"     3     GroupName                    SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       24      "\r\n"   4     ModifiedDate                 ""

For more information, see Understanding Non-XML Format Files.

C. Creating 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 AdventureWorks2008R2.HumanResources.Department format nul -T -N -f Department-n.fmt

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

D. Creating 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 AdventureWorks2008R2.HumanResources.Department format nul -T -w -f Department-w.fmt

For more information about how to use Unicode character data, see Using Unicode Character Format to Import or Export Data.

Creating an XML Format File

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:

bcptable_or_viewformat nul-fformat_file_name-x

Note

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 Understanding XML Format Files. For syntax information, see Schema Syntax for XML Format Files. For examples, see Sample XML Format Files.

Examples

This section contains the following examples that show how to use bcp commands to create an XML format file:

  • A. Creating an XML format file for character data

  • B. Creating an XML format file for native data

The examples use the HumanResources.Department table in the AdventureWorks2008R2 sample database. The HumanResources.Department table contains four columns: DepartmentID, Name, GroupName, and ModifiedDate.

Note

Adventure Works Cycles is a fictional manufacturing company used to demonstrate database concepts and scenarios.

A. Creating an XML format file for character data

The following example creates an XML format file, Department.xml, for the AdventureWorks2008R2HumanResources.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

formatnul-fformat_file-x

Specifies the XML format file.

-c

Specifies character data.

-t,

Specifies a comma (,) as the field terminator.

NoteNote
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 is not specified, you must specify -U and -P to successfully log in.

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

bcp AdventureWorks2008R2.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="https://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 Schema Syntax for XML Format Files. For information about character data, see Using Character Format to Import or Export Data.

B. Creating an XML format file for native data

The following example creates an XML format file, Department-n.xml, for the AdventureWorks2008R2HumanResources.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

formatnul-fformat_file-x

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 is not specified, you must specify -U and -P to successfully log in.

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

bcp AdventureWorks2008R2.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="https://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 Schema Syntax for XML Format Files. For information about how to use native data, see Using Native Format to Import or Export Data.

Mapping Data Fields to Table Columns

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. This lets you customize a format file to a data file whose fields do not map directly to the table columns. For more information, see the following topics: