Format Files

After gathering information about each field in the table, bcp asks if you want to save the information to a format file and prompts for the file name.

Using a format file created for the data to be copied with bcp allows you to copy data in or out noninteractively without being prompted by bcp for information, since the format file supplies the information that bcp needs. Use this newly created format file at any other time to copy the data back into Adaptive Server or to copy data out from the table.

Figure 2-3 illustrates the format of the bcp format files. It shows the publishers table from the pubs2 database, with all the host file columns in character format, with no prefix, and using the default data length, a newline terminator at the end of the final column of a row, and tabs as terminators for all other columns.

Figure 2-3: bcp format file

The format of the bcp format file includes the TDS level, number of columns, host file comn order, host file datatype, prefix length, host file data length, terminator, server column order, and server column name.

Using Figure 2-3 as the format file example, the names of the various elements of a bcp format file are:

The Tabular Data Stream (TDS) version is always the first line of the file. It specifies the version of TDS that you are using, not the Adaptive Server version, and appears is a literal string without quotation marks. In Figure 2-3, the version is 10.0.
The second line of a bcp format file is the number of columns, which refers to the number of records in the format file, not including lines 1 and 2. Each column in the host table has one line.
One line for each column follows the first and second lines in the database table. Each line consists of elements that are usually separated by tabs, except for the host file datatype and the prefix length which are usually separated by a space. These elements are:

Elements	Descriptions
Host file column order	The host file column order is the sequential number of the field in the host data file, which begins numbering at 1.
Host file datatype	The host file datatype refers to the storage format of the field in the host data file, not the datatype of the database table column. The host file datatypes and their storage formats are: SYBCHAR – char, chavarchar (ASCII), nchar, nvarchar SYBTEXT – text SYBBINARY – binary, timestamp, unichar, univarchar, varbinary SYBIMAGE – image SYBINT1 – tinyint SYBINT2 – smallint SYBINT4 – int SYBINT8 – bigint SYBFLT8 – float SYBREAL – real SYBBIT – bit SYBNUMERIC – numeric SYBDECIMAL – decimal SYBMONEY – money SYBMONEY4 – smallmoney SYBDATETIME – datetime SYBDATETIME4 – smalldatetime SYBDATE – date SYBTIME – time SYBUINT8 – unsigned bigint SYBUINT4 – unsigned int SYBUINT2 – unsigned smallint SYBUNITEXT – unitext SYBFLT8 – double Data written to a host file in its native format preserves all of its precision. datetime and float values preserve all of their precision, even when they are converted to character format. Adaptive Server stores money values to a precision of one ten-thousandth of a monetary unit. However, when money values are converted to character format, their character format values are recorded only to the nearest two places. See Chapter 1, “System and User-Defined Datatypes” in the Reference Manual: Building Blocks for descriptions and appropriate uses of Adaptive Server datatypes.
Prefix length	Prefix length indicates the number of bytes in the field length prefix. The prefix length is a 0-, 1-, 2-, or 4-byte unsigned integer value embedded in the host data file that specifies the actual length of data contained in the field. Some fields may have a length prefix while others do not. The allowable prefix length values in bytes, and their ranges are: 0. Range: No prefix 1. Range: 2⁸-1; 0-255 2. Range: 2¹⁶-1; 0-65535 4. Range: 2³² -1; 0-4,294,967,295
Host file data length	Host file data length refers to the maximum number of bytes to copy for the field. To decide how much data to copy in or out, bcp uses one of: The maximum field length The prefix length, if any The field terminator string, if any If more than one method of field length specification is given, bcp chooses the one that copies the least amount of data.
Terminator	The terminator can be up to 30 bytes of characters enclosed in quotation marks (" "). The terminator designates the end of data for the host data file field.
Server column order	The server column order represents the colid (column ID) of the syscolumns column into which the host data file column is to be loaded. Together with the host file column order, this element maps host data file fields to the database table columns.
Server column name	The server column name is the name of the database table column into which this field is to be loaded.
Column precision	The column precision is the precision of the database table column into which this field is to be loaded. This element is present only if the storage format is numeric or decimal.
Column scale	The column scale is the scale of the database table column into which this field is to be loaded. This element is present only if the storage format is numeric or decimal.