Partage via


Utiliser un fichier de format pour mapper les colonnes d’une table aux champs d’un fichier de données (SQL Server)

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Il se peut que les champs d'un fichier de données ne soient pas dans le même ordre que les colonnes correspondantes présentes dans la table. Cet article présente les fichiers de format non XML et XML modifiés pour prendre en charge un fichier de données dont les champs sont organisés dans un ordre différent des colonnes de la table. Le fichier de format modifié permet de mapper les champs de données sur les colonnes correspondantes de la table. Pour plus d’informations, consultez Créer un fichier de format avec bcp (SQL Server).

Un fichier de format non XML ou XML peut être utilisé pour importer en bloc un fichier de données dans la table, à l’aide d’une commande d’utilitaire bcp, d’une instruction BULK INSERT ou d’INSERT ... SELECT * FROM OPENROWSET, instruction. Pour plus d’informations, consultez Utiliser un fichier de format pour importer en bloc des données (SQL Server).

Remarque

Cette syntaxe, y compris l’insertion en bloc, n’est pas prise en charge dans Azure Synapse Analytics. Dans Azure Synapse Analytics et d’autres intégrations de plateforme de base de données cloud, effectuez le déplacement des données via l’instruction COPY dans Azure Data Factory ou à l’aide d’instructions T-SQL telles que COPY INTO et PolyBase.

Exemples de conditions de test

Les exemples de fichiers de format modifiés de cet article sont basés sur la table et le fichier de données défini plus loin dans cet article.

Exemple de table

Le script Transact-SQL suivant crée une base de données de test et une table nommée myRemap.

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;

CREATE TABLE myRemap
(
    PersonID SMALLINT,
    FirstName VARCHAR (25),
    LastName VARCHAR (30),
    Gender CHAR (1)
);

Exemple de fichier de données

Les données suivantes présentent FirstName et LastName dans l’ordre inverse, comme indiqué dans le tableau myRemap. À l’aide d’un éditeur de texte tel que le Bloc-notes, créez un fichier D:\BCP\myRemap.bcp vide et insérez les données suivantes :

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

Créer les fichiers de format

Pour importer en bloc des données à partir de myRemap.bcp la myRemap table, le fichier de format doit effectuer les actions suivantes :

  • mapper le premier champ des données à la première colonne, PersonID;
  • mapper le second champ des données à la troisième colonne, LastName;
  • mapper le troisième champ des données à la deuxième colonne, FirstName;
  • mapper le quatrième champ des données à la quatrième colonne, Gender.

La méthode la plus simple pour créer le fichier de format consiste à utiliser bcp utility. Tout d’abord, créez un fichier de format de base à partir de la table existante. Ensuite, modifiez le fichier de format de base afin qu’il reflète le fichier de données réel.

Créer un fichier de format non XML

Consultez Utiliser des fichiers de format non XML (SQL Server) pour obtenir des informations détaillées. La commande suivante utilise l’utilitaire bcp pour générer un fichier de format non XML, myRemap.fmtbasé sur le schéma de myRemap. En outre, le qualificateur c est utilisé pour sépcifier les données de caractère, t, est utilisé pour spécifier une virgule comme délimiteur de champ, et T est utilisé pour spécifier une connexion approuvée à l’aide de la sécurité intégrée. À partir d'une invite de commandes, entrez la commande suivante :

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

Modification du fichier de format non-XML

Pour plus d’informations sur la terminologie, consultez Structure des fichiers de format non XML. Ouvrez D:\BCP\myRemap.fmt dans Bloc-notes et effectuez les modifications suivantes :

  1. Réorganisez l’ordre des lignes de fichier de format afin que les lignes soient dans le même ordre que les données dans myRemap.bcp.
  2. Assurez-vous que les valeurs d’ordre du champ de fichier hôte soient séquentielles.
  3. Vérifiez qu’il existe un retour chariot après la dernière ligne de fichier de format.

Comparez les modifications :

Avant le

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

Après

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

Le fichier de format modifié reflète à présent les éléments suivants :

  • Le premier champ de données figurant dans myRemap.bcp est mappé à la première colonne ; myRemap.. PersonID
  • Le second champ de données figurant dans myRemap.bcp est mappé à la troisième colonne ; myRemap.. LastName
  • Le troisième champ de données figurant dans myRemap.bcp est mappé à la deuxième colonne ; myRemap.. FirstName
  • Le quatrième champ de données figurant dans myRemap.bcp est mappé à la quatrième colonne ; myRemap.. Gender

Création d’un fichier de format XML

Veuillez consulter Fichiers de format XML (SQL Server) pour obtenir des informations détaillées. La commande suivante utilise l’utilitaire bcp pour créer un fichier de format xml, myRemap.xmlbasé sur le schéma de myRemap. En outre, le qualificateur c est utilisé pour sépcifier les données de caractère, t, est utilisé pour spécifier une virgule comme délimiteur de champ, et T est utilisé pour spécifier une connexion approuvée à l’aide de la sécurité intégrée. Le qualificateur x doit être utilisé pour générer un fichier de format XML. À partir d'une invite de commandes, entrez la commande suivante :

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

Modification du fichier de format XML

Consultez Syntaxe de schéma pour les fichiers de format XML pour la terminologie. Ouvrez D:\BCP\myRemap.xml dans Bloc-notes et effectuez les modifications suivantes :

  1. L’ordre dans lequel les <FIELD> éléments sont déclarés dans le fichier de format est l’ordre dans lequel ces champs apparaissent dans le fichier de données. Par conséquent, inversez l’ordre des <FIELD> éléments avec des attributs d’ID 2 et 3.

  2. Vérifiez que les valeurs de l’attribut <FIELD>ID sont séquentielles.

  3. L’ordre des éléments de <COLUMN> l’élément <ROW> définit l’ordre dans lequel l’opération en bloc les envoie à la cible. Le fichier de format XML attribue à chaque élément <COLUMN> un nom local qui n’a aucune relation avec la colonne dans la table cible d’une opération d’importation en bloc. L’ordre des <COLUMN> éléments est indépendant de l’ordre des <FIELD> éléments dans une <définition RECORD> . Chaque élément <COLUMN>correspond à un élément <FIELD> (dont l’ID est spécifié dans l’attribut SOURCE de l’élément <COLUMN>). Par conséquent, les valeurs de <COLUMN> SOURCE sont les seuls attributs qui nécessitent une révision. Inversez l’ordre pour les attributs 2 et 3 de <COLUMN> SOURCE.

Comparez les modifications :

Avant le

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

Après

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

Le fichier de format modifié reflète à présent les éléments suivants :

  • FIELD 1, qui correspond à COLUMN 1, est mappé à la première colonne de la table myRemap.. PersonID
  • FIELD 2, qui correspond à LA COLONNE 2, est remappé dans la troisième colonne de table, myRemap.. LastName
  • FIELD 3, qui correspond à LA COLONNE 3, est remappé à la deuxième colonne de table, myRemap.. FirstName
  • FIELD 4, qui correspond à COLUMN 4, est mappé à la quatrième colonne de la table myRemap.. Gender

Importation de données avec un fichier de format pour mapper les colonnes d’une table aux champs d’un fichier de données

Les exemples suivants utilisent la base de données, le fichier de données et les fichiers de format créés précédemment.

Utilisation de bcp et d’un fichier de format non-XML

À l’invite de commandes, entrez la commande suivante.

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

Utilisation de bcp et d’un fichier de format XML

À l’invite de commandes, entrez la commande suivante.

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

Utiliser BULK INSERT et un fichier de format non-XML

Exécutez le script Transact-SQL suivant.

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;

Utiliser BULK INSERT et un fichier de format XML

Exécutez le script Transact-SQL suivant.

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;

Utiliser le fichier de format OPENROWSET et non XML

Exécutez le script Transact-SQL suivant.

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;

Utiliser le fichier de format OPENROWSET et XML

Exécutez le script Transact-SQL suivant.

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;