Using a Format File to Skip a Data Field

Switch View

Using a Format File to Skip a Data ...

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (October 2009)

Using a Format File to Skip a Data Field

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 Using a Format File to Bulk Import Data.

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 AdventureWorks sample database under the dbo schema. To create this table, in Microsoft SQL Server Management Studio Query Editor, run the following code:

USE AdventureWorks;
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 Understanding Non-XML Format Files.

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 SQL Server Management Studio Query Editor, run the following code:

USE AdventureWorks;
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 Creating a Format File.

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 SQL Server Management Studio Query Editor, run the following code:

USE AdventureWorks;
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 Understanding XML Format Files.

Sample Table

Sample Data File

Examples

Examples

Concepts

Other Resources

Help and Information