Exécuter des requêtes SQL sur des fichiers Excel

Bien que les actions Excel puissent gérer la plupart des scénarios d’automatisation Excel, les requêtes SQL peuvent récupérer et manipuler plus efficacement des quantités importantes de données Excel.

Supposons qu’un flux doive modifier uniquement les registres Excel qui contiennent une valeur particulière. Pour obtenir cette fonctionnalité sans requêtes SQL, vous avez besoin de boucles, de conditions et de plusieurs actions Excel.

Sinon, vous pouvez mettre en œuvre cette fonctionnalité avec des requêtes SQL en utilisant seulement deux actions : Ouvrir une connexion SQL et Exécuter des instructions SQL.

Ouvrir une connexion SQL dans un fichier Excel

Avant d’exécuter une requête SQL, vous devez ouvrir une connexion avec le fichier Excel auquel vous voulez accéder.

Pour établir la connexion, créez une variable nommée %Excel_File_Path% et initialisez-la avec le chemin d’accès du fichier Excel. Vous pouvez éventuellement ignorer cette étape et utiliser le chemin d’accès codé en dur du fichier plus tard dans le flux.

Maintenant, déployez l’action Ouvrir la connexion SQL et remplissez la chaîne de connexion suivante dans ses propriétés.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%Excel_File_Path%;Extended Properties="Excel 12.0 Xml;HDR=YES";

Note

Pour utiliser correctement la chaîne de connexion présentée, vous devez télécharger et installer Moteur de base de données Microsoft Access 2010 redistribuable.

Ouvrir une connexion SQL dans un fichier Excel protégé par mot de passe

Une approche différente est requise dans les cas où vous exécutez des requêtes SQL sur des fichiers Excel protégés par mot de passe. L’action Ouvrir la connexion SQL ne peut pas se connecter aux fichiers Excel protégés par mot de passe, vous devez donc supprimer la protection.

Pour cela, lancez le fichier Excel à l’aide de l’action Lancer Excel. Le fichier étant protégé par mot de passe, saisissez le mot de passe approprié dans le champ Mot de passe.

Ensuite, déployez les actions d’automatisation de l’interface utilisateur appropriées et allez dans Fichier>Informations>Protéger le classeur>Chiffrer avec mot de passe. Trouvez plus d’informations sur l’automatisation de l’interface utilisateur et sur l’utilisation des actions respectives dans Automatiser les applications de bureau.

Après avoir sélectionné Chiffrer avec le mot de passe, remplissez une chaîne vide dans la boîte de dialogue contextuelle à l’aide de l’action Remplir le champ de texte dans la fenêtre. Pour remplir une chaîne vide, utilisez l’expression suivante : %""%.

Pour appuyer sur le bouton OK dans la boîte de dialogue et appliquer les modifications, déployez l’action Appuyer sur le bouton dans la fenêtre.

Enfin, déployez l’action Fermer Excel pour enregistrer le classeur non protégé en tant que nouveau fichier Excel.

Après avoir enregistré le fichier, suivez les instructions dans Ouvrir une connexion SQL dans un fichier Excel pour ouvrir une connexion à celui-ci.

Une fois la manipulation du fichier Excel terminée, utilisez l’action Supprimer les fichiers pour supprimer la copie non protégée du fichier Excel.

Lire le contenu d’une feuille de calcul Excel

Bien que l’action Lire à partir de la feuille de calcul Excel permette de lire le contenu d’une feuille de calcul Excel, les boucles peuvent prendre beaucoup de temps à parcourir les données récupérées.

Un moyen plus efficace de récupérer des valeurs spécifiques à partir de feuilles de calcul consiste à traiter les fichiers Excel comme des bases de données et à exécuter des requêtes SQL sur eux. Cette approche est plus rapide et augmente les performances du flux.

Pour récupérer tout le contenu d’une feuille de calcul, utilisez la requête SQL suivante dans l’action Exécuter l’instruction SQL.

SELECT * FROM [SHEET$]

Note

Pour appliquer cette requête SQL dans vos flux, remplacez l’espace réservé FEUILLE par le nom de la feuille de calcul à laquelle vous souhaitez accéder.

Pour récupérer les lignes qui contiennent une valeur particulière dans une colonne spécifique, utilisez la requête SQL suivante :

SELECT * FROM [SHEET$] WHERE [COLUMN NAME] = 'VALUE'

Note

Pour appliquer cette requête SQL dans vos flux, remplacez :

• SHEET par le nom de la feuille de calcul à laquelle vous voulez accéder.
• NOM DE COLONNE par la colonne qui contient la valeur que vous voulez trouver. Les colonnes de la première ligne de la feuille de calcul Excel sont identifiées comme les noms de colonne de la table.
• VALUE par la valeur que vous voulez trouver.

Supprimer des données d’une ligne Excel

Même si Excel ne prend pas en charge la requête SQL DELETE, utilisez la requête UPDATE pour définir toutes les cellules d’une ligne spécifique sur la valeur Null.

Plus précisément, vous pouvez utiliser la requête SQL suivante :

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Pendant le développement du flux, vous devez remplacer l’espace réservé SHEET par le nom de la feuille de calcul à laquelle vous voulez accéder.

Les espaces réservés COLUMN1 et COLUMN2 représentent les noms des colonnes à gérer. Cet exemple a deux colonnes, mais dans un scénario réel, le nombre de colonnes peut différer. Les colonnes de la première ligne de la feuille de calcul Excel sont identifiées comme les noms de colonnes du tableau.

La partie [COLUMN1]=’VALUE’ de la requête définit la ligne à mettre à jour. Dans votre flux, utilisez le nom de la colonne et la valeur en fonction de la combinaison qui décrit les lignes de manière unique.

Récupérer des données Excel à l’exception d’une ligne spécifique

Dans certains cas, vous voulez peut-être récupérer tout le contenu d’une feuille de calcul Excel à l’exception d’une ligne spécifique.

Un moyen pratique d’y parvenir consiste à définir les valeurs de la ligne non voulue sur Null et de récupérer ensuite toutes les valeurs à l’exception des valeurs Null.

Pour modifier les valeurs d’une ligne spécifique de la feuille de calcul, vous pouvez utiliser une requête SQL UPDATE, telle que présentée dans Supprimer des données d’une ligne Excel :

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Ensuite, exécutez la requête SQL suivante pour récupérer toutes les lignes de la feuille de calcul qui ne contiennent pas de valeurs nulles :

SELECT * FROM [SHEET$] WHERE [COLUMN1] IS NOT NULL OR [COLUMN2] IS NOT NULL

Les espaces réservés COLUMN1 et COLUMN2 représentent les noms des colonnes à gérer. Cet exemple a deux colonnes, mais dans une table réelle, le nombre de colonnes peut différer. Toutes les colonnes de la première ligne de la feuille de calcul Excel sont identifiées comme les noms de colonne de la table.