Charger des cellules Excel avec SSIS et filtrer les lignes

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

Pour importer des données depuis un fichier Excel au format XLS ou XLSX, dans une table SQL Server, il est possible de charger des cellules spécifiques avec les options SSIS. La plage de données 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 avec une requête, il faut adapter les requêtes de ce tutoriel et la méthode.

En effet, pour charger une plage Excel ciblée, utiliser un flux de données SSIS spécial pour traiter chaque plage séparément. Il faut exécuter plusieurs flux de données ou une boucle avec des requêtes dynamiques. Le choix se fera en fonction des besoins du projet.

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

1. 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 les requêtes SQL dans SSIS. Voici le lien pour télécharger le fichier Excel source.

2. Requête SQL pour charger une plage de cellules Excel avec SSIS

Dans ce premier exemple de requête SQL, la requête permet de récupérer les données d’un tableau Excel sur une plage bien définie. C’est à dire de la 3ème à la 13ème ligne, et de la colonne A jusqu’à la colonne C du fichier Excel. Cette requête qui ressemble à la syntaxe du T-SQL permet de lire les données sources dans un tableau Excel et n’a pas de clause WHERE pour filtrer les données.

Autrement dit cette requête charge toutes les cellules Excel présentes dans la plage sélectionnée sans filtrer les données en amont. On note donc la première ligne vide du tableau, qui correspond à la ligne 9 du fichier Excel.

SELECT * FROM [Sheet1$A3:C13];

3. Requête SQL dans SSIS pour filtrer les lignes vides d’Excel et avec les entêtes

Dans ce deuxième exemple, la requête SQL permet de filtrer les données du fichier Excel avec une simple clause WHERE. Et donc de sélectionner toutes les lignes non nulles de la plage sélectionnée. En d’autres termes, de la 3ème à la 10ème ligne, de la colonne A jusqu’à la colonne D, du fichier Microsoft Excel à importer.

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

Dans la chaîne de connexion SSIS, utiliser le paramètre HDR=YES pour indiquer que les noms de colonnes sont fixes et utilisés dans la requête, comme dans celle ci :

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

Voici le résultat de la requête, la ligne vide du fichier Excel n’est plus sélectionnée.

4. Filtrer les données du tableau Excel sans noms de colonnes et sans entêtes

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 ou encore simplement pour importer un fichier Excel sans entêtes et uniquement des données.

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

Utiliser cette fois l’option HDR=NO pour ne pas activer la gestion des noms de colonnes. L’option HDR dans la chaine de connexion Excel 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";

5. Requête pour importer les lignes Excel de façon dynamique avec SSIS

Dans ce dernier exemple de requête, voici comment charger des cellules Excel avec SSIS et sélectionner les données jusqu’à la dernière ligne du tableau sans connaitre le nombre de lignes. Pour cela utiliser cette variante de la requête, ou il suffit de renseigner uniquement le nom de la colonne la plus à droite de la plage à charger sans le numéro de ligne. Les lignes vides sont chargées, pour les filtrer, ajouter à nouveau la clause WHERE comme dans les exemples précédents.

SELECT * FROM [Sheet1$A3:E]

Filtrer les données Excel avec une clause WHERE depuis SSIS

Dans le même sens, pour filtrer les données et n’afficher que certaines lignes, utiliser une requête de ce type :

SELECT * FROM [Sheet1$A3:E] WHERE FirstName = 'Leonard';

6. 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.

Le tutoriel suivant SSIS explique pas à pas comment charger les cellules depuis une feuille Excel dans une table, cette fois avec SSMS. Il est aussi possible de charger un fichier Excel dans une table avec l’assistant SQL Server.