Partager via

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

  • Article

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.

[!REMARQUE]

Vous pouvez utiliser un fichier de format XML ou non-XML pour procéder à l'importation en bloc d'un fichier de données dans la table à l'aide d'une commande bcp, 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).

Exemples de table et de fichier de données

Les fichiers de format modifiés pris en exemple dans cette rubrique sont fondés sur la table et le fichier de données suivants.

Table d'exemple

Dans les exemples de cette rubrique, une table nommée myTestOrder doit être créée dans l'exemple de base de données AdventureWorks2012 sous le schéma dbo. Pour créer cette table, dans l'Éditeur de requêtes SQL Server Management Studio, exécutez le code suivant :

USE AdventureWorks2012;
GO
CREATE TABLE myTestOrder 
   (
   Col1 smallint,
   Col2 nvarchar(50) ,
   Col3 nvarchar(50) , 
   Col4 nvarchar(50) 
   );
GO

Fichier de données

Le fichier de données, myTestOrder-c.txt, contient les enregistrements suivants :

DataField3,DataField2,1,DataField4
DataField3,DataField2,1,DataField4
DataField3,DataField2,1,DataField4

Pour importer des données en bloc depuis myTestSkipCol2-c.dat dans la table myTestSkipCol, le fichier de format doit mapper le premier champ de données à Col3, le deuxième champ à Col2, le troisième champ à Col1, et le quatrième champ à Col4.

Utilisation d'un fichier de format non-XML

Pour modifier l'ordre d'un mappage de colonne, vous devez modifier la valeur d'ordre de la colonne afin d'indiquer la position du champ de données correspondant.

L'exemple suivant présente un fichier de format non-XML, myTestOrder.fmt, qui mappe les champs de myTestOrder-c.txt aux colonnes de la table myTestOrder. Pour plus d'informations sur la table et le fichier de données et la manière de les créer, consultez la section « Exemples de fichier de données et de table », plus haut dans cette rubrique. Ce fichier de format utilise le format de données caractères.

Le fichier de format contient les informations suivantes :

9.0
4
1       SQLCHAR       0       100     ","     3     Col3               SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       100     ","     2     Col2               SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       7       ","     1     Col1               ""
4       SQLCHAR       0       100     "\r\n"  4     Col4               SQL_Latin1_General_CP1_CI_AS

[!REMARQUE]

Pour plus d'informations sur la structure des fichiers de format non-XML, consultez Fichiers de format non-XML (SQL Server).

Exemple

L'exemple suivant utilise une instruction BULK INSERT pour procéder à l'importation en bloc de données issues du fichier de données myTestOrder-c.txt vers l'exemple de table myTestOrder à l'aide du fichier de format non-XML myTestOrder.fmt.

Dans l'Éditeur de requête SQL Server Management Studio, exécutez :

USE AdventureWorks2012;
GO
BULK INSERT myTestOrder
FROM 'C:\myTestOrder-c.txt' 
WITH (formatfile='C:\myTestOrder.fmt');
GO

Utilisation d'un fichier de format XML

L'exemple suivant présente un fichier de format non-XML, myTestOrder.xml, qui mappe les champs de myTestOrder-c.txt sur les colonnes de la table myTestOrder. Pour plus d'informations sur la création du fichier de données et de la table, consultez « Exemples de table et de fichier de données » plus haut dans cette rubrique.

Le fichier de format myTestOrder.xml contient les informations suivantes :

<?xml version="1.0"?>
<BCPFORMAT xmlns="https://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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="3" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="1" NAME="Col3" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

[!REMARQUE]

Pour plus d'informations sur la syntaxe du schéma XML et pour d'autres exemples de fichiers de format XML, consultez Fichiers de format XML (SQL Server).

Exemple

L'exemple suivant utilise le fournisseur d'ensemble de lignes en bloc OPENROWSET pour procéder à l'importation du fichier de données myTestOrder-c.txt vers l'exemple de table myTestOrder à l'aide du fichier de format XML myTestOrder.xml. L'instruction INSERT… SELECT spécifie la liste de colonnes de la liste de sélection.

Dans l'Éditeur de requête SQL Server Management Studio, exécutez le code suivant :

USE AdventureWorks2012;
GO
INSERT INTO myTestOrder 
  SELECT Col1, Col2, Col3, Col4
      FROM  OPENROWSET(BULK  'C:\myTestOrder-c.txt',
      FORMATFILE='C:\myTestOrder.Xml'  
       ) AS t1;
GO

Voir aussi

Concepts

Utiliser un fichier de format pour ignorer une colonne de table (SQL Server)

Utiliser un fichier de format pour ignorer un champ de données (SQL Server)