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 the SAP ASE server or to copy data out from the table.

The figure 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.

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.

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 this 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 SAP ASE version, and appears is a literal string without quotation marks. In the figure, 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	Description
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. See the next table for the list of host file datatypes and their storage formats. 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. The SAP ASE 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 System and User-Defined Datatypes in the Reference Manual: Building Blocks for descriptions and appropriate uses of SAP ASE 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.`

Host File Datatype Storage Formats

`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`