What is a BCP file?

BCP (Bulk Copy Format) is Microsoft SQL Server’s technical data format that defines data structures to store different database data type values for import/export. The format fully defines the interpretation of each data column so that the set of values specified in the data file could be read. The BCP utility uses the BCP file format to read data from such a file and identify it.

BCP File Format

The BCP format file is an XML document that defines the column order, name and data type. It lets users import/export large amount data from data file specifying these fields. This is helpful in bulk import of data values from data files. The number and order of data fields in the data file may be different than those in the destination table columns. This is when the BCP data format file comes to help by specifying the order and type of columns for importing the data.

The structure of the format file is represented in the following format.

<BCPFORMAT ...>
    <RECORD>
       <FIELD ID = "fieldID" xsi:type = "fieldType" [...] />
    </RECORD>
    <ROW>
       <COLUMN SOURCE = "fieldID" NAME = "columnName" xsi:type = "columnType" [...]  />
    </ROW>
 </BCPFORMAT>

BCP Data Types

Data TypeRangeRepresentation
BigInt-263 (-9,223,372,036,854,775,808) through 263-1 (9,223,372,036,854,775,807)BigInt = ["-"]1*19DIGIT
Binary1 through 8000 byteshexadecimal-encoded Unicode string format Binary = 32000OCTET
Bit0 or 1simple Unicode string Bit = “0” / “1”
Char1 through 8000Unicode String Format, Char = 16000OCTET
CLRUDTVarBinaryCLRUDT = 0*nOCTET with n = 4 x (2,147,483,647)
Date0001-01-01 through 9999-12-31YYYY-MM-DD string format
DateTime1753-01-01 00:00:00.000 through 9999-12-31 23:59:59.997Unicode YYYY-MM-DD hh:mm:ss[.nnn] string format
DateTime20001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999.Unicode YYYY-MM-DD hh:mm:ss[.nnnnnnn] string format
DateTimeOffset0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 in the Coordinated Universal Time (UTC) time zoneUnicode YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+
Decimal-1038 + 1 through 1038 – 1Unicode string format Decimal = ["-"] 0*38DIGIT ["."0*38DIGIT]
Float-1.79E+308 through -2.23E-308; 0; from 2.23E-308 through 1.79E+308Unicode string format
Imagesequence of bytes that range from 0 through 231 – 1 (2,147,483,647)hexadecimal-encoded Unicode string format
Int-231 (-2,147,483,648) through 231 – 1 (2,147,483,647)Unicode string format

References

See Also