• Article

Understanding Non-XML Format Files

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

Note

XML format files offer several advantages. For more information, see Format Files for Importing or Exporting Data.

Typically, format files are created for a particular data format by using the bcp command in one of the following ways:

  • You can create either type of format file, such as character data or native data, by specifying the format option in the bcp command. For more information, see Creating a Format File.

  • You can create a non-XML format file that contains interactively specified attributes for each data field. For more information, see Specifying Data Formats for Compatibility by Using bcp.

    Note

    When you specify an existing format file in a bcp command, the command uses the values that are recorded in the format file and does not prompt you for the file storage type, prefix length, field length, or field terminator.

Structure of Non-XML Format Files

A non-XML format file is a text file that has a specific structure. The non-XML format file contains information about the file storage type, prefix length, field length, and field terminator of every table column.

The following illustration illustrates the format-file fields for a sample non-XML format file.

Identifies the fields of a non-XML format file

The Version and Number of columns fields occur one time only. Their meanings are describes in the following table.

Format-file field

Description

Version

Version number of the format file. For most versions of SQL Server, the format file version is the same as the bcp utility (Bcp.exe), as follows:

SQL Server versionFormat file versionbcp version1
SQL Server version 7.07.07.0
SQL Server 2000 8.08.0
SQL Server 2005 9.09.0
SQL Server 200810.010.0
SQL Server 2008 R210.010.50

1 The version number is recognized only by bcp, not by Transact-SQL.

NoteNote
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.

Number of columns

Number of fields in the data file. This number must be the same in all rows.

The other format-file fields describe the data fields that are to be bulk imported or exported. Each data field requires a separate row in the format file. Every format-file row contains values for the format-file fields that are described in the following table.

Format-file field

Description

Host file field order

A number that indicates the position of each field in the data file. The first field in the row is 1, and so on.

Host file data type

Indicates the data type that is stored in a given field of the data file. With ASCII data files, use SQLCHAR; for native format data files, use default data types. For more information, see Specifying File Storage Type by Using bcp.

Prefix length

Number of length prefix characters for the field. Valid prefix lengths are 0, 1, 2, 4, and 8. To avoid specifying the length prefix, set this to 0. A length prefix must be specified if the field contains NULL data values. For more information, see Specifying Prefix Length in Data Files.

Host file data length

Maximum length, in bytes, of the data type stored in the particular field of the data file.

If you are creating a non-XML format file for a delimited text file, you can specify 0 for the host file data length of every data field. When a delimited text file having a prefix length of 0 and a terminator is imported, the field-length value is ignored, because the storage space used by the field equals the length of the data plus the terminator.

For more information, see Specifying Field Length by Using bcp.

Terminator

Delimiter to separate the fields in a data file. Common terminators are comma (,), tab (\t), and end of line (\r\n). For more information, see Specifying Field and Row Terminators.

Server column order

Order in which columns appear in the SQL Server table. For example, if the fourth field in the data file maps to the sixth column in a SQL Server table, the server column order for the fourth field is 6.

To prevent a column in the table from receiving any data from the data file, set the server column order value to 0.

Server column name

Name of the column copied from the SQL Server table. The actual name of the field is not required, but the field in the format file must not be blank.

Column collation

The collation used to store character and Unicode data in the data file.

Note

   You can modify a format file to let you bulk import from a data file in which the number or order of the fields are different from the number or order of table columns. For more information, see Using a Format File to Map Fields to Columns During Bulk Import.

Examples

The following example shows a previously created non-XML format file (myDepartmentIdentical-f-c.fmt). This file describes a character-data field for every column in the HumanResources.Department table in the AdventureWorks2008R2 sample database.

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

10.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     ""

Note

For an illustration that shows the format-file fields in relation to this sample non-XML format file, see "Structure of Non-XML Format Files," earlier in this topic.