Create a Field Format and Mapping File for BCP

---

Samples from http://blog.netnerds.net/2007/01/bcp-remove-quotes-from-csv-import-using-bcp-format-file/

CSV File:

locId,country,region,city,postalCode,latitude,longitude,dmaCode,areaCode
68954,"US","LA","Kaplan","70548",29.9845,-92.3224,642,337

Matching BCP Format File:

7.0
9
1       SQLCHAR    0       255       ",""    1     locId
2       SQLCHAR    0       255       "",""    2     country
3       SQLCHAR    0       255       "",""    3     region
4       SQLCHAR    0       255       "",""    4     city
5       SQLCHAR    0       255       "","     5     postalCode
6       SQLCHAR    0       255       ","    6     latitude
7       SQLCHAR    0       255       ","    7     longitude
8       SQLCHAR    0       255       ","    8     dmaCode
9       SQLCHAR    0       255       "\n"   9     areaCode

Bcp.gif

Field	Description
Version	Version number of bcp.
Number of fields	Number of fields in the data file.
Host file field order	Position of each field within the data file. The first field in the row is 1, and so on.
Host file data type	Data type stored in the particular field of the data file. With ASCII data files, use SQLCHAR; for native format data files, use default data types. For more information, see <a href="impt_bcp_12.htm">File Storage Type</a>.
Prefix length	Number of length prefix characters for the field. Legal prefix lengths are 0, 1, 2, and 4. To avoid specifying the length prefix, set this to 0. A length prefix must be specified if the field contains null data values. For more information, see <a href="impt_bcp_13.htm">Prefix Length</a>.
Host file data length	Maximum length, in bytes, of the data type stored in the particular field of the data file. For more information, see <a href="impt_bcp_14.htm">Field Length</a>.
Terminator	Delimiter to separate the fields in a data file. Common terminators are comma (,), tab (\t), and end of line (\r\n). For more information, see <a href="impt_bcp_15.htm">Field Terminator</a>.
Server column order	Order that columns appear in the SQL Server table. For example, if the fourth field in the data file maps to the sixth column in a SQL Server table, then for the fourth field the server column order is 6. To omit a column in the table from receiving any data in the data file, set the server column order value to 0.
Server column name	Name of the column taken from the SQL Server table. It is not necessary to use the actual name of the field. The only condition is that the field in the format file not be blank.