Utiliser un fichier de format pour importer des données en bloc (SQL Server)Use a Format File to Bulk Import Data (SQL Server)

CETTE RUBRIQUE S’APPLIQUE À :ouiSQL Server (à partir de la version 2008)ouiAzure SQL DatabaseouiAzure SQL Data Warehouse ouiParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Cette rubrique illustre l'utilisation d'un fichier de format dans les importations en bloc.This topic illustrates the use of a format file in bulk-import operations. Un fichier de format met en relation les champs du fichier de données avec les colonnes de la table.A format file maps the fields of the data file to the columns of the table. Veuillez consulter Créer un fichier de format (SQL Server) pour plus d’informations.Please review Create a Format File (SQL Server) for additional information.

ContourOutline
Avant de commencerBefore You Begin
Exemples de conditions de testExample Test Conditions
 ● Exemple de table ● Sample Table
 ● Exemple de fichier de données ● Sample Data File
Création des fichiers de formatCreating the Format Files
 ● Création d’un fichier de format non-XML ● Creating a Non-XML Format File
 ● Création d’un fichier de format XML ● Creating an XML Format File
Utilisation d'un fichier de format pour importer des données en blocUsing a Format File to Bulk Import Data
●  Utilisation d’un fichier de format bcp et non-XML ● Using bcp and Non-XML Format File
 ● Utilisation de bcp et d’un fichier de format XML ● Using bcp and XML Format File
 ● Utilisation de BULK INSERT et d’un fichier de format non-XML ● Using BULK INSERT and Non-XML Format File
 ● Utilisation de BULK INSERT et d’un fichier de format XML ● Using BULK INSERT and XML Format File
 ● Utilisation d’OPENROWSET(BULK...) et d’un fichier de format non-XML ● Using OPENROWSET(BULK...) and Non-XML Format File
 ● Utilisation d’OPENROWSET(BULK...) et d’un fichier de format XML ● Using OPENROWSET(BULK...) and XML Format File

Avant de commencerBefore You Begin

  • Pour qu’un fichier de format fonctionne avec un fichier de données de caractères Unicode, tous les champs d’entrée doivent être des chaînes de texte Unicode (autrement dit, des chaînes Unicode de taille fixe ou terminées par un caractère).For a format file to work with a Unicode character data file, all input fields must be Unicode text strings (that is, either fixed-size or character-terminated Unicode strings).
  • Pour exporter ou importer en bloc des données SQLXML , utilisez l’un des types de données ci-dessous dans votre fichier de format.To bulk export or import SQLXML data, use one of the following data types in your format file:
    • SQLCHAR ou SQLVARYCHAR (les données sont envoyées dans la page de codes du client ou dans la page de codes impliquée par le classement)SQLCHAR or SQLVARYCHAR (the data is sent in the client code page or in the code page implied by the collation)
    • SQLNCHAR ou SQLNVARCHAR (les données sont envoyées au format Unicode)SQLNCHAR or SQLNVARCHAR (the data is sent as Unicode)
    • SQLBINARY ou SQLVARYBIN (les données sont envoyées sans être converties).SQLBINARY or SQLVARYBIN (the data is sent without any conversion).
  • Azure SQL Database et Azure SQL Data Warehouse prennent uniquement en charge bcp.Azure SQL Database and Azure SQL Data Warehouse only support bcp. Pour plus d’informations, consultez :For additional information, see:

Exemples de conditions de testExample Test Conditions

Les fichiers de format pris en exemple dans cette rubrique sont fondés sur la table et le fichier de données définis ci-dessous.The examples of format files in this topic are based on the table and data file defined below.

Exemple de tableSample Table

Le script ci-dessous crée une base de données de test et une table nommée myFirstImport.The script below creates a test database and a table named myFirstImport. Exécutez l’instruction Transact-SQL suivante dans Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) :Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.MyFirstImport (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   BirthDate Date
   );

Fichier de données d’exempleSample Data File

À l’aide du Bloc-notes, créez un fichier vide D:\BCP\myFirstImport.bcp et insérez les données suivantes :Using Notepad, create an empty file D:\BCP\myFirstImport.bcp and insert the following data:

1,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
3,Stella,Rosenhain,1992-03-02

Vous pouvez aussi exécuter le script PowerShell suivant pour créer et remplir le fichier de données :Alternatively, you can execute the following PowerShell script to create and populate the data file:

cls
# revise directory as desired
$dir = 'D:\BCP\';

$bcpFile = Join-Path -Path $dir -ChildPath 'MyFirstImport.bcp';

# Confirm directory exists
IF ((Test-Path -Path $dir) -eq 0)
{
    Write-Host "The path $dir does not exist; please create or modify the directory.";
    RETURN;
};

# clear content, will error if file does not exist, can be ignored
Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;

# Add data
Add-Content -Path $bcpFile -Value '1,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,1992-03-02';

#Review content
Get-Content -Path $bcpFile;
Invoke-Item $bcpFile;

Création des fichiers de formatCreating the Format Files

SQL Server prend en charge deux types de fichier de format : format XML et format non XML.SQL Server support two types of format file: non-XML format and XML format. Le format non XML est le format d’origine pris en charge dans les versions précédentes de SQL Server.The non-XML format is the original format that is supported by earlier versions of SQL Server.

Création d’un fichier de format non-XMLCreating a Non-XML Format File

Veuillez consulter Fichiers de format non-XML (SQL Server) pour obtenir des informations détaillées.Please review Non-XML Format Files (SQL Server) for detailed information. La commande suivante utilise l’utilitaire bcp pour générer un fichier de format non xml myFirstImport.fmtbasé sur le schéma de myFirstImport.The following command will use the bcp utility to generate a non-xml format file, myFirstImport.fmt, based on the schema of myFirstImport. Pour utiliser une commande bcp pour créer un fichier de format, spécifiez l’argument format et utilisez nul à la place d’un chemin de fichier de données.To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. L’option format nécessite également l’option -f .The format option also requires the -f option. Pour cet exemple, le qualificateur c est utilisé pour spécifier 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.In addition, for this example, 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. À partir d'une invite de commandes, entrez la commande suivante :At a command prompt, enter the following command:

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

REM Review file
Notepad D:\BCP\myFirstImport.fmt

Votre fichier de format non XML D:\BCP\myFirstImport.fmt doit se présenter comme suit :Your non-XML format file, D:\BCP\myFirstImport.fmt should look as follows:

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       11      "\r\n"   4     BirthDate              ""

Important

Vérifiez que votre fichier de format non XML se termine par un retour charriot\saut de ligne.Ensure your non-XML format file ends with a carriage return\line feed. Sinon, vous recevez probablement le message d’erreur suivant :Otherwise you will likely receive the following error message:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

Création d’un fichier de format XMLCreating an XML Format File

Veuillez consulter Fichiers de format XML (SQL Server) pour obtenir des informations détaillées.Please review XML Format Files (SQL Server) for detailed information. La commande suivante utilise l’ utilitaire bcp pour créer un fichier de format xml myFirstImport.xmlbasé sur le schéma de myFirstImport.The following command will use the bcp utility to create an xml format file, myFirstImport.xml, based on the schema of myFirstImport. Pour utiliser une commande bcp pour créer un fichier de format, spécifiez l’argument format et utilisez nul à la place d’un chemin de fichier de données.To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. L’option de format nécessite toujours l’option -f . De plus, pour créer un fichier de format XML, vous devez spécifier l’option -x .The format option always requires the -f option, and to create an XML format file, you must also specify the -x option. Pour cet exemple, le qualificateur c est utilisé pour spécifier 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.In addition, for this example, 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. À partir d'une invite de commandes, entrez la commande suivante :At a command prompt, enter the following command:

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

REM Review file
Notepad D:\BCP\myFirstImport.xml

Votre fichier de format XML D:\BCP\myFirstImport.xml doit se présenter comme suit :Your XML format file, D:\BCP\myFirstImport.xml should look as follows:

\<?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="11"/>
 </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="BirthDate" xsi:type="SQLDATE"/>
 </ROW>
</BCPFORMAT>

Utilisation d'un fichier de format pour importer des données en blocUsing a Format File to Bulk Import Data

Les exemples ci-dessous utilisent la base de données, le fichier de données et les fichiers de format créés ci-dessus.The examples below use the database, datafile, and format files created above.

Utilisation de bcp et d’un fichier de format non-XMLUsing bcp and Non-XML Format File

À partir d'une invite de commandes, entrez la commande suivante :At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport"

Utilisation de bcp et d’un fichier de format XMLUsing bcp and XML Format File

À partir d'une invite de commandes, entrez la commande suivante :At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.xml -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport;"

Utilisation de BULK INSERT et d’un fichier de format non-XMLUsing BULK INSERT and Non-XML Format File

Exécutez l’instruction Transact-SQL suivante dans Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) :Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport   
   FROM 'D:\BCP\myFirstImport.bcp'   
   WITH (FORMATFILE = 'D:\BCP\myFirstImport.fmt');  
GO  

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

Utilisation de BULK INSERT et d’un fichier de format XMLUsing BULK INSERT and XML Format File

Exécutez l’instruction Transact-SQL suivante dans Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) :Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport   
   FROM 'D:\BCP\myFirstImport.bcp'   
   WITH (FORMATFILE = 'D:\BCP\myFirstImport.xml');  
GO  

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

Utilisation d’ OPENROWSET(BULK...) et d’un fichier de format non-XMLUsing OPENROWSET(BULK...) and Non-XML Format File

Exécutez l’instruction Transact-SQL suivante dans Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) :Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myFirstImport.bcp',
        FORMATFILE = 'D:\BCP\myFirstImport.fmt'
        ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

Utilisation d’OPENROWSET(BULK...) et d’un fichier de format XMLUsing OPENROWSET(BULK...) and XML Format File

Exécutez l’instruction Transact-SQL suivante dans Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) :Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport 
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myFirstImport.bcp',
        FORMATFILE = 'D:\BCP\myFirstImport.xml'  
       ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

Plus d’exemples !More examples!

Créer un fichier de format (SQL Server)Create a Format File (SQL Server)
Utiliser un fichier de format pour ignorer une colonne de table (SQL Server)Use a Format File to Skip a Table Column (SQL Server)
Utiliser un fichier de format pour ignorer un champ de données (SQL Server)Use a Format File to Skip a Data Field (SQL Server)
Utiliser un fichier de format pour mapper les colonnes d’une table aux champs d’un fichier de données (SQL Server)Use a Format File to Map Table Columns to Data-File Fields (SQL Server)

Voir aussiSee also

bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
Fichiers de format non-XML (SQL Server) Non-XML Format Files (SQL Server)
Fichiers de format XML (SQL Server)XML Format Files (SQL Server)
Fichiers de format pour l'importation ou l'exportation de données (SQL Server)Format Files for Importing or Exporting Data (SQL Server)