Usar un archivo de formato para asignar columnas de tabla a campos de un archivo de datos (SQL Server)Use a Format File to Map Table Columns to Data-File Fields (SQL Server)

Un archivo de datos puede contener campos organizados en un orden diferente al de las columnas correspondientes en la tabla.A data file can contain fields arranged in a different order from the corresponding columns in the table. Este tema trata los archivos de formato XML y no XML que se han modificado para alojar un archivo de datos cuyos campos están organizados en un orden diferente al de las columnas de la tabla.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. El archivo de formato modificado asigna los campos de datos a las columnas correspondientes de la tabla.The modified format file maps the data fields to their corresponding table columns. Revise Crear un archivo de formato (SQL Server) para información adicional.Please review Create a Format File (SQL Server) for additional information.

EsquemaOutline
Condiciones de prueba de ejemploExample Test Conditions
 ● Tabla de ejemplo ● Sample Table
 ● Archivo de datos de ejemplo ● Sample Data File
Creación de los archivos de formatoCreating the Format Files
 ● Creación de un archivo de formato no XML ● Creating a Non-XML Format File
 ● Modificación del archivo de formato no XML ● Modifying the Non-XML Format File
 ● Creación de un archivo de formato XML ● Creating an XML Format File
 ● Modificación del archivo de formato XML ● Modifying the XML Format File
Importación de datos con un archivo de formato para asignar columnas de tabla a campos de un archivo de datosImporting Data with a Format File to Map Table Columns to Data-File Field
 ● Uso de bcp y un archivo de formato no XML ● Using bcp and Non-XML Format File
 ● Uso de bcp y un archivo de formato XML ● Using bcp and XML Format File
 ● Uso de BULK INSERT y un archivo de formato no XML ● Using BULK INSERT and Non-XML Format File
 ● Uso de BULK INSERT y un archivo de formato XML ● Using BULK INSERT and XML Format File
 ● Uso de OPENROWSET(BULK...) y archivo de formato no XML ● Using OPENROWSET(BULK...) and Non-XML Format File
 ● Uso de OPENROWSET(BULK...) y archivo de formato XML ● Using OPENROWSET(BULK...) and XML Format File

Nota

Puede usarse un archivo de formato XML o no XML para importar en bloque un archivo de datos en la tabla mediante un comando de la utilidad bcp, una instrucción BULK INSERT o una instrucción INSERT… SELECT * FROM OPENROWSET(BULK...).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. Para obtener más información, vea Usar un archivo de formato para importar datos en bloque (SQL Server).For more information, see Use a Format File to Bulk Import Data (SQL Server).

Condiciones de prueba de ejemploExample Test Conditions

Los ejemplos de archivos de formato modificados de este tema se basan en la tabla y archivo de datos definidos a continuación.The examples of modified format files in this topic are based on the table and data file defined below.

Tabla de ejemploSample Table

El script siguiente crea una base de datos de prueba y una tabla llamada myRemap.The script below creates a test database and a table named myRemap. Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):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)
   );

Archivo de datos de ejemploSample Data File

Los datos siguientes presentan FirstName y LastName en el orden inverso según se presentan en la tabla myRemap.The data below presents FirstName and LastName in the reverse order as presented in the table myRemap. Mediante el Bloc de notas, cree un archivo vacío D:\BCP\myRemap.bcp e inserte los datos siguientes: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

Creación de los archivos de formatoCreating the Format Files

Para realizar una importación masiva de datos de myRemap.bcp en la tabla myRemap , el archivo de formato debe llevar a cabo lo siguiente:To bulk import data from myRemap.bcp into the myRemap table, the format file must do the following:

  • Asignar el primer campo de datos a la primera columna, PersonID.Map the first data field to the first column, PersonID.
  • Asignar el segundo campo de datos a la tercera columna, LastName.Map the second data field to the third column, LastName.
  • Asignar el tercer campo de datos a la segunda columna, FirstName.Map the third data field to the second column, FirstName.
  • Asignar el cuarto campo de datos a la cuarta columna, Gender.Map the fourth data field to the fourth column, Gender.

Se trata del método más sencillo para crear el archivo de formato mediante la utilidad bcp.The simplest method to create the format file is by using the bcp utility. En primer lugar, cree un archivo de formato base a partir de la tabla existente.First, create a base format file from the existing table. En segundo lugar, modifique el archivo de formato base para reflejar el archivo de datos real.Second, modify the base format file to reflect the actual data file.

Creación de un archivo de formato no XMLCreating a Non-XML Format File

Revise Archivos de formato no XML [SQL Server] para información detallada.Please review Non-XML Format Files (SQL Server) for detailed information. El siguiente comando hará uso de la utilidad bcp para generar un archivo de formato no xml, myRemap.fmt, basado en el esquema de myRemap.The following command will use the bcp utility to generate a non-xml format file, myRemap.fmt, based on the schema of myRemap. Además, el calificador c se utiliza para especificar los datos de caracteres, t, se utiliza para especificar una coma como terminador de campo y T se utiliza para especificar una conexión de confianza que usa seguridad integrada.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. En el símbolo del sistema, escriba el siguiente comando:At a command prompt, enter the following command:

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

Modificación del archivo de formato no XML Modifying the Non-XML Format File

Consulte Estructura de los archivos de formato no XML para terminología.See Structure of Non-XML Format Files for terminology. Abra D:\BCP\myRemap.fmt en el Bloc de notas y realice las modificaciones siguientes:Open D:\BCP\myRemap.fmt in Notepad and perform the following modifications: 1) Vuelva a organizar el orden de las filas del archivo de formato para que las filas se encuentren en el mismo orden que los datos de 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) Asegúrese de que los valores del orden del campo del archivo de host son secuenciales.Ensure the host file field order values are sequential. 3) Asegúrese de que hay un retorno de carro después de la última fila del archivo de formato.Ensure there is a carriage return after the last format-file row.

Compare los cambios:Compare the changes:
Antes delBefore

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

DespuésAfter

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

El archivo de formato modificado ahora refleja:The modified format file now reflects:

  • El primer campo de datos de myRemap.bcp está asignado a la primera columna, myRemap.. PersonIDThe first data field in myRemap.bcp is mapped to the first column, myRemap.. PersonID
  • El segundo campo de datos de myRemap.bcp está asignado a la tercera columna, myRemap.. LastNameThe second data field in myRemap.bcp is mapped to the third column, myRemap.. LastName
  • El tercer campo de datos de myRemap.bcp está asignado a la segunda columna, myRemap.. FirstNameThe third data field in myRemap.bcp is mapped to the second column, myRemap.. FirstName
  • El cuarto campo de datos de myRemap.bcp está asignado a la cuarta columna, myRemap.. GenderThe fourth data field in myRemap.bcp is mapped to the fourth column, myRemap.. Gender

Creación de un archivo de formato XML Creating an XML Format File

Revise Archivos de formato XML (SQL Server) para información detallada.Please review XML Format Files (SQL Server) for detailed information. El siguiente comando hará uso de la utilidad bcp para crear un archivo de formato xml, myRemap.xml, basado en el esquema de myRemap.The following command will use the bcp utility to create an xml format file, myRemap.xml, based on the schema of myRemap. Además, el calificador c se utiliza para especificar los datos de caracteres, t, se utiliza para especificar una coma como terminador de campo y T se utiliza para especificar una conexión de confianza que usa seguridad integrada.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. El calificador x se debe usar para generar un archivo de formato basado en XML.The x qualifier must be used to generate an XML-based format file. En el símbolo del sistema, escriba el siguiente comando:At a command prompt, enter the following command:

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

Modificación del archivo de formato XML Modifying the XML Format File

Consulte Sintaxis de esquema para archivos de formato XML para terminología.See Schema Syntax for XML Format Files for terminology. Abra D:\BCP\myRemap.xml en el Bloc de notas y realice las modificaciones siguientes:Open D:\BCP\myRemap.xml in Notepad and perform the following modifications: 1) El orden en el que se declaran los elementos <FIELD> en el formato de archivo es el orden en que esos campos aparecen en el archivo de datos, por tanto, invierta el orden de los elementos <FIELD> con atributos de identificador 2 y 3.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) Asegúrese de que los valores de atributo de identificador <FIELD> son secuenciales.Ensure the <FIELD> ID attribute values are sequential. 3) El orden de los elementos <COLUMN> del elemento <ROW> define el orden en el que la operación masiva los devuelve.The order of the <COLUMN> elements in the <ROW> element defines the order in which they are returned by the bulk operation. El archivo de formato XML asigna a cada elemento <COLUMN> un nombre local que no tiene ninguna relación con la columna de la tabla de destino de una operación de importación en bloque.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. El orden de los elementos <COLUMN> es independiente del orden de los elementos <FIELD> de una definición <RECORD>.The order of the <COLUMN> elements is independent of the order of <FIELD> elements in a <RECORD> definition. Cada elemento <COLUMN> corresponde a un elemento <FIELD> (cuyo identificador se especifica en el atributo SOURCE del elemento <COLUMN>).Each <COLUMN> element corresponds to a <FIELD> element (whose ID is specified in the SOURCE attribute of the <COLUMN> element). Por lo tanto, los valores de <COLUMN> SOURCE son los únicos atributos que requieren revisión.Thus, the values for <COLUMN> SOURCE are the only attributes that require revision. Invierta el orden de los atributos 2 y 3 de <COLUMN> SOURCE.Reverse the order for <COLUMN> SOURCE attributes 2 and 3.

Compare los cambios.Compare the changes
Antes delBefore

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

DespuésAfter

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

El archivo de formato modificado ahora refleja:The modified format file now reflects:

  • FIELD 1, que corresponde a COLUMN 1, se asigna a la primera columna de tabla, myRemap.. PersonIDFIELD 1, which corresponds to COLUMN 1, is mapped to the first table column, myRemap.. PersonID
  • FIELD 2, que corresponde a COLUMN 2, se reasigna a la tercera columna de tabla, myRemap.. LastNameFIELD 2, which corresponds to COLUMN 2, is re-mapped to the third table column, myRemap.. LastName
  • FIELD 3, que corresponde a COLUMN 3, se reasigna a la segunda columna de tabla, myRemap.. FirstNameFIELD 3, which corresponds to COLUMN 3, is re-mapped to the second table column, myRemap.. FirstName
  • FIELD 4, que corresponde a COLUMN 4, se asigna a la cuarta columna de tabla, myRemap.. GenderFIELD 4, which corresponds to COLUMN 4, is mapped to the fourth table column, myRemap.. Gender

Importación de datos con un archivo de formato para asignar columnas de tabla a campos de un archivo de datosImporting Data with a Format File to Map Table Columns to Data-File Field

Los ejemplos siguientes usan la base de datos, el archivo de datos y los archivos de formato creados anteriormente.The examples below use the database, datafile, and format files created above.

Uso de bcp y un archivo de formato no XMLUsing bcp and Non-XML Format File

En el símbolo del sistema, escriba el siguiente comando:At a command prompt, enter the following command:

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

Uso de bcp y un archivo de formato XMLUsing bcp and XML Format File

En el símbolo del sistema, escriba el siguiente comando:At a command prompt, enter the following command:

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

Uso de BULK INSERT y un archivo de formato no XMLUsing BULK INSERT and Non-XML Format File

Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):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;

Uso de BULK INSERT y un archivo de formato XMLUsing BULK INSERT and XML Format File

Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):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;

Uso de OPENROWSET(BULK...) y archivo de formato no XMLUsing OPENROWSET(BULK...) and Non-XML Format File

Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):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;

Uso de OPENROWSET(BULK...) y archivo de formato XMLUsing OPENROWSET(BULK...) and XML Format File

Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):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;

Vea tambiénSee Also

bcp Utility bcp Utility
Usar un archivo de formato para omitir una columna de tabla (SQL Server) Use a Format File to Skip a Table Column (SQL Server)
Usar un archivo de formato para omitir un campo de datos (SQL Server) Use a Format File to Skip a Data Field (SQL Server)