Créer un fichier de format avec bcp (SQL Server)

Article
12/06/2023

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

Cet article décrit l’utilisation de l’ utilitaire bcp pour créer un fichier de format pour une table donnée. Le fichier de format est basé sur l’option de type de données spécifiée (-n, -c, -w ou -N) et les délimiteurs de la table ou de la vue.

Lors d’une importation en bloc dans une table SQL Server ou d’une exportation en bloc des données à partir d’une table, vous pouvez utiliser un fichier de format comme système flexible pour écrire des fichiers de données. Ces fichiers nécessitent peu de modifications, voire aucune, pour se conformer aux autres formats de données, ou pour lire des fichiers de données provenant d'autres logiciels.

Limites

La version de l’utilitaire bcp (bcp.exe) servant à lire un fichier de format doit être identique ou ultérieure à la version utilisée pour le créer. Par exemple, bcp sous SQL Server 2016 (13.x) peut lire un fichier de format version 12.0 généré par bcp sous SQL Server 2014 (12.x), mais bcp sous SQL Server 2014 (12.x) ne peut pas lire un fichier de format version 13.0 généré par bcp sous SQL Server 2016 (13.x).

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.

Créer der fichiers de format

SQL Server prend en charge deux types de fichier de format : format XML et format non XML. Le format non XML est le format d’origine pris en charge dans les versions précédentes de SQL Server.

En règle générale, les fichiers de format XML et non-XML sont interchangeables. Toutefois, nous recommandons d'utiliser la syntaxe XML pour les fichiers de format, car elle offre plusieurs avantages par rapport aux fichiers de format non XML.

Fichier de format XML
Fichier de format non XML

Les exemples de code Transact-SQL de cet article sont fondés sur l’échantillon de base de données AdventureWorks2022 ou AdventureWorksDW2022 fourni, que vous pouvez télécharger à partir de la page d’accueil Échantillons et projets communautaires Microsoft SQL Server. Adventure Works Cycles est une société de fabrication fictive utilisée pour illustrer des scénarios et des concepts de base de données.

Création d’un fichier de format XML

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. L’option format nécessite toujours l’option -f. La création d’un fichier de format XML nécessite également l’option -x, comme suit : bcp <table_or_view> format nul -f <format_file_name> -x.

Pour bien distinguer un fichier au format XML, nous vous recommandons d’utiliser l’extension de nom de fichier .xml, par exemple MyTable.xml.

Pour plus d’informations sur la structure et les champs des fichiers de format XML, consultez Fichiers de format XML (SQL Server).

Exemples

Cette section contient les exemples suivants, qui illustrent l’utilisation des commandes bcp pour créer un fichier de format XML. La table HumanResources.Department contient quatre colonnes : DepartmentID, Name, GroupNameet ModifiedDate.

R : Création d’un fichier de format XML pour des données de type caractère

L'exemple suivant crée un fichier de format XML appelé Department.xmlet destiné à la table HumanResources.Department . Le fichier de format utilise les formats de données de type caractère ainsi qu'un indicateur de fin de champ qui n'est pas défini par défaut (,). Le contenu du fichier ainsi généré vous est présenté après la commande.

La commande bcp contient les qualificateurs suivants.

Qualificateurs	Description
`format nul -x -f <format_file>`	Fichier de format XML.
`-c`	Données de type caractère.
`-t,`	Virgule (`,`) servant d'indicateur de fin de champ. Remarque : si le fichier de données utilise la marque de fin de champ par défaut (`\t`), le commutateur `-t` n’est pas nécessaire.
`-T`	Spécifie que l'utilitaire bcp se connecte à SQL Server avec une connexion approuvée qui utilise la sécurité intégrée. Si `-T` n'est pas spécifié, vous devez indiquer `-U` et `-P` pour pouvoir vous connecter.

Dans la fenêtre d'invite de commandes Windows, tapez la commande bcp suivante :

bcp AdventureWorks2022.HumanResources.Department format nul -c -x -f Department-c.xml -t, -T

Le fichier de format généré Department-c.xmlcontient les éléments XML suivants :

<?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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>

Pour obtenir des informations sur la syntaxe de ce fichier de format, consultez Fichiers de format XML (SQL Server). Pour plus d’informations sur les données de type caractère, consultez Utiliser le format caractère pour importer ou exporter des données (SQL Server).

B. Création d’un fichier de format XML pour des données natives

L'exemple suivant crée un fichier de format XML appelé Department-n.xmlet destiné à la table HumanResources.Department . Le fichier de format utilise des types de données natives. Le contenu du fichier ainsi généré vous est présenté après la commande.

La commande bcp contient les qualificateurs suivants.

Qualificateurs	Description
`format nul -x -f <format_file>`	Fichier de format XML.
`-n`	Spécifie les types de données natifs.
`-T`	Spécifie que l'utilitaire bcp se connecte à SQL Server avec une connexion approuvée qui utilise la sécurité intégrée. Si `-T` n'est pas spécifié, vous devez indiquer `-U` et `-P` pour pouvoir vous connecter.

Dans la fenêtre d'invite de commandes Windows, tapez la commande bcp suivante :

bcp AdventureWorks2022.HumanResources.Department format nul -x -f Department-n.xml -n -T

Le fichier de format généré Department-n.xmlcontient les éléments XML suivants :

<?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="NativeFixed" LENGTH="2"/>
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="NativeFixed" LENGTH="8"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>

Créer un fichier de format non XML

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. L’option format requiert également l’option -f, comme suit : bcp <table_or_view> format nul -f <format_file_name>.

Pour bien distinguer un fichier au format non XML, nous vous recommandons d’utiliser l’extension de nom de fichier .fmt, par exemple MyTable.fmt.

Pour plus d’informations sur la structure et les champs des fichiers de format non XML, consultez Utilisez les fichiers de format non XML (SQL Server).

Exemples

Cette section contient les exemples suivants qui illustrent l’utilisation des commandes bcp pour créer un fichier de format non-XML. La table HumanResources.Department contient quatre colonnes : DepartmentID, Name, GroupNameet ModifiedDate.

R : Création d’un fichier de format non-XML pour des données natives

La commande bcp contient les qualificateurs suivants.

Qualificateurs	Description
`format nul -f <format_file>`	Format de fichier non-XML.
`-n`	Spécifie les types de données natifs.
`-T`	Spécifie que l'utilitaire bcp se connecte à SQL Server avec une connexion approuvée qui utilise la sécurité intégrée. Si `-T` n'est pas spécifié, vous devez indiquer `-U` et `-P` pour pouvoir vous connecter.

Dans la fenêtre d'invite de commandes Windows, tapez la commande bcp suivante :

bcp AdventureWorks2022.HumanResources.Department format nul -T -n -f Department-n.fmt

Le fichier de format généré, Department-n.fmt, contient les informations suivantes :

12.0
4
1  SQLSMALLINT   0       2       ""   1     DepartmentID         ""
2  SQLNCHAR      2       100     ""   2     Name                 SQL_Latin1_General_CP1_CI_AS
3  SQLNCHAR      2       100     ""   3     GroupName            SQL_Latin1_General_CP1_CI_AS
4  SQLDATETIME   0       8       ""   4     ModifiedDate         ""

Pour plus d’informations, consultez Utilisez les fichiers de format non XML (SQL Server).

B. Création d’un fichier de format non-XML pour des données de type caractère

L'exemple suivant crée un fichier de format XML appelé Department.fmtet destiné à la table HumanResources.Department . Le fichier de format utilise les formats de données de type caractère ainsi qu'un indicateur de fin de champ qui n'est pas défini par défaut (,). Le contenu du fichier ainsi généré vous est présenté après la commande.

La commande bcp contient les qualificateurs suivants.

Qualificateurs	Description
`format nul -f <format_file>`	Format de fichier non-XML.
`-c`	Données de type caractère.
`-T`	Spécifie que l'utilitaire bcp se connecte à SQL Server avec une connexion approuvée qui utilise la sécurité intégrée. Si `-T` n'est pas spécifié, vous devez indiquer `-U` et `-P` pour pouvoir vous connecter.

Dans la fenêtre d'invite de commandes Windows, tapez la commande bcp suivante :

bcp AdventureWorks2022.HumanResources.Department format nul -c -f Department-c.fmt -T

Le fichier de format généré, Department-c.fmt, contient les informations suivantes :

12.0
4
1  SQLCHAR       0       7       "\t"     1     DepartmentID            ""
2  SQLCHAR       0       100     "\t"     2     Name                    SQL_Latin1_General_CP1_CI_AS
3  SQLCHAR       0       100     "\t"     3     GroupName               SQL_Latin1_General_CP1_CI_AS
4  SQLCHAR       0       24      "\r\n"   4     ModifiedDate            ""

Pour plus d’informations, consultez Utilisez les fichiers de format non XML (SQL Server).

C. Création d’un fichier de format non-XML pour des données Unicode natives

Pour créer un fichier de format non-XML pour des données Unicode natives destinées à la table HumanResources.Department, utilisez la commande suivante :

bcp AdventureWorks2022.HumanResources.Department format nul -T -N -f Department-n.fmt

Pour plus d’informations sur l’utilisation des données natives Unicode, consultez Utiliser le format natif Unicode pour importer ou exporter des données (SQL Server).

D. Création d’un fichier de format non XML pour des données Unicode de type caractère

Pour créer un fichier de format non-XML pour des données Unicode de type caractère, s'appuyant sur des indicateurs de fin définis par défaut et destinées à la table HumanResources.Department, utilisez la commande suivante :

bcp AdventureWorks2022.HumanResources.Department format nul -T -w -f Department-w.fmt

Pour plus d’informations sur l’utilisation des données de caractères Unicode, consultez Utiliser le format caractère unicode pour importer ou exporter des données (SQL Server).

F. Utilisation d’un fichier de format avec l’option de page de code

Si vous créez un fichier de format à l’aide de bcp (c’est-à-dire en utilisant bcp format), les informations sur la page de code/classement sont écrites dans le fichier de format.

L’exemple de fichier de format suivant, pour une table avec cinq colonnes, inclut le classement.

13.0
5
1  SQLCHAR         0       0       "\t"             1     c_0          Cyrillic_General_CS_AS
2  SQLCHAR         0       0       "\t"             2     c_1          Cyrillic_General_CS_AS
3  SQLCHAR         0       3000    "\t"             3     c_2          Cyrillic_General_CS_AS
4  SQLCHAR         0       5       "\t"             4     c_3          ""
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4          ""

Si vous essayez d’importer des données dans SQL Server à l’aide de « bcp in -c -C65001 -f format_file… » ou de « BULK INSERT/OPENROWSET… FORMATFILE='format_file' CODEPAGE=65001… », les informations sur la page de code/classement ont la priorité sur l’option 65001.

Par conséquent, si vous générez un fichier de format, vous devez supprimer manuellement les informations de classement du fichier de format généré avant de commencer l’importation des données dans SQL Server.

Voici un exemple de fichier de format sans les informations de classement.

13.0
5
1  SQLCHAR         0       0       "\t"             1     c_0              ""
2  SQLCHAR         0       0       "\t"             2     c_1              ""
3  SQLCHAR         0       3000    "\t"             3     c_2              ""
4  SQLCHAR         0       5       "\t"             4     c_3              ""
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4              ""

Mappage des champs de données aux colonnes de table

Lorsqu’il est créé par bcp, un fichier de format décrit toutes les colonnes de table dans l’ordre. Vous pouvez modifier ce fichier pour réorganiser ou omettre des lignes de la table. Vous pouvez ainsi personnaliser un fichier de format en fonction d'un fichier de données dont les champs ne sont pas mappés directement aux colonnes de table. Pour plus d’informations, consultez les articles suivants :

Partager via

Créer un fichier de format avec bcp (SQL Server)

Limites

Créer der fichiers de format

Création d’un fichier de format XML

Exemples

R : Création d’un fichier de format XML pour des données de type caractère

B. Création d’un fichier de format XML pour des données natives

Créer un fichier de format non XML

Exemples

R : Création d’un fichier de format non-XML pour des données natives

B. Création d’un fichier de format non-XML pour des données de type caractère

C. Création d’un fichier de format non-XML pour des données Unicode natives

D. Création d’un fichier de format non XML pour des données Unicode de type caractère

F. Utilisation d’un fichier de format avec l’option de page de code

Mappage des champs de données aux colonnes de table

Commentaires

Ressources supplémentaires

Partager via

Créer un fichier de format avec bcp (SQL Server)

Limites

Créer der fichiers de format

Création d’un fichier de format XML

Exemples

R : Création d’un fichier de format XML pour des données de type caractère

B. Création d’un fichier de format XML pour des données natives

Mappage des champs de données aux colonnes de table

Contenu connexe

Commentaires

Ressources supplémentaires