Partager via

Importer des données à partir d’Excel ou exporter des données vers Excel avec SQL Server Integration Services (SSIS)

S’applique à :SQL Server SSIS Integration Runtime dans Azure Data Factory

Cet article décrit les informations de connexion à fournir et les paramètres à configurer pour importer des données à partir d’Excel ou exporter des données vers Excel avec SQL Server Integration Services (SSIS).

Les sections suivantes donnent les informations nécessaires pour utiliser efficacement Excel avec SSIS, et comprendre et résoudre des problèmes courants :

Outils disponibles

Vous pouvez utiliser les outils suivants pour importer des données à partir d’Excel ou exporter des données vers Excel avec SSIS :

Se procurer les fichiers nécessaires pour se connecter à Excel

Avant de pouvoir importer des données à partir d’Excel ou exporter des données vers Excel avec SSIS, vous devrez peut-être télécharger les composants de connectivité pour Excel s’ils ne sont pas déjà installés. Les composants de connectivité pour Excel ne sont pas installés par défaut.

Utilisez le tableau dans Impossible d’utiliser les interfaces Access ODBC, OLEDB ou DAO en dehors de Démarrer en un clic d’Office pour déterminer si d’autres composants sont nécessaires pour votre environnement.

Remarque : Les pilotes système Office sont uniquement pris en charge dans certains scénarios. Pour plus d’informations, consultez Considérations sur l’automatisation côté serveur de Microsoft Office.

Spécifier Excel comme source de données

La première étape consiste à indiquer que vous voulez vous connecter à Excel.

Dans SSIS

Dans SSIS, créez un gestionnaire de connexions Excel pour vous connecter au fichier source ou de destination Excel. Il existe plusieurs façons de créer le gestionnaire de connexions :

  • Dans la zone Gestionnaires de connexions, cliquez avec le bouton droit sur Nouvelle connexion. Dans la boîte de dialogue Ajout d’un gestionnaire de connexions SSIS, sélectionnez EXCEL, puis Ajouter.

  • Dans le menu SSIS, sélectionnez Nouvelle connexion. Dans la boîte de dialogue Ajout d’un gestionnaire de connexions SSIS, sélectionnez EXCEL, puis Ajouter.

  • Créez le gestionnaire de connexions en même temps que vous configurez la Source Excel ou la Destination Excel dans la page Gestionnaire de connexions de l’Éditeur de source Excel ou de l’Éditeur de destination Excel.

Dans l’Assistant Importation et Exportation SQL Server

Dans l’Assistant Importation et Exportation, dans la page Choisir une source de données ou Choisir une destination, sélectionnez Microsoft Excel dans la liste Source de données.

Si vous ne voyez pas Excel dans la liste des sources de données, assurez-vous d’utiliser l’Assistant 32 bits. Les composants de connectivité Excel sont généralement des fichiers 32 bits et ne sont pas affichés dans l’Assistant 64 bits.

Fichier et chemin Excel

Les premières informations à fournir sont le nom et le chemin du fichier Excel. Entrez cette information dans l’Éditeur du gestionnaire de connexions Excel dans un package SSIS, ou dans la page Choisir une source de données ou Choisir une destination de l’Assistant Importation et Exportation.

Entrez le nom et le chemin du fichier au format suivant :

  • Pour un fichier sur un ordinateur local, entrez C:\TestData.xlsx.

  • Pour un fichier sur un partage réseau, entrez \\Sales\Data\TestData.xlsx.

Vous pouvez également sélectionner Parcourir pour localiser la feuille de calcul à l’aide de la boîte de dialogue Ouvrir .

Important

Vous ne pouvez pas vous connecter à un fichier Excel protégé par mot de passe.

Version Excel

La deuxième information à fournir est la version du fichier Excel. Entrez cette information dans l’Éditeur du gestionnaire de connexions Excel dans un package SSIS, ou dans la page Choisir une source de données ou Choisir une destination de l’Assistant Importation et Exportation.

Sélectionnez la version de Microsoft Excel ayant été utilisée pour créer le fichier, ou une autre version compatible. Par exemple, si vous n’avez pas pu installer les composants de connectivité 2016, vous pouvez installer les composants 2010 et sélectionner Microsoft Excel 2007-2010 dans cette liste.

Vous ne pouvez peut-être pas sélectionner les versions plus récentes d’Excel dans la liste si vous avez uniquement des versions antérieures des composants de connectivité installés. La liste des versions Excel inclut toutes les versions d’Excel prises en charge par SSIS. La présence d’éléments dans cette liste n’indique pas que les composants de connectivité requis sont installés. Par exemple, Microsoft Excel 2016 apparaît dans la liste même si vous n’avez pas installé les composants de connectivité 2016.

Note

À compter de SQL Server Management Studio 21 et SQL Server 2025, l’Assistant Importation et Exportation prend uniquement en charge un environnement 64 bits. Microsoft.JET.OLEDB.4.0 fonctionne uniquement dans des environnements 32 bits.

Pour utiliser l’Assistant Importation et Exportation pour les fichiers Excel dans un environnement 64 bits, téléchargez le moteur de base de données Microsoft Access 2016 Redistributable pour installer le fournisseur Microsoft.ACE.OLEDB.16.0. Sélectionnez ensuite Microsoft Excel 2016 comme version Excel dans l’Assistant Importation et Exportation SQL Server, par exemple la capture d’écran suivante :

Capture d’écran de l’Assistant Importation et Exportation d’Excel dans SSMS avec Microsoft Excel 2016 sélectionné.

Le fournisseur Microsoft.ACE.OLEDB.16.0 prend en charge les fichiers Excel créés par Excel 97-2003 (.xsl) et Excel 2007-2010 ( 2016)..xlsx

La première ligne possède des noms de colonnes

Si vous importez des données d’Excel, l’étape suivante consiste à indiquer si la première ligne de données contient des noms de colonnes. Spécifiez cette information dans l’Éditeur du gestionnaire de connexions Excel dans un package SSIS, ou dans la page Choisir une source de données de l’Assistant Importation et Exportation.

  • Si vous désactivez cette option parce que les données sources ne contiennent pas de noms de colonnes, l’Assistant utilise F1, F2 et ainsi de suite comme en-têtes de colonnes.
  • Si les données contiennent des noms de colonnes, mais que vous désactivez cette option, l’Assistant importe les noms de colonnes comme étant la première ligne de données.
  • Si les données ne contiennent pas de noms de colonnes, mais que vous activez cette option, l’Assistant utilise la première ligne de données sources comme noms de colonnes. Dans ce cas, la première ligne de données sources n’est plus incluse dans les données elles-mêmes.

Si vous exportez des données à partir d’Excel et que vous activez cette option, la première ligne de données exportées contient les noms de colonnes.

Feuilles de calcul et plages

Vous pouvez utiliser trois types d’objets Excel comme source ou destination de vos données : une feuille de calcul, une plage nommée ou une plage de cellules sans nom que vous spécifiez par son adresse.

  • Feuille de calcul. Pour spécifier une feuille de calcul, ajoutez le caractère $ à la fin du nom de la feuille et ajoutez des délimiteurs autour de la chaîne. Par exemple, [Sheet1$] . Vous pouvez aussi rechercher un nom qui se termine par le caractère $ dans la liste des tables et vues existantes.

  • Plage nommée. Pour spécifier une plage nommée, indiquez simplement le nom de la plage. Par exemple, MyDataRange. Ou recherchez un nom qui ne se termine pas par le $ caractère dans la liste des tables et vues existantes.

  • Plage sans nom. Pour spécifier une plage de cellules que vous n’avez pas nommée, ajoutez le caractère $ à la fin du nom de la feuille, ajoutez la spécification de plage ainsi que des délimiteurs autour de la chaîne. Par exemple, [Sheet1$A1:B4] .

Pour sélectionner ou spécifier le type d’objet Excel que vous souhaitez utiliser comme source ou destination de vos données, effectuez l’une des procédures suivantes :

Dans SSIS

Dans SSIS, dans la page Gestionnaire de connexions de l’Éditeur de source Excel ou de l’Éditeur de destination Excel, effectuez l’une des opérations suivantes :

  • Pour utiliser une feuille de calcul ou une plage nommée, sélectionnez Table ou vue comme Mode d’accès aux données. Ensuite, dans la liste Nom de la feuille Excel, sélectionnez la feuille de calcul ou la plage nommée.

  • Pour utiliser une plage sans nom que vous spécifiez par son adresse, sélectionnez Commande SQL comme Mode d’accès aux données. Puis, dans le champ Texte de la commande SQL, entrez une requête semblable à l’exemple suivant :

    SELECT * FROM [Sheet1$A1:B5]

Dans l’Assistant Importation et Exportation SQL Server

Dans l’Assistant Importation et Exportation, effectuez l’une des procédures ci-dessous :

  • Dans le cas d’une importation à partir d’Excel, effectuez l’une des opérations suivantes :

    • Pour utiliser une feuille de calcul ou une plage nommée, dans la page Spécifier la copie ou l’interrogation de table, sélectionnez Copier les données à partir d’une ou plusieurs tables ou vues. Puis, dans la page Sélectionner les tables et les vues sources, dans la colonne Source, sélectionnez les feuilles de calcul et les plages nommées sources.

    • Pour utiliser une plage sans nom que vous spécifiez par son adresse, dans la page Spécifier la copie ou l’interrogation de table, sélectionnez Écrire une requête pour spécifier les données à transférer. Puis, dans la page Fournir une requête source, spécifiez une requête semblable à l’exemple suivant :

      SELECT * FROM [Sheet1$A1:B5]

  • Dans le cas d’une exportation vers Excel, effectuez l’une des opérations suivantes :

    • Pour utiliser une feuille de calcul ou une plage nommée, dans la page Sélectionner les tables et les vues sources, dans la colonne Destination, sélectionnez les feuilles de calcul et les plages nommées de destination.

    • Pour utiliser une plage sans nom que vous spécifiez par son adresse, dans la page Sélectionner les tables et les vues sources, dans la colonne Destination, entrez la plage au format suivant sans délimiteurs : Sheet1$A1:B5. L’Assistant ajoute les délimiteurs nécessaires.

Une fois que vous avez sélectionné ou entré les objets Excel à importer ou exporter, vous pouvez également effectuer les opérations suivantes dans la page Sélectionner les tables et les vues sources de l’Assistant :

  • Vérifiez les mappages de colonnes entre la source et la destination en sélectionnant Modifier les mappages.

  • Affichez un aperçu des exemples de données pour vous assurer que le résultat est conforme à vos attentes, en sélectionnant Aperçu.

Problèmes avec les types de données

Types de données

Le pilote Excel ne reconnaît qu'un ensemble limité de types de données. Par exemple, toutes les colonnes numériques sont interprétées comme doubles (DT_R8) et toutes les colonnes de type chaîne (autres que les colonnes mémo) comme des chaînes Unicode de 255 caractères (DT_WSTR). SSIS mappe les types de données Excel de la façon suivante :

  • Numérique - virgule flottante double précision (DT_R8)

  • Devise - devise (DT_CY)

  • Booléen - booléen (DT_BOOL)

  • Date/heure - datetime (DT_DATE)

  • Chaîne - chaîne Unicode, longueur 255 (DT_WSTR)

  • Mémo - flux de texte Unicode (DT_NTEXT)

Conversions des types de données et des longueurs

SSIS ne convertit pas implicitement les types de données. Par conséquent, vous devrez peut-être utiliser des transformations de conversion de colonnes ou de données dérivées pour convertir explicitement des données Excel avant de les charger dans une destination autre qu’Excel, ou pour convertir des données à partir d’une source autre qu’Excel avant de la charger dans une destination Excel.

Voici quelques exemples de conversions qui peuvent être requises :

  • conversion entre des colonnes Excel de type chaîne Unicode et des colonnes de type chaîne non-Unicode avec des pages de codes spécifiques ;

  • conversion entre des colonnes Excel de type chaîne de 255 caractères et des colonnes de type chaîne de longueurs différentes ;

  • conversion entre des colonnes numériques Excel à double précision et des colonnes numériques d'autres types.

Conseil

Si vous utilisez l’Assistant Importation et Exportation et que vos données nécessitent certaines de ces conversions, l’Assistant configure automatiquement les conversions nécessaires. Par conséquent, même lorsque vous souhaitez utiliser un package SSIS, il peut être utile de créer le package initial à l’aide de l’Assistant Importation et Exportation. Laissez l’Assistant créer et configurer automatiquement les gestionnaires de connexions, les sources, les transformations et les destinations.

Problèmes d’importation

Lignes vides

Quand vous spécifiez une feuille de calcul ou une plage nommée comme source, le pilote lit le bloc de cellules contigu à partir de la première cellule non vide en haut à gauche de la feuille de calcul ou de la plage. Par conséquent, vos données peuvent ne pas commencer à la ligne 1, mais les données sources ne doivent pas contenir de lignes vides. Par exemple, vous ne pouvez pas avoir de ligne vide entre les en-têtes de colonnes et les lignes de données, ni avoir un titre suivi de lignes vides en haut de la feuille de calcul.

Si vos données sont précédées de lignes vides, vous ne pouvez pas interroger les données comme une feuille de calcul. Dans Excel, vous devez sélectionner la plage de données et lui attribuer un nom, puis interroger la plage nommée au lieu de la feuille de calcul.

Valeurs manquantes

Le pilote Excel lit un certain nombre de lignes (par défaut, huit lignes) dans la source spécifiée pour déterminer le type de données de chaque colonne. Lorsqu'il s'avère qu'une colonne combine différents types de données, notamment des données numériques avec des données texte, le pilote porte son choix sur le type de données majoritaire et retourne des valeurs NULL dans les cellules qui contiennent des données de l'autre type. (Dans une liaison, le type numérique gagne.) La plupart des options de mise en forme de cellule dans la feuille de calcul Excel ne semblent pas affecter cette détermination de type de données.

Vous pouvez modifier ce comportement du pilote Excel en spécifiant le mode d’importation qui importe toutes les valeurs sous forme de texte. Pour spécifier le mode d’importation, ajoutez IMEX=1 à la valeur de Propriétés étendues dans la chaîne de connexion du gestionnaire de connexions Excel dans la fenêtre Propriétés.

Texte tronqué

Lorsque le pilote détermine qu'une colonne Excel contient des données texte, il sélectionne le type de données (string ou memo) en fonction de la valeur la plus longue qu'il échantillonne. Si le pilote ne découvre pas de valeurs supérieures à 255 caractères dans les lignes qu’il échantillonne, il traite la colonne comme une colonne de chaîne de 255 caractères au lieu d’une colonne mémo. Par conséquent, les valeurs supérieures à 255 caractères peuvent être tronquées.

Pour importer des données d’une colonne de type mémo sans troncation, vous avez deux options :

  • Assurez-vous que la colonne mémo dans au moins une des lignes échantillonnées contient une valeur de plus de 255 caractères.

  • Augmentez le nombre de lignes échantillonnées par le pilote pour inclure une ligne. Vous pouvez augmenter le nombre de lignes échantillonnées en augmentant la valeur de TypeGuessRows sous la clé de Registre suivante :

Version des composants Redistributable Clé de Registre
Excel 2016 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel
Excel 2010 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

Problèmes d’exportation

Créer un fichier de destination

Dans SSIS

Créez un gestionnaire de connexions Excel en indiquant le nom et le chemin du nouveau fichier Excel que vous souhaitez créer. Ensuite, dans l’Éditeur de destination Excel, pour Nom de la feuille Excel, sélectionnez Nouveau pour créer la feuille de calcul de destination. SSIS crée alors le fichier Excel avec la feuille de calcul spécifiée.

Dans l’Assistant Importation et Exportation SQL Server

Dans la page Choisir une destination, sélectionnez Parcourir. Dans la boîte de dialogue Ouvrir, accédez au dossier où vous souhaitez créer le fichier Excel, entrez un nom pour ce nouveau fichier, puis sélectionnez Ouvrir.

Exporter vers une plage suffisamment grande

Quand vous spécifiez une plage comme destination, une erreur se produit si la plage a moins de colonnes que dans les données sources. Toutefois, si la plage que vous spécifiez a moins de lignes que dans les données sources, l’Assistant continue d’écrire des lignes sans erreur et étend la définition de la plage pour qu’elle corresponde au nouveau nombre de lignes.

Exporter des valeurs texte longues

Avant de pouvoir enregistrer des chaînes dépassant 255 caractères dans une colonne Excel, le pilote doit reconnaître le type de données de la colonne de destination comme mémo et non comme chaîne.

  • Si une table de destination existante contient déjà des lignes de données, les premières lignes échantillonnées par le pilote doivent contenir au moins une instance d’une valeur dépassant 255 caractères dans la colonne mémo.

Contenu connexe

  • Last updated on