Format File Overview

The number of the field in the data file (starting with 1).

The format of the field as stored in the data file (e.g., SQLCHAR for external character fields, SQLNCHAR for fields stored in Unicode character format, and SQLINT for binary integer fields in the data file). The next three fields combine to describe the length of the field in the data file and support both fixed-length and variable-length fields.

The Prefix Length field, which stores the data field's actual length in bytes. The prefix field can be 0, 1, 2, or 4 bytes long, and SQL Server Books Online (BOL) describes the minimum length that you must specify for different data types.

The Host File Data Length, which is the maximum number of bytes this field will occupy in the data file. The bcp operation uses this field only when the Prefix Length is zero—meaning bcp doesn't have access to the actual data length—and you haven't designated a Field Terminator for the field. You specify a fixed-field format for the data field by setting a zero-length prefix field and no Field Terminator. The bcp operation uses the number of bytes specified for the data-file field's Host File Data Length.

The Field Terminator string, which is a sequence of one or more characters that don't appear in the field's data and that bcp uses to signify the end of that data field. A null string ("") instructs bcp not to use a Field Terminator string. The bcp operation defaults to using the newline character (specified as "") for the row's last field and the Tab character (specified as "t") for other fields. When you specify a Field Terminator for a field, the length of the data field that bcp writes to the data file varies only when the table column is of a variable-length data type and you requested a character or Unicode character format for the data-file field. Otherwise, when the database column is of fixed length or bcp writes the field to the file in a SQL Server native data format, the field's length in the data file is the same for all data rows. When you don't use a Field Terminator string with fixed-length fields or when you ask bcp to write a field to the data file in SQL Server native data format, you must specify the correct number of bytes that the field will occupy in the data file. In any case, you can easily get the correct number of bytes by letting bcp generate the format file during an export operation. The next two fields in the format file designate the column in the SQL Server table that corresponds to the data-file field that this record describes. The bcp operation relies on the first of these two fields.

The column number in the SQL Server table that bcp will copy the data to or from. You can display table column numbers by using the sp_help system stored procedure.

The column's name in the SQL Server table. This field only documents the column name; bcp ignores it. You can't, however, leave this field blank.

The SQL Server collation for character data in the data file. You can let bcp generate the format file with the proper collation specification for character fields; for noncharacter fields, specify a null string ( "" ).