--- title: "bcp_colfmt | Microsoft Docs" ms.custom: "" ms.date: "03/14/2017" ms.prod: sql ms.prod_service: "database-engine, sql-database, sql-data-warehouse, pdw" ms.reviewer: "" ms.technology: native-client ms.topic: "reference" apiname: - "bcp_colfmt" apilocation: - "sqlncli11.dll" apitype: "DLLExport" helpviewer_keywords: - "bcp_colfmt function" ms.assetid: 5c3b6299-80c7-4e84-8e69-4ff33009548e author: markingmyname ms.author: maghan monikerRange: ">=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current" --- # bcp_colfmt [!INCLUDE[appliesto-ss-asdb-asdw-pdw-md](../../includes/appliesto-ss-asdb-asdw-pdw-md.md)] Specifies the source or target format of the data in a user file. When used as a source format, **bcp_colfmt** specifies the format of an existing data file used as the source of data in a bulk copy to a [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] table. When used as a target format, the data file is created using the column formats specified with **bcp_colfmt**. ## Syntax ``` RETCODE bcp_colfmt ( HDBC hdbc, INT idxUserDataCol, BYTE eUserDataType, INT cbIndicator, DBINT cbUserData, LPCBYTE pUserDataTerm, INT cbUserDataTerm, INT idxServerCol); ``` ## Arguments *hdbc* Is the bulk copy-enabled ODBC connection handle. *idxUserDataCol* Is the ordinal column number in the user data file for which the format is being specified. The first column is 1. *eUserDataType* Is the data type of this column in the user file. If different from the data type of the corresponding column in the database table (*idxServerColumn*), bulk copy converts the data if possible. [!INCLUDE[ssVersion2005](../../includes/ssversion2005-md.md)] introduced support for SQLXML and SQLUDT data type tokens in the *eUserDataType* parameter. The *eUserDataType* parameter is enumerated by the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] data type tokens in sqlncli.h, not the ODBC C data type enumerators. For example, you can specify a character string, ODBC type SQL_C_CHAR, using the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]-specific type SQLCHARACTER. To specify the default data representation for the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] data type, set this parameter to 0. For a bulk copy out of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] into a file, when *eUserDataType* is SQLDECIMAL or SQLNUMERIC: - If the source column is not **decimal** or **numeric**, the default precision and scale are used. - If the source column is **decimal** or **numeric**, the precision and scale of the source column are used. *cbIndicator* Is the length, in bytes, of a length/null indicator within the column data. Valid indicator length values are 0 (when using no indicator), 1, 2, 4, or 8. To specify default bulk copy indicator usage, set this parameter to SQL_VARLEN_DATA. Indicators appear in memory directly before any data, and in the data file directly before the data to which they apply. If more than one means of specifying a data file column length is used (such as an indicator and a maximum column length, or an indicator and a terminator sequence), bulk copy chooses the one that results in the least amount of data being copied. Data files generated by bulk copy when no user intervention adjusts the format of the data contain indicators when the column data can vary in length or the column can accept NULL as a value. *cbUserData* Is the maximum length, in bytes, of this column's data in the user file, not including the length of any length indicator or terminator. Setting *cbUserData* to SQL_NULL_DATA indicates that all values in the data file column are, or should be set to NULL. Setting *cbUserData* to SQL_VARLEN_DATA indicates that the system should determine the length of data in each column. For some columns, this could mean that a length/null indicator is generated to precede data on a copy from [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], or that the indicator is expected in data copied to [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. For [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] character and binary data types, *cbUserData* can be SQL_VARLEN_DATA, SQL_NULL_DATA, 0, or some positive value. If *cbUserData* is SQL_VARLEN_DATA, the system uses either the length indicator, if present, or a terminator sequence to determine the length of the data. If both a length indicator and a terminator sequence are supplied, bulk copy uses the one that results in the least amount of data being copied. If *cbUserData* is SQL_VARLEN_DATA, the data type is an [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] character or binary type, and neither a length indicator nor a terminator sequence is specified, the system returns an error message. If *cbUserData* is 0 or a positive value, the system uses *cbUserData* as the maximum data length. However, if, in addition to a positive *cbUserData*, a length indicator or terminator sequence is provided, the system determines the data length by using the method that results in the least amount of data being copied. The *cbUserData* value represents the count of bytes of data. If character data is represented by Unicode wide characters, then a positive *cbUserData* parameter value represents the number of characters multiplied by the size, in bytes, of each character. *pUserDataTerm* Is the terminator sequence to be used for this column. This parameter is useful mainly for character data types because all other types are of fixed length or, in the case of binary data, require an indicator of length to accurately record the number of bytes present. To avoid terminating extracted data, or to indicate that data in a user file is not terminated, set this parameter to NULL. If more than one means of specifying a user-file column length is used (such as a terminator and a length indicator, or a terminator and a maximum column length), bulk copy chooses the one that results in the least amount of data being copied. The bulk copy API performs Unicode-to-MBCS character conversion as required. Care must be taken to ensure that both the terminator byte string and the length of the byte string are set correctly. *cbUserDataTerm* Is the length, in bytes, of the terminator sequence to be used for this column. If no terminator is present or desired in the data, set this value to 0. *idxServerCol* Is the ordinal position of the column in the database table. The first column number is 1. The ordinal position of a column is reported by [SQLColumns](../../relational-databases/native-client-odbc-api/sqlcolumns.md). If this value is 0, bulk copy ignores the column in the data file. ## Returns SUCCEED or FAIL. ## Remarks The **bcp_colfmt** function allows you to specify the user-file format for bulk copies. For bulk copy, a format contains the following parts: - A mapping from user-file columns to database columns. - The data type of each user-file column. - The length of the optional indicator for each column. - The maximum length of data per user-file column. - The optional terminating byte sequence for each column. - The length of the optional terminating byte sequence. Each call to **bcp_colfmt** specifies the format for one user-file column. For example, to change the default settings for three columns in a five-column user data file, first call [bcp_columns](../../relational-databases/native-client-odbc-extensions-bulk-copy-functions/bcp-columns.md)**(5)**, and then call **bcp_colfmt** five times, with three of those calls setting your custom format. For the remaining two calls, set *eUserDataType* to 0, and set *cbIndicator*, *cbUserData*, and *cbUserDataTerm* to 0, SQL_VARLEN_DATA, and 0 respectively. This procedure copies all five columns, three with your customized format and two with the default format. For *cbIndicator*, a value of 8 to indicate a large value type is now valid. If the prefix is specified for a field whose corresponding column is a new max type, it can only be set to 8. For details, see [bcp_bind](../../relational-databases/native-client-odbc-extensions-bulk-copy-functions/bcp-bind.md). The **bcp_columns** function must be called before any calls to **bcp_colfmt**. You must call **bcp_colfmt** once for each column in the user file. Calling **bcp_colfmt** more than once for any user-file column causes an error. You do not need to copy all data in a user file to the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] table. To skip a column, specify the format of the data for the column, setting the *idxServerCol* parameter to 0. If you want to skip a column, you must specify its type. The [bcp_writefmt](../../relational-databases/native-client-odbc-extensions-bulk-copy-functions/bcp-writefmt.md) function can be used to persist the format specification. ## bcp_colfmt Support for Enhanced Date and Time Features For information about the types used with the *eUserDataType* parameter for date/time types, see [Bulk Copy Changes for Enhanced Date and Time Types (OLE DB and ODBC)](../../relational-databases/native-client-odbc-date-time/bulk-copy-changes-for-enhanced-date-and-time-types-ole-db-and-odbc.md). For more information, see [Date and Time Improvements (ODBC)](../../relational-databases/native-client-odbc-date-time/date-and-time-improvements-odbc.md). ## See Also [Bulk Copy Functions](../../relational-databases/native-client-odbc-extensions-bulk-copy-functions/sql-server-driver-extensions-bulk-copy-functions.md)