Använda en formatfil för att hoppa över ett datafält (SQL Server)

Artikel
2025-03-05

gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

En datafil kan innehålla fler fält än antalet kolumner i tabellen. I det här avsnittet beskrivs hur du ändrar både icke-XML- och XML-formatfiler för att hantera en datafil med fler fält genom att mappa tabellkolumnerna till motsvarande datafält och ignorera de extra fälten. Mer information finns i Skapa en formatfil (SQL Server).

Anteckning

Antingen kan en icke-XML- eller XML-formatfil användas för att massimportera en datafil till tabellen med hjälp av ett bcp-verktyg kommando, BULK INSERT--instruktion eller INSERT ... SELECT * FROM OPENROWSET(BULK...)-instruktion. Mer information finns i Använda en formatfil för att massimportera data (SQL Server).

Not

Denna syntax, inklusive bulkinsättning, stöds inte i Azure Synapse Analytics. I Azure Synapse Analytics och andra molndatabasplattformsintegreringar utför du dataflytt via COPY-instruktionen i Azure Data Factoryeller med hjälp av T-SQL-instruktioner som COPY INTO och PolyBase.

Exempel på testvillkor

Exemplen på ändrade formatfiler i det här avsnittet baseras på tabellen och datafilen som definieras nedan.

Exempeltabell

Skriptet nedan skapar en testdatabas och en tabell med namnet myTestSkipField. Kör följande Transact-SQL i Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

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

Exempeldatafil

Skapa en tom fil D:\BCP\myTestSkipField.bcp och infoga följande data:

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

Skapa formatfilerna

Om du vill massimportera data från myTestSkipField.bcp till tabellen myTestSkipField måste formatfilen göra följande:

Mappa det första datafältet till den första kolumnen PersonID.
Hoppa över det andra datafältet.
Mappa det tredje datafältet till den andra kolumnen, FirstName.
Mappa det fjärde datafältet till den tredje kolumnen LastName.

Den enklaste metoden för att skapa formatfilen är med hjälp av bcp-verktyget. Skapa först en basformatfil från den befintliga tabellen. För det andra ändrar du basformatfilen så att den återspeglar den faktiska datafilen.

Skapa en fil som inte är XML-format

Granska Filer i icke-XML-format (SQL Server) för detaljerad information. Följande kommando använder verktyget bcp för att generera en fil som inte är xml-format, myTestSkipField.fmt, baserat på schemat för myTestSkipField. Dessutom används c för att ange teckendata , t, används för att ange ett kommatecken som fältavgränsare och T används för att ange en betrodd anslutning med integrerad säkerhet. I en kommandotolk anger du följande kommando:

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

Ändra filen med icke-XML-format

Granska struktur för filer som inte är XML-format för terminologi. Öppna D:\BCP\myTestSkipField.fmt i Anteckningar och utför följande ändringar:

Kopiera hela formatfilraden för FirstName och klistra in den direkt efter FirstName på nästa rad.
Öka värdet för fältordningen för värdfilen med en för den nya raden och alla efterföljande rader.
Öka antalet kolumner för att återspegla det faktiska antalet fält i datafilen.
Ändra serverkolumnordningen från 2 till 0 för den andra formatfilraden.

Jämför de ändringar som gjorts:

före

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

efter

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

Den ändrade formatfilen återspeglar nu:

4 datafält
Det första datafältet i myTestSkipField.bcp mappas till den första kolumnen, myTestSkipField.. PersonID
Det andra datafältet i myTestSkipField.bcp mappas inte till någon kolumn.
Det tredje datafältet i myTestSkipField.bcp mappas till den andra kolumnen, myTestSkipField.. FirstName
Det fjärde datafältet i myTestSkipField.bcp mappas till den tredje kolumnen myTestSkipField.. LastName

Skapa en XML-formatfil

Se XML-formatfiler (SQL Server) för mer detaljerad information. Följande kommando använder verktyget bcp för att skapa en XML-formatfil, myTestSkipField.xml, baserat på schemat för myTestSkipField. Dessutom används c för att ange teckendata , t, används för att ange ett kommatecken som fältavgränsare och T används för att ange en betrodd anslutning med integrerad säkerhet. x-kvalificeraren måste användas för att generera en XML-baserad formatfil. I en kommandotolk anger du följande kommando:

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

Ändra XML-formatfilen

Granska schemasyntax för XML-formatfiler för terminologi. Öppna D:\BCP\myTestSkipField.xml i Anteckningar och utför följande ändringar:

Kopiera hela det andra fältet och klistra in det direkt efter det andra fältet på nästa rad.
Öka värdet "FÄLT-ID" med 1 för det nya FÄLTET och för varje efterföljande FÄLT.
Öka värdet "KOLUMNKÄLLA" med 1 för FirstNameoch LastName för att återspegla den reviderade mappningen.

Jämför de ändringar som gjorts:

före

<?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>

efter

<?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>

Den ändrade formatfilen återspeglar nu:

4 datafält
FÄLT 1 som motsvarar KOLUMN 1 mappas till den första tabellkolumnen myTestSkipField.. PersonID
FÄLT 2 motsvarar inte någon KOLUMN och mappas därför inte till någon tabellkolumn.
FÄLT 3 som motsvarar KOLUMN 3 mappas till den andra tabellkolumnen myTestSkipField.. FirstName
FÄLT 4 som motsvarar KOLUMN 4 mappas till den tredje tabellkolumnen myTestSkipField.. LastName

Importera data med en formatfil för att hoppa över ett datafält

I exemplen nedan används databasen, datafilen och formatfilerna som skapades ovan.

Använd bcp-- och filformat som inte är XML-format

I en kommandotolk anger du följande kommando:

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

Använd bcp och XML formatfil

I en kommandotolk anger du följande kommando:

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

Använd BULK INSERT och icke-XML-formatfil

Kör följande Transact-SQL i 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;

Använd BULK INSERT och XML-formatfil

Kör följande Transact-SQL i 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;

Använd OPENROWSET(BULK...) och icke-XML-formatfil

Kör följande Transact-SQL i 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;

Använd OPENROWSET(BULK...) och XML-formatfilen

Kör följande Transact-SQL i 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;

Dela via

Använda en formatfil för att hoppa över ett datafält (SQL Server)

Exempel på testvillkor

Exempeltabell

Exempeldatafil

Skapa formatfilerna

Skapa en fil som inte är XML-format

Ändra filen med icke-XML-format

Skapa en XML-formatfil

Ändra XML-formatfilen

Importera data med en formatfil för att hoppa över ett datafält

Använd bcp-- och filformat som inte är XML-format

Använd bcp och XML formatfil

Använd BULK INSERT och icke-XML-formatfil

Använd BULK INSERT och XML-formatfil

Använd OPENROWSET(BULK...) och icke-XML-formatfil

Använd OPENROWSET(BULK...) och XML-formatfilen

Nästa steg

Feedback

Ytterligare resurser