Verwenden einer Formatdatei zum Zuordnen von Tabellenspalten zu Datendateifeldern (SQL Server)Use a Format File to Map Table Columns to Data-File Fields (SQL Server)

Eine Datendatei kann Felder in einer anderen Reihenfolge als die der entsprechenden Spalten in der Tabelle aufweisen.A data file can contain fields arranged in a different order from the corresponding columns in the table. In diesem Thema werden Nicht-XML-Formatdateien und XML-Formatdateien dargestellt, die zum Anpassen an eine Datendatei, deren Felder eine andere Reihenfolge als die Tabellenspalten aufweisen, geändert wurden.This topic presents both non-XML and XML format files that have been modified to accommodate a data file whose fields are arranged in a different order from the table columns. Die geänderte Formatdatei ordnet die Datenfelder den entsprechenden Tabellenspalten zu.The modified format file maps the data fields to their corresponding table columns. Weitere Informationen finden Sie unter Erstellen einer Formatdatei (SQL Server) .Please review Create a Format File (SQL Server) for additional information.

OutlineOutline
BeispieltestbedingungenExample Test Conditions
 ● Beispieltabelle ● Sample Table
 ● Beispieldatendatei ● Sample Data File
Erstellen der FormatdateienCreating the Format Files
 ● Erstellen einer Nicht-XML-Formatdatei ● Creating a Non-XML Format File
 ● Ändern der Nicht-XML-Formatdatei ● Modifying the Non-XML Format File
 ● Erstellen einer XML-Formatdatei ● Creating an XML Format File
 ● Ändern der XML-Formatdatei ● Modifying the XML Format File
Importieren von Daten mit einer Formatdatei, um Tabellenspalten zu Datendateifeldern zuzuordnenImporting Data with a Format File to Map Table Columns to Data-File Field
 ● Verwenden von BCP und der Nicht-XML-Formatdatei ● Using bcp and Non-XML Format File
 ● Verwenden von BCP und der XML-Formatdatei ● Using bcp and XML Format File
 ● Verwenden von BULK INSERT und der Nicht-XML-Formatdatei ● Using BULK INSERT and Non-XML Format File
 ● Verwenden von BULK INSERT und der XML-Formatdatei ● Using BULK INSERT and XML Format File
 ● Verwenden von OPENROWSET(BULK...) und der Nicht-XML-Formatdatei ● Using OPENROWSET(BULK...) and Non-XML Format File
 ● Verwenden von OPENROWSET(BULK...) und der XML-Formatdatei ● Using OPENROWSET(BULK...) and XML Format File

Hinweis

Mithilfe einer Nicht-XML- oder XML-Formatdatei kann der Massenimport einer Datendatei in die Tabelle ausgeführt werden. Hierzu kann ein bcp-Hilfsprogramm-Befehl, eine BULK INSERT- oder eine INSERT ... SELECT * FROM OPENROWSET(BULK...)-Anweisung verwendet werden.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 statement, or INSERT ... SELECT * FROM OPENROWSET(BULK...) statement. Weitere Informationen finden Sie unter Massenimport von Daten mithilfe einer Formatdatei (SQL Server).For more information, see Use a Format File to Bulk Import Data (SQL Server).

BeispieltestbedingungenExample Test Conditions

Die in diesem Thema enthaltenen Beispiele der geänderten Formatdateien basieren auf der Tabelle und der Datendatei, die nachstehend definiert sind.The examples of modified format files in this topic are based on the table and data file defined below.

BeispieltabelleSample Table

Das folgende Skript erstellt eine Testdatenbank und eine Tabelle namens myRemap.The script below creates a test database and a table named myRemap. Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myRemap
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   Gender char(1)
   );

BeispieldatendateiSample Data File

Die nachstehenden Daten entsprechen FirstName und LastName in umgekehrter Reihenfolge, wie in der Tabelle myRemapdargestellt.The data below presents FirstName and LastName in the reverse order as presented in the table myRemap. Erstellen Sie mit Editor die leere Datei D:\BCP\myRemap.bcp , und fügen Sie die folgenden Daten ein:Using Notepad, create an empty file D:\BCP\myRemap.bcp and insert the following data:

1,Grosse,Anthony,M
2,Fatnowna,Alica,F
3,Rosenhain,Stella,F

Erstellen der FormatdateienCreating the Format Files

Wenn ein Massenimport für Daten aus myRemap.bcp in die myRemap -Tabelle ausgeführt werden soll, muss die Formatdatei folgende Funktionen ausführen:To bulk import data from myRemap.bcp into the myRemap table, the format file must do the following:

  • Der ersten Spalte ( PersonID) das erste Datenfeld zuordnen.Map the first data field to the first column, PersonID.
  • Der dritten Spalte ( LastName) das zweite Datenfeld zuordnen.Map the second data field to the third column, LastName.
  • Der zweiten Spalte ( FirstName) das dritte Datenfeld zuordnen.Map the third data field to the second column, FirstName.
  • Der vierten Spalte ( Gender) das vierte Datenfeld zuordnen.Map the fourth data field to the fourth column, Gender.

Die einfachste Methode zum Erstellen der Formatdatei besteht darin, das bcp-Dienstprogrammzu verwenden.The simplest method to create the format file is by using the bcp utility. Erstellen Sie zunächst eine Basisformatdatei aus der vorhandenen Tabelle.First, create a base format file from the existing table. Ändern Sie dann die Basisformatdatei so, dass sie der tatsächlichen Datendatei entspricht.Second, modify the base format file to reflect the actual data file.

Erstellen einer Nicht-XML-FormatdateiCreating a Non-XML Format File

Ausführliche Informationen finden Sie unter Nicht-XML-Formatdateien (SQL Server) .Please review Non-XML Format Files (SQL Server) for detailed information. Im folgenden Befehl wird das bcp-Hilfsprogramm verwendet, um die Nicht-XML-Formatdatei myRemap.fmtzu erstellen, die auf dem Schema von myRemapbasiert.The following command will use the bcp utility to generate a non-xml format file, myRemap.fmt, based on the schema of myRemap. Außerdem wird der Qualifizierer c verwendet, um Zeichendaten anzugeben, wird t, verwendet, um ein Komma als Feldabschlusszeichen anzugeben, und wird T verwendet, um eine vertrauenswürdige Verbindung anzugeben, für die integrierte Sicherheit verwendet wird.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. Geben Sie folgenden Befehl an der Eingabeaufforderung ein:At a command prompt, enter the following command:

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

Ändern der Nicht-XML-Formatdatei Modifying the Non-XML Format File

Informationen zur Terminologie finden Sie unter Struktur von Nicht-XML-Formatdateien .See Structure of Non-XML Format Files for terminology. Öffnen Sie D:\BCP\myRemap.fmt in Editor, und nehmen Sie die folgenden Änderungen vor:Open D:\BCP\myRemap.fmt in Notepad and perform the following modifications: 1) Ändern Sie die Reihenfolge der Zeilen in der Formatdatei, sodass die Zeilen in derselben Reihenfolge vorliegen wie die Daten in myRemap.bcp.Re-arrange the order of the format-file rows so that the rows are in the same order as the data in myRemap.bcp. 2) Stellen Sie sicher, dass die Werte für die Reihenfolge der Hostfelder sequenziell sind.Ensure the host file field order values are sequential. 3) Stellen Sie sicher, dass nach der letzten Zeile der Formatdatei ein Zeilenumbruch eingefügt wird.Ensure there is a carriage return after the last format-file row.

Vergleichen Sie die Änderungen:Compare the changes:
vorBefore

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

NachAfter

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

Die geänderte Formatdatei entspricht nun Folgendem:The modified format file now reflects:

  • Das erste Datenfeld in myRemap.bcp wird der ersten Spalte zugeordnet, myRemap.. PersonIDThe first data field in myRemap.bcp is mapped to the first column, myRemap.. PersonID
  • Das zweite Datenfeld in myRemap.bcp wird der dritten Spalte zugeordnet, myRemap.. LastNameThe second data field in myRemap.bcp is mapped to the third column, myRemap.. LastName
  • Das dritte Datenfeld in myRemap.bcp wird der zweiten Spalte zugeordnet, myRemap.. FirstNameThe third data field in myRemap.bcp is mapped to the second column, myRemap.. FirstName
  • Das vierte Datenfeld in myRemap.bcp wird der vierten Spalte zugeordnet, myRemap.. GenderThe fourth data field in myRemap.bcp is mapped to the fourth column, myRemap.. Gender

Erstellen einer XML-Formatdatei Creating an XML Format File

Ausführliche Informationen finden Sie unter XML-Formatdateien (SQL Server) .Please review XML Format Files (SQL Server) for detailed information. Im folgenden Befehl wird das bcp-Hilfsprogramm verwendet, um die XML-Formatdatei myRemap.xmlzu erstellen, die auf dem Schema von myRemapbasiert.The following command will use the bcp utility to create an xml format file, myRemap.xml, based on the schema of myRemap. Außerdem wird der Qualifizierer c verwendet, um Zeichendaten anzugeben, wird t, verwendet, um ein Komma als Feldabschlusszeichen anzugeben, und wird T verwendet, um eine vertrauenswürdige Verbindung anzugeben, für die integrierte Sicherheit verwendet wird.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. Der x -Qualifizierer muss verwendet werden, um eine XML-basierte Formatdatei zu generieren.The x qualifier must be used to generate an XML-based format file. Geben Sie folgenden Befehl an der Eingabeaufforderung ein:At a command prompt, enter the following command:

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

Ändern der XML-Formatdatei Modifying the XML Format File

Informationen zur Terminologie finden Sie unter Schemasyntax für XML-Formatdateien .See Schema Syntax for XML Format Files for terminology. Öffnen Sie D:\BCP\myRemap.xml in Editor, und nehmen Sie die folgenden Änderungen vor:Open D:\BCP\myRemap.xml in Notepad and perform the following modifications: 1) Die Reihenfolge, in der die <FIELD>-Elemente in der Formatdatei deklariert sind, ist die Reihenfolge, in der diese Felder in der Datendatei angezeigt werden. Kehren Sie daher die Reihenfolge für die <FIELD>-Elemente mit den ID-Attributen 2 und 3 um.The order in which the <FIELD> elements are declared in the format file is the order in which those fields appear in the data file, thus reverse the order for the <FIELD> elements with ID attributes 2 and 3. 2) Stellen Sie sicher, dass die Werte des <FIELD>-Attributs ID sequenziell sind.Ensure the <FIELD> ID attribute values are sequential. 3) Die Reihenfolge der <COLUMN>-Elemente im <ROW>-Element definiert, in welcher Reihenfolge diese durch den Massenvorgang zurückgegeben werden.The order of the <COLUMN> elements in the <ROW> element defines the order in which they are returned by the bulk operation. Die XML-Formatdatei weist jedem <COLUMN>-Element einen lokalen Namen zu, der keine Verbindung zu der Spalte in der Zieltabelle des Massenimportvorgangs aufweist.The XML format file assigns each <COLUMN> element a local name that has no relationship to the column in the target table of a bulk import operation. Die Reihenfolge der <COLUMN>-Elemente ist unabhängig von der Reihenfolge der <FIELD>-Elemente in einer <RECORD>-Definition.The order of the <COLUMN> elements is independent of the order of <FIELD> elements in a <RECORD> definition. Jedes <COLUMN>-Element entspricht einem <FIELD>-Element (dessen ID im SOURCE-Attribut des <COLUMN>-Elements angegeben ist).Each <COLUMN> element corresponds to a <FIELD> element (whose ID is specified in the SOURCE attribute of the <COLUMN> element). Somit sind die Werte für <COLUMN> SOURCE die einzigen Attribute, die eine Überarbeitung erfordern.Thus, the values for <COLUMN> SOURCE are the only attributes that require revision. Kehren Sie die Reihenfolge für die <COLUMN>-Attribute SOURCE 2 und 3 um.Reverse the order for <COLUMN> SOURCE attributes 2 and 3.

Vergleichen Sie die Änderungen:Compare the changes
vorBefore

\<?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="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  \<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" 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"/>
  \<COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
 </ROW>
</BCPFORMAT>

NachAfter

\<?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="30" 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="1" 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="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  \<COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
 </ROW>
</BCPFORMAT>

Die geänderte Formatdatei entspricht nun Folgendem:The modified format file now reflects:

  • FIELD 1, das COLUMN 1 entspricht, wird der ersten Tabellenspalte zugeordnet, myRemap.. PersonIDFIELD 1, which corresponds to COLUMN 1, is mapped to the first table column, myRemap.. PersonID
  • FIELD 2, das COLUMN 2 entspricht, wird der dritten Tabellenspalte neu zugeordnet, myRemap.. LastNameFIELD 2, which corresponds to COLUMN 2, is re-mapped to the third table column, myRemap.. LastName
  • FIELD 3, das COLUMN 3 entspricht, wird der zweitten Tabellenspalte neu zugeordnet, myRemap.. FirstNameFIELD 3, which corresponds to COLUMN 3, is re-mapped to the second table column, myRemap.. FirstName
  • FIELD 4, das COLUMN 4 entspricht, wird der vierten Tabellenspalte zugeordnet, myRemap.. GenderFIELD 4, which corresponds to COLUMN 4, is mapped to the fourth table column, myRemap.. Gender

Importieren von Daten mit einer Formatdatei, um Tabellenspalten zu Datendateifeldern zuzuordnenImporting Data with a Format File to Map Table Columns to Data-File Field

In den folgenden Beispielen werden die Datenbank, die Datendatei und die Formatdateien verwendet, die oben erstellt wurden.The examples below use the database, datafile, and format files created above.

Verwenden von bcp und der Nicht-XML-FormatdateiUsing bcp and Non-XML Format File

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:At a command prompt, enter the following command:

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

Verwenden von bcp und der XML-FormatdateiUsing bcp and XML Format File

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:At a command prompt, enter the following command:

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

Verwenden von BULK INSERT und der Nicht-XML-FormatdateiUsing BULK INSERT and Non-XML Format File

Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

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

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

Verwenden von BULK INSERT und der XML-FormatdateiUsing BULK INSERT and XML Format File

Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

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

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

Verwenden von OPENROWSET(BULK...) und der Nicht-XML-FormatdateiUsing OPENROWSET(BULK...) and Non-XML Format File

Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

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

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

Verwenden von OPENROWSET(BULK...) und der XML-FormatdateiUsing OPENROWSET(BULK...) and XML Format File

Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

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

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

Siehe auchSee Also

bcp Utility bcp Utility
Überspringen einer Tabellenspalte mithilfe einer Formatdatei (SQL Server) Use a Format File to Skip a Table Column (SQL Server)
Auslassen eines Datenfelds mithilfe einer Formatdatei (SQL Server) Use a Format File to Skip a Data Field (SQL Server)