Use a format file to skip a data field (SQL Server)

2025-06-18

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

A data file can contain more fields than the number of columns in the table. This article describes modifying both non-XML and XML format files to accommodate a data file with more fields by mapping the table columns to the corresponding data fields and ignoring the extra fields.

For more information, see Create a format file with bcp (SQL Server).

Note

Either a non-XML or XML format file can be used to bulk import a data file into the table by using a bcp utility command, BULK INSERT (Transact-SQL) statement, or INSERT ... SELECT * FROM OPENROWSET BULK (Transact-SQL) statement. For more information, see Use a format file to bulk import data (SQL Server).

Note

This syntax, including bulk insert, is not supported in Azure Synapse Analytics. In Azure Synapse Analytics and other cloud database platform integrations, accomplish data movement via the COPY statement in Azure Data Factory, or by using T-SQL statements such as COPY INTO and PolyBase.

Example test conditions

The examples of modified format files in this article are based on the sample table myTestSkipField and data file D:\BCP\myTestSkipField.bcp. Change the local file location in the code sample to a file location on your machine.

Sample table

The script creates a test database and a table named myTestSkipField. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

Sample data file

Create an empty file D:\BCP\myTestSkipField.bcp and insert the following data:

1,SkipMe,Anthony,Grosse
2,SkipMe,Alica,Fatnowna
3,SkipMe,Stella,Rosenhain

Create the format files

To bulk import data from myTestSkipField.bcp into the myTestSkipField table, the format file must do the following:

Map the first data field to the first column, PersonID.
Skip the second data field.
Map the third data field to the second column, FirstName.
Map the fourth data field to the third column, LastName.

The simplest method to create the format file is by using the bcp utility. First, create a base format file from the existing table. Second, modify the base format file to reflect the actual data file.

Create a non-XML format file

Review Use Non-XML format files (SQL Server) for detailed information. The following command will use the bcp utility to generate a non-xml format file, myTestSkipField.fmt, based on the schema of myTestSkipField. In addition, the qualifier c is used to specify character data , t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. At a command prompt, enter the following command:

bcp TestDatabase.dbo.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.fmt -t, -T

Modify the non-XML format file

Review the structure of non-XML format files for terminology. Open D:\BCP\myTestSkipField.fmt in Notepad and perform the following modifications:

Copy the entire format-file row for FirstName and paste it directly after FirstName on the next line.
Increase the host file field order value by one for the new row and all subsequent rows.
Increase the number of columns value to reflect the actual number of fields in the data file.
Modify the server column order from 2 to 0 for the second format-file row.

Compare the changes made:

Before

13.0
3
1       SQLCHAR    0       7       ","      1     PersonID        ""
2       SQLCHAR    0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       30      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

After

13.0
4
1       SQLCHAR    0       7       ","      1     PersonID     ""
2       SQLCHAR    0       25      ","      0     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR    0       50      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

The modified format file now reflects:

4 data fields
The first data field in myTestSkipField.bcp is mapped to the first column, myTestSkipField.. PersonID
The second data field in myTestSkipField.bcp is not mapped to any column.
The third data field in myTestSkipField.bcp is mapped to the second column, myTestSkipField.. FirstName
The fourth data field in myTestSkipField.bcp is mapped to the third column, myTestSkipField.. LastName

Create an XML format file

Review XML format files (SQL Server) for detailed information. The following command will use the bcp utility to create an xml format file, myTestSkipField.xml, based on the schema of myTestSkipField.

The qualifier c is used to specify character data
t, is used to specify a comma as a field terminator
T is used to specify a trusted connection using integrated security.
The x qualifier must be used to generate an XML-based format file.

At a command prompt, enter the following command:

bcp TestDatabase.dbo.myTestSkipField format nul -c -x -f D:\BCP\myTestSkipField.xml -t, -T

Modify the XML format file

Review schema syntax for XML format files for terminology. Open D:\BCP\myTestSkipField.xml in Notepad and perform the following modifications:

Copy the entire second field and paste it directly after the second field on the next line.
Increase the FIELD ID value by 1 for the new FIELD and for each subsequent FIELD.
Increase the COLUMN SOURCE value by 1 for FirstName, and LastName to reflect the revised mapping.

Compare the changes made:

Before

<?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="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

After

<?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="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

The modified format file now reflects:

4 data fields
FIELD 1 which corresponds to COLUMN 1 is mapped to the first table column, myTestSkipField.. PersonID
FIELD 2 does not correspond to any COLUMN and thus, is not mapped to any table column.
FIELD 3 which corresponds to COLUMN 3 is mapped to the second table column, myTestSkipField.. FirstName
FIELD 4 which corresponds to COLUMN 4 is mapped to the third table column, myTestSkipField.. LastName

Import data with a format file to skip a data field

The examples use the sample database, datafile, and format files created in this article.

Use bcp and non-XML format file

At a command prompt, enter the following command:

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.fmt -T

Use bcp and XML format files (SQL Server)

At a command prompt, enter the following command:

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.xml -T

Use BULK INSERT (Transact-SQL) and non-XML format file

Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField  
   FROM 'D:\BCP\myTestSkipField.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.fmt');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Use BULK INSERT (Transact-SQL) and XML format files (SQL Server)

Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField  
   FROM 'D:\BCP\myTestSkipField.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.xml');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Use OPENROWSET BULK (Transact-SQL) and non-XML format file

Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.fmt'
        ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Use OPENROWSET BULK (Transact-SQL) and XML format files (SQL Server)

Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField  
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.xml'  
       ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

Share via

Use a format file to skip a data field (SQL Server)

Example test conditions

Sample table

Sample data file

Create the format files

Create a non-XML format file

Modify the non-XML format file

Create an XML format file

Modify the XML format file

Import data with a format file to skip a data field

Use bcp and non-XML format file

Use bcp and XML format files (SQL Server)

Use BULK INSERT (Transact-SQL) and non-XML format file

Use BULK INSERT (Transact-SQL) and XML format files (SQL Server)

Use OPENROWSET BULK (Transact-SQL) and non-XML format file

Use OPENROWSET BULK (Transact-SQL) and XML format files (SQL Server)

Related content

Feedback

Additional resources