Charger des cellules Excel avec SSIS dans une table SQL

Comment charger une plage de cellules Excel spécifiques dans une table SQL Server avec SSIS ?

Lors de l’import de données depuis un fichier XLSX dans une table SQL, il est possible de charger des cellules Excel spécifiques avec les options SSIS. La plage sera forcément contiguë, en d’autres termes, les cellules Excel à charger sont côte à côte. Cependant, pour charger plusieurs plages de cellules non contiguës, il faut adapter la méthode présentée dans ce tutoriel.

Requêtes pour importer une plage spécifique de cellules Excel avec SSIS

En effet, pour charger une plage Excel où les cellules sont séparées ou encore seulement certaine cellules Excel ciblées. Il est nécessaire de passer par un développement de flux de données SSIS spécifique pour traiter chaque plage séparément. Il faut exécuter plusieurs flux de données ou une boucle avec des requêtes dynamiques.

Pour charger une plage particulière de cellules Excel, il est nécessaire d’écrire une requête statique. Cette requête permet de préciser quelles sont les cellules à charger. Les tableurs Excel sont extraordinairement puissants et ils sont très souvent intégrés en base de données pour les projets d’entrepôt de données.

Télécharger le fichier Excel utilisé dans le tutoriel

Voici un aperçu de la plage de données utilisée dans le fichier Excel, elle contient 24 lignes de données, et après chaque bloc de 5 lignes, il y a une ligne vide, pour tester le comportement des filtres depuis SSIS.

Voici le lien pour télécharger le fichier Excel source.

1. Requête SQL pour charger une plage Excel avec SSIS

Dans ce premier exemple de requête SQL, la requête permet de récupérer toutes les cellules d’un tableau Excel. C’est à dire de la 3ème à la 13ème ligne, et de la colonne A jusqu’à la colonne C du fichier MS Excel à charger avec le package.

Stocker ou non les données sources dans un tableau Excel ne change pas la procédure. Autrement dit cette requête charge les cellules Excel sans filtrer les données en amont. Cependant les données chargées s’arrêtent à la première ligne vide.

SELECT * FROM [Sheet1$A3:C13];

2. Requête SSIS pour filtrer les lignes Excel vides

Dans ce deuxième exemple, la requête SQL permet de filtrer les données. Et donc de sélectionner toutes les lignes avec la première cellule de la zone sélectionnée non nulle. Et à partir de la 3ème à la 10ème ligne, de la colonne A jusqu’à la colonne D, du fichier Microsoft Excel.

SELECT * FROM [Sheet1$A3:C13] WHERE CustomerID IS NOT NULL;

Utiliser le paramètre HDR=YES pour indiquer que les noms de colonnes sont fixes et utilisés dans la requête par exemple.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\data\Customers_Data_Sample.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";

3. Filtrer les données Excel sans les noms de colonnes

Pour utiliser les noms de colonnes génériques du type F1, F2, F3, etc. Utiliser une syntaxe comme celle-ci pour construire la chaîne de connexion Excel. Cette option est particulièrement pratique pour charger des données avec des noms de colonnes qui peuvent évoluer.

SELECT * FROM [Sheet1$A3:C13] WHERE F1 IS NOT NULL

Utiliser donc l’option HDR=NO pour ne pas activer la gestion des noms de colonnes. HDR est d’ailleurs simplement l’abréviation de Header.

Data Source=C:\dossier\fichier.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties="Excel 12.0 Xml;HDR=NO";

4. Requête pour sélectionner les lignes Excel dynamiquement

Pour sélectionner les données jusqu’à la dernière ligne, utiliser cette variante de la requête.

SELECT * FROM [Sheet1$A3:E]

5. Créer le package SSIS en quatre étapes

Pour enfin mettre en pratique ces requêtes, importer des fichiers Excel vers SQL Server et effectuer d’autres tâches de transformations. Comme le filtrage et le tri des données avant le chargement dans la base de données. Utiliser aussi SSIS pour transférer des données Excel dans les deux sens, c’est à dire importer des données depuis Excel vers le serveur SQL ou exporter des données depuis MS SQL vers un fichier Excel.

La création du package se fait en 4 étapes, dans un flux de données Integration Services:

1. Créer d’abord une connexion avec le gestionnaire SSIS pour pointer sur le fichier source.
2. Ajouter une Excel Source qui utilise la connexion et utiliser la requête pour filtrer les cellules.
3. Créer une destination OLE DB vers la base de données et la table cible et la configurer.
4. Configurer ensuite le mapping des données.

Pour le détail des étapes, voici comment charger un fichier Excel dans une table SQL Server. Ce tutoriel SSIS explique pas à pas comment charger les cellules depuis une feuille Excel dans une table. Il est aussi possible de charger un fichier Excel dans une table avec l’assistant SQL Server.

Importer un fichier Excel dans une base SQL Server avec SSIS