Charger des cellules Excel spécifiques dans une table avec SSIS

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

Lors du chargement de données depuis un fichier Excel dans une table SQL, avec un package SSIS il est possible de cibler une plage de cellules bien spécifiques. 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.

Charger une plage de cellules Excel spécifiques dans une table SQL 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 utilisé dans les exemples

Voici un aperçu de a 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.

Data range sample from the Excel file used with the SSIS SQL queries
Plage de données du fichier Excel utilisé avec les requêtes SQL SSIS

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

1. Exemple de requête SQL pour charger une plage Excel entière 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];
Requête SQL dans le composant Excel Source SSIS pour sélectionner une plage
Requête SQL dans le composant Excel Source SSIS pour sélectionner une plage

2. Exemple de requête SSIS pour filtrer les lignes vides avec les noms de colonnes

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";
Requête SQL sur Excel avec SSIS avec un filtre sur les colonnes du fichier
Requête SQL sur Excel avec SSIS avec un filtre sur les colonnes du fichier

3. Variante pour filtrer sans utiliser 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";
Requête SQL avec SSIS avec filtre sur nom de colonne générique
Requête SQL avec SSIS avec filtre sur nom de colonne générique

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

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

SELECT * FROM [Sheet1$A3:E]
Requête SQL pour charger les cellules Excel et filtrer jusqu'à la dernière ligne de données
Requête SQL pour charger les cellules Excel et filtrer jusqu’à la dernière ligne de données

Créer le package 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 de ce 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.

Soyez le premier à commenter

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée.


*