• Article

Introduction to Format Files

A file that is used to store format information for each field in a data file relative to a specific table is called a format file. A format file provides all the format information that is required to bulk export or bulk import data. 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.

In Microsoft SQL Server 2000 and earlier versions, bulk exporting and importing works with a single type of format file. This is still supported. However, SQL Server 2005 and later versions also support XML format files as an alternative. Format files of the original type are called non-XML format files.

All format files contain descriptions of every field in a data file. XML format files also contain descriptions of the corresponding table columns. 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. An XML format file has the following characteristics:

  • Self-describing and easy to read, create, and extend.

  • Contains the data types of target columns.

    This enables separation between how data is represented in the data file and what data type is associated with each field in the file. For example, if a data file contains a character representation of the data, the corresponding SQL column type is lost.

    The bcp command and the BULK INSERT statement use the target table columns to do the type conversion. This makes a target table necessary. By contrast, the OPENROWSET(BULK...) function relies on an XML format file to read data from a data file. This makes a target table optional.

  • Allows for loading of a field that contains a single large object (LOB) data type from a data file.

  • The syntax of a format file is independent of the direction of the operation; that is, the syntax is the same for bulk export and bulk import.

For information about the layout of each type of format files, see "Examples," later in this topic.

When Is a Format File Required?

An INSERT ... SELECT * FROM OPENROWSET(BULK...) statement always requires a format file.

  • For bcp or BULK INSERT, in simple situations, using a format file is optional and rarely necessary. However, for complex bulk-import situations, a format file is frequently required.

Format files are required if:

  • The same data file is used as a source for multiple tables that have different schemas.

  • The data file has a different number of fields that the target table has columns; for example:

    • The target table contains at least one column for which either a default value is defined or NULL is allowed.

    • The users do not have SELECT/INSERT permissions on one or more columns in the table.

    • A single data file is used with two or more tables that have different schemas.

  • The column order is different for the data file and table.

  • The terminating characters or prefix lengths differ among the columns of the data file.

Note

In the absence of a format file, if a bcp command specifies a data-format switch (-n, -c, -w, or -N) or a BULK INSERT operation specifies the DATAFILETYPE option, the specified data format is used as the default method of interpreting the fields of the data file.

Examples

The following examples show the layout of a non-XML format file and of an XML format file. These format files correspond to the HumanResources.myTeam table in the AdventureWorks sample database. This table contains four columns: EmployeeID, Name, Title, and ModifiedDate.

Note

For information about this table and how to create it, see Creating the HumanResources.myTeam Table.

A. Using a non-XML format file

The following non-XML format file uses the SQL Server native data format for the HumanResources.myTeam table. This format file was created by using the following bcp command.

bcp AdventureWorks.HumanResources.myTeam format nul -f myTeam.Fmt -n -T 
The contents of this format file are as follows: 9.0
4
1       SQLSMALLINT   0       2       ""   1     EmployeeID               ""
2       SQLNCHAR      2       100     ""   2     Name                     SQL_Latin1_General_CP1_CI_AS
3       SQLNCHAR      2       100     ""   3     Title                    SQL_Latin1_General_CP1_CI_AS
4       SQLNCHAR      2       100     ""   4     Background               SQL_Latin1_General_CP1_CI_AS

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

B. Using an XML format file

The following XML format file uses the SQL Server native data format for the HumanResources.myTeam table. This format file was created by using the following bcp command.

bcp AdventureWorks.HumanResources.myTeam format nul -f myTeam.Xml -x -n -T

The format file contains:

 <?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="NativePrefix" LENGTH="1"/>
  <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="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="EmployeeID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Title" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Background" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

For more information, see Understanding XML Format Files.

See Also

Concepts

Working with Format Files