Format File Overview

The first two lines of the format file contain the version number of the bulk copy program (bcp) that the format file was created to use (8.0 for SQL Server 2000's bcp) and the number of data columns in the data file, respectively. Each remaining line defines the correspondence between one data field in the file and one column in the SQL Server table. Each of these lines must contain the following eight fields of information:

1. The number of the field in the data file (starting with 1).
2. 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.
3. 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.
4. 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.
5. 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 "\n") 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.
6. 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.
7. 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.
8. 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 ( "" ).