Partager via

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

  • Article

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

Le nombre de champs contenus dans un fichier de données peut être supérieur au nombre de colonnes dans la table. Cette rubrique explique comment modifier les fichiers de format non-XML et XML en mappant les colonnes de la table avec les champs de données correspondants et en ignorant les champs supplémentaires afin de prendre en charge un fichier de données contenant davantage de champs. Veuillez consulter Créer un fichier de format (SQL Server) pour plus d’informations.

Remarque

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).

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 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 myTestSkipField. Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

CREATE DATABASE TestDatabase;
GO

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

Exemple de fichier de données

Créez un fichier vide D:\BCP\myTestSkipField.bcp et insérez les données suivantes :

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

Créer les fichiers de format

Pour effectuer l’importation en bloc de données de myTestSkipField.bcp dans la table myTestSkipField , le fichier de format doit effectuer les tâches suivantes :

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

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 myTestSkipField.fmtbasé sur le schéma de myTestSkipField. 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.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.fmt -t, -T

Modification du fichier de format non-XML

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

  1. Copiez l’intégralité de la ligne format-file pour FirstName et collez-la directement après FirstName sur la ligne suivante.
  2. Incrémentez de 1 la valeur d’ordre des champs du fichier hôte pour la nouvelle ligne et toutes les lignes suivantes.
  3. Augmentez la valeur du nombre de colonnes pour refléter le nombre réel de champs figurant dans le fichier de données.
  4. Modifiez l’ordre des colonnes du serveur en les faisant passer de 2 à 0 pour la deuxième ligne format-file.

Comparez les modifications apportées :

Avant le

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

Après

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

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

  • 4 champs de données
  • Le premier champ de données figurant dans myTestSkipField.bcp est mappé à la première colonne ; myTestSkipField.. PersonID
  • Le deuxième champ de données dans myTestSkipField.bcp n’est mappé à aucune colonne.
  • Le troisième champ de données figurant dans myTestSkipField.bcp est mappé à la deuxième colonne ; myTestSkipField.. FirstName
  • Le quatrième champ de données figurant dans myTestSkipField.bcp est mappé à la troisième colonne ; myTestSkipField.. LastName

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 myTestSkipField.xmlbasé sur le schéma de myTestSkipField. 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.myTestSkipField format nul -c -x -f D:\BCP\myTestSkipField.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\myTestSkipField.xml dans Bloc-notes et effectuez les modifications suivantes :

  1. Copiez la totalité du deuxième champ et collez-la directement après le deuxième champ sur la ligne suivante.
  2. Incrémentez la valeur « FIELD ID » de 1 pour le nouveau champ et pour chaque champ suivant.
  3. Incrémentez la valeur de « COLUMN SOURCE » de 1 pour FirstName, et LastName afin de refléter le mappage modifié.

Comparez les modifications apportées :

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="\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>

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="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>

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

  • 4 champs de données
  • FIELD 1 qui correspond à COLUMN 1 est mappé à la première colonne de la table. myTestSkipField.. PersonID
  • FIELD 2 ne correspond à aucune COLUMN et n’est, par conséquent, mappé à aucune colonne de la table.
  • FIELD 3 qui correspond à COLUMN 3 est mappé à la seconde colonne de la table. myTestSkipField.. FirstName
  • FIELD 4 qui correspond à COLUMN 4 est mappé à la troisième colonne de la table. myTestSkipField.. LastName

Importation de données avec un fichier de format pour ignorer un champ 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.

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

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

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

Utilisation de bcp et d’un fichier de format XML

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

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

Utiliser BULK INSERT et un fichier de format non-XML

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

Utiliser BULK INSERT et un fichier de format XML

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

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

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

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

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

Étapes suivantes