use-a-format-file-to-skip-a-data-field-sql-server.md

title	Use a Format File to Skip a Data Field (SQL Server) | Microsoft Docs
ms.custom
ms.date	06/13/2017
ms.prod	sql-server-2014
ms.reviewer
ms.suite
ms.technology	dbe-bulk-import-export
ms.tgt_pltfrm
ms.topic	article
helpviewer_keywords	format files [SQL Server], skipping data fields skipping data fields when importing
ms.assetid	6a76517e-983b-47a1-8f02-661b99859a8b
caps.latest.revision	33
author	JennieHubbard
ms.author	jhubbard
manager	jhubbard

Use a Format File to Skip a Data Field (SQL Server)

A data file can contain more fields than the number of columns in the table. This topic 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.

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 command, BULK INSERT statement, or INSERT ... SELECT * FROM OPENROWSET(BULK...) statement. For more information, see Use a Format File to Bulk Import Data (SQL Server).

Sample Data File and Table

The examples of modified format files in this topic are based on the following table and data file.

Sample Table

The examples require that a table named myTestSkipField be created in the [!INCLUDEssSampleDBnormal] sample database under the dbo schema. To create this table, in [!INCLUDEmsCoName] [!INCLUDEssManStudioFull] Query Editor, run the following code:

USE AdventureWorks2012;  
GO  
CREATE TABLE myTestSkipField   
   (  
   PersonID smallint,  
   FirstName nvarchar(50) ,  
   LastName nvarchar(50)   
   );  
GO  

Sample Data File

The data file, myTestSkipField-c.dat, contains the following records:

1,Skipme,DataField3,DataField4  
1,Skipme,DataField3,DataField4  
1,Skipme,DataField3,DataField4  

To bulk import data from myTestSkipField-c.dat 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.

Non-XML Format File for More Data Fields

The following format file, myTestSkipField.fmt, maps the fields in myTestSkipField-c.dat to the columns of the myTestSkipField table. The format file uses character data format. Skipping a column mapping requires changing its column order value to 0, as shown for the ExtraField column in the format file.

The myTestSkipField.fmt format file contains the following information:

9.0  
4  
1       SQLCHAR       0       7       ","      1     PersonID               ""  
2       SQLCHAR       0       100       ","    0     ExtraField             SQL_Latin1_General_CP1_CI_AS  
3       SQLCHAR       0       100     ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS  
4       SQLCHAR       0       100     "\r\n"   3     LastName               SQL_Latin1_General_CP1_CI_AS  
  

Note

For information about the syntax of non-XML format files, see Non-XML Format Files (SQL Server).

Examples

The following example uses INSERT ... SELECT * FROM OPENROWSET(BULK...) using the myTestSkipField.fmt format file. The example bulk imports the myTestSkipField-c.dat data file into the myTestSkipField table. To create the sample table and data file, see "Sample Data File and Table," earlier in this topic.

In the [!INCLUDEssManStudioFull] Query Editor, run the following code:

USE AdventureWorks2012;  
GO  
INSERT INTO myTestSkipField   
   SELECT *  
      FROM  OPENROWSET(BULK  'C:\myTestSkipField-c.dat',  
      FORMATFILE='C:\myTestSkipField.fmt'    
       ) AS t1;  
GO   

XML Format File for More Data Fields

The format file presented in this example is based on another format file, myTestSkipField.xml, which uses character data format throughout and whose fields correspond exactly in number and order to the columns in the myTestSkipField table. To view the contents of that format file, see Create a Format File (SQL Server).

The following format file, myTestSkipField.xml, maps the fields in myTestSkipField-c.dat to the columns of the myTestSkipField table. The format file uses character data format.

The myTestSkipField.xml format file contains the following information:

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

Examples

The following example uses INSERT ... SELECT * FROM OPENROWSET(BULK...) using the myTestSkipField.Xml format file. The example bulk imports the myTestSkipField-c.dat data file into the myTestSkipField table. To create the sample table and data file, see "Sample Data File and Table," earlier in this topic.

In the [!INCLUDEssManStudioFull] Query Editor, run the following code:

USE AdventureWorks2012;  
GO  
INSERT INTO myTestSkipField   
  SELECT *  
      FROM  OPENROWSET(BULK  'C:\myTestSkipField-c.dat',  
      FORMATFILE='C:\myTestSkipField.xml'    
       ) AS t1;  
GO  
  

Note

For information about the syntax of the XML Schema and additional samples of XML format files, see XML Format Files (SQL Server).

See Also

bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Use a Format File to Skip a Table Column (SQL Server)
Use a Format File to Map Table Columns to Data-File Fields (SQL Server)