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

Article
04/04/2023

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. Cette rubrique présente les fichiers de format XML et non-XML ayant été modifiés de sorte à accepter un fichier de données dont les champs sont organisés dans un ordre différent de celui des colonnes de la table correspondante. Le fichier de format modifié permet de mapper les champs de données sur les colonnes correspondantes de la table. Veuillez consulter Créer un fichier de format (SQL Server) pour plus d’informations.

Notes

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

Notes

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 fichiers de format modifiés pris en exemple dans cette rubrique sont fondés sur la table et le fichier de données définis ci-dessous.

Exemple de table

Le script ci-dessous crée une base de données de test et une table nommée myRemap. Exécutez l’instruction Transact-SQL suivante dans 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)
   );

Exemple de fichier de données

Les données ci-dessous présentent FirstName et LastName dans l’ordre inverse, comme présenté dans la table myRemap. À l’aide du Bloc-notes, créez un fichier vide D:\BCP\myRemap.bcp 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 effectuer l’importation en bloc de données de myRemap.bcp dans la table myRemap , le fichier de format doit effectuer les tâches 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

Veuillez consulter 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

Modifier le fichier de format non XML

Consultez Structure des fichiers de format non XML pour la terminologie. Ouvrez D:\BCP\myRemap.fmt dans Bloc-notes et effectuez les modifications suivantes :

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

Comparez les modifications :

Avant

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éer 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

Modifier le fichier de format XML

Consultez la 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 :

L’ordre dans lequel les éléments <FIELD> sont déclarés dans le format de fichier est l’ordre dans lequel ces champs s’affichent dans le fichier de données, par conséquent, inversez l’ordre pour les éléments <FIELD> avec les attributs d’ID 2 et 3.
Vérifiez que les valeurs de l’attribut <FIELD> ID sont séquentielles.
L’ordre des éléments <COLUMN> dans l’élément <ROW> définit l’ordre dans lequel ils sont envoyés à la cible par l’opération en bloc. Le fichier de format XML assigne à 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 éléments <COLUMN> est indépendant de l’ordre des éléments <FIELD> 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 ls seuls attributs qui nécessitent une révision. Inversez l’ordre pour les attributs 2 et 3 de <COLUMN> SOURCE.

Comparez les modifications :

Avant

<?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 à COLUMN 2, est mappé de nouveau à la troisième colonne de la table myRemap.. LastName
FIELD 3, qui correspond à COLUMN 3, est mappé de nouveau à la seconde colonne de la table myRemap.. FirstName
FIELD 4, qui correspond à COLUMN 4, est mappé à la quatrième colonne de la table myRemap.. Gender

Importer des données avec un fichier de format pour mapper des colonnes de table à un champ de fichier de données

Les exemples ci-dessous utilisent la base de données, le fichier de données et les fichiers de format créés ci-dessus.

Utiliser un fichier de format bcp et non XML

À partir d'une invite de commandes, entrez la commande suivante :

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

Utiliser le fichier de format bcp et XML

À partir d'une 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 le fichier au format non XML

Exécutez l’instruction Transact-SQL suivante dans 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;

Utiliser BULK INSERT et le fichier de format XML

Exécutez l’instruction Transact-SQL suivante dans 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;

Utiliser OPENROWSET(BULK...) et un fichier de format non XML

Exécutez l’instruction Transact-SQL suivante dans 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;

Utiliser OPENROWSET(BULK...) et le fichier de format XML

Exécutez l’instruction Transact-SQL suivante dans 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;

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

Exemples de conditions de test

Exemple de table

Exemple de fichier de données

Créer les fichiers de format

Créer un fichier de format non XML

Modifier le fichier de format non XML

Créer un fichier de format XML

Modifier le fichier de format XML

Importer des données avec un fichier de format pour mapper des colonnes de table à un champ de fichier de données

Utiliser un fichier de format bcp et non XML

Utiliser le fichier de format bcp et XML

Utiliser BULK INSERT et le fichier au format non XML

Utiliser BULK INSERT et le fichier de format XML

Utiliser OPENROWSET(BULK...) et un fichier de format non XML

Utiliser OPENROWSET(BULK...) et le fichier de format XML

Étapes suivantes

Commentaires

Commentaires

Ressources supplémentaires