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 in Microsoft SQL Server 2005. However, SQL Server 2005 also supports 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.
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. For example, when importing data from a data file into a table, the follow situations might require format files:
- The user does not have INSERT permissions on some columns of the target table.
- The same data file is used as a source for multiple tables that have different schemas.
- The data file has columns in a different order than the target table.
- Data elements in the data file have different terminating characters or prefix lengths.
Format files are required if:
- 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
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
17 July 2006 |
|