Generate format file for BULK INSERT with Text Qualifier in SQL Server

We need format file to import the data from flat file or CSV file to SQL table when using BCP or BULK INSERT method. That format file can be .XML or .FMT file where we need to mention the sequence of columns name, data type and length.

For instance, we have employee data flat file like as below and we need to populate the data on Employee data by different methods.

Employee_Id,Employee_Name,Employee_Address,Employee_City,Employee_State,Employee_Salary
1001,BaskaranR,"50, Mount Road",Chennai,TamilNadu,50000
1002,xxxxxxxxx,"16, Cross Street",Chennai,TamilNadu,45000
1003,yyyyyyyyy,"74, DSI estate",Chennai,TamilNadu,37000
1004,zzzzzzzzz,"125, Mount Road",Chennai,TamilNadu,50000
1005,vvvvvvvvv,"86, Cross Street",Chennai,TamilNadu,45000

How to Generate Format file using BCP Utility in SQL Server

We can manually create the format file in notepad but we have an option to generate the format file using BCP utility.

Open the Command prompt and paste below mentioned line.

bcp dbname.schema.Employee format -c -t -f d:\Employee.fmt -T

Once you have entered above line the utility will ask each field data type, prefix length and field terminator. The utility would not give the format with text qualifier and we need to add it manually as below.

11.0
6
1 SQLCHAR 0 0 "," 1 Employee_Id ""
2 SQLCHAR 0 0 ",\"" 2 Employee_Name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 "\"," 3 Employee_Address SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 0 "," 4 Employee_City SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 0 "," 5 Employee_State SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 0 "\r\n" 6 Employee_Salary ""

We can save manually the above format as Employee.fmt file name

Format File content and Description

Content	Description
11.0	Indicates SQL Server Version.
6	Number of columns.
First column	Sequence of field.
Second Column	Data Type.
Third Column	Minimum Length.
Fourth Column	Maximum length.
Fifth Column	Delimiter with Text Qualifier.
Sixth Column	Table column number.
Seventh Column	Table column name.
Last Column	Indicate the database is case sensitive.

XML format for BULK INSERT in SQL Server

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR=',' />
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=',"'/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR='",'/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR=','/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR=','/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR='\n'/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Employee_Id" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="Employee_Name" xsi:type="SQLNVARCHAR" LENGTH="50"/>
<COLUMN SOURCE="3" NAME="Employee_Address" xsi:type="SQLNVARCHAR" LENGTH="50"/>
<COLUMN SOURCE="4" NAME="Employee_City" xsi:type="SQLNVARCHAR" LENGTH="50"/>
<COLUMN SOURCE="5" NAME="Employee_State" xsi:type="SQLNVARCHAR" LENGTH="30"/>
<COLUMN SOURCE="6" NAME="Employee_Salary" xsi:type="SQLINT"/>
</ROW>
</BCPFORMAT>

We can use either .FMT or XML format file to load the data from flat file to SQL table using BCP or BULK INSERT method.

Also see below articles

Import bulk data from flat file to SQL Server table using BCP and BULK INSERT
Monitoring and Performance Tuning Tips for SQL Server database
SQL Server/Developer Interview Questions and Answers for Experienced Professional