Nettoyer les textes par les expressions régulières en VBA

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Nettoyer les chaînes de textes par expressions régulières

Cette formation fait suite aux deux précédentes. Dans le premier volet, nous avions réalisé l'extraction et la consolidation des mots clés de recherche, en parcourant des fichiers de cache déposés par le serveur. Dans le deuxième volet, nous avions exploité cette consolidation pour offrir des suggestions intuitives au cours de la saisie, dans le moteur de recherche. Mais comme nous l'avions constaté, un problème persiste. Ces fichiers de cache proposent le code Html complet des résultats pour une restitution instantanée sur une page Web. Or ces balises Html rendent l'extraction illisible sur une feuille Excel.



La capture ci-dessus illustre le résultat de l'application finalisée. L'objectif est de développer un code VBA à validation de l'un des termes de recherche, afin de purger le fichier de cache correspondant, à l'aide des expressions régulières.

Récupération des sources et présentation
Pour simuler l'application et finaliser son code, nous avons besoin des fichiers de cache, des fichiers consolidés mais aussi de l'application Excel en cours de développement.

• Télécharger le fichier purger-par-expressions-regulieres.rar en cliquant sur son lien,
• Le décompresser dans le dossier de votre choix,

Comme vous le constatez, le classeur de l'application Excel est accompagné des deux sous dossiers cache et consolidation. Le sous dossier consolidation propose les fichiers dans lesquels sont concaténés tous les termes de recherche, sur la base des fichiers de cache. C'est l'un de ces fichiers qui a été exploité pour créer la saisie automatique par le code VBA.

• Double cliquer sur le sous dossier cache pour l'ouvrir,

Vous y notez la présence d'innombrables fichiers de texte. Leurs noms consistent en un assemblage des mots clés tapés par les internautes dans le moteur de recherche du site Bonbache.fr.

• Double cliquer par exemple sur le fichier absolues-excel.txt pour l'ouvrir,

Comme vous le constatez, ce fichier délivre un contenu bien difficile à déchiffrer. C'est pourtant lui qui contient les résultats à afficher si la demande de l'utilisateur porte sur les termes absolues et Excel. Mais ces balises Html commencent nécessairement par le symbole inférieur (<) et se terminent nécessairement par le symbole supérieur (>). Ce que nous ne connaissons pas à l'avance, c'est le contenu présent entre ces deux symboles. Et c'est là qu'interviennent les expressions régulières. Comme leur nom l'indique, elles sont capables de trouver et traiter toutes les occurrences qui ont une signature particulière. Ces deux symboles les rendent remarquables. Il s'agit de l'une des clés pour cette formation.

Instancier la classe des expressions régulières
Dans de nombreuses formations, nous avons fait appel à la fonction VBA CreateObject qui permet de désigner une librairie de code présente sur le système d'exploitation, sans doute une Dll. C'est une fois encore le cas ici avec le paramètre vbscript.regexp, pour créer et manipuler ces expressions régulières.

• Fermer le fichier de texte,
• A la racine du dossier de décompression, double cliquer sur le fichier purger-par-expressions-regulieres.xlsm pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,
• Dans la zone du moteur de recherche, taper le début d'expression abs,
• Dans la liste qui apparaît juste en dessous, cliquer sur la proposition absolues excel,

Comme l'illustre la capture ci-dessus, le choix est accepté, la recherche est lancée et l'extraction produit le contenu exact du fichier texte que nous avons consulté précédemment. Son code Html est restitué sur une ligne et il apparaît tronqué. Bien qu'illisible à ce stade, ce fichier de cache propose plusieurs résultats en accord avec les mots clés de la recherche. En même temps que nous allons devoir purger le fichier de ses balises, nous allons devoir découper l'information. L'objectif est de proposer chaque résultat indépendant, sur des cellules explicitement différentes, les unes en dessous des autres.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur de code VBA Excel,

Vous y notez la présence du code VBA que nous avons développé au cours des formations précédentes. La procédure moteur_change permet de réaliser cette saisie semi-automatique au fil de la frappe des caractères dans le moteur de recherche. La procédure validation est celle qui réceptionne les mots clés pour récupérer le contenu du fichier de cache correspondant. C'est donc le code VBA de cette dernière que nous devons ajuster. Après la fermeture du fichier de texte, soit après l'instruction Close #1, se trouve la ligne qui restitue le contenu complet tel qu'il a été chargé.

...
Loop
Close #1

ligne = 6
Cells(ligne, 2) = contenu

Exit Sub
...

Avant de restituer ce contenu(variable contenu) en l'état, nous devons donc le retravailler. Et pour cela, nous proposons de commencer par créer une fonction dédiée.

• En dessous de la procédure validation, créer la fonction res_decoupe comme suit :

Function res_decoupe(chaine As String) As String

End Function

La procédure validation devra désormais passer le contenu récupéré à partir du fichier texte à la fonction res_decoupe. C'est la raison pour laquelle, nous la créons avec un paramètre en attente, de type String. Une fois son traitement terminé, elle retournera la chaîne nettoyée à la procédure validation qui pourra se charger de restituer les résultats dans des cellules indépendantes de la feuille. Et comme nous l'avait appris la formation VBA pour créer des fonctions, c'est par le nom même de cette dernière que ce retour est effectué. Il n'y a pas d'instruction Return comme en Javascript, Php ou encore C#.

• Dans la partie déclarative de la procédure validation, ajouter la déclaration suivante :

Private Sub validation()
On Error GoTo pb:
Dim chaine As String: Dim contenu As String
Dim ligne As Integer
Dim tab_res() As String

prop.Clear
...

Nous ajoutons la déclaration du tableau de variables tab_res. La fonction res_decoupe doit aussi se charger d'introduire un caractère remarquable entre chaque résultat. A réception de la valeur de retour, nous en profiterons pour découper l'information sur ce caractère. Et pour cela, comme dans la formation précédente, nous devons exploiter la fonction VBA Split.

• Dans la fonction res_decoupe, déclarer les deux variables objets comme suit :

Dim expReg As Object: Dim motif As Object

La variable expReg doit servir à instancier la classe permettant de manipuler les expressions régulières. La variable motif est celle qui doit permettre de trouver l'expression régulière créée selon un pattern (Un modèle de texte reconnaissable) dans la chaîne de caractères.

• A la suite du code de la fonction, ajouter l'instruction suivante :

Set expReg = CreateObject("vbscript.regexp")

Grâce au paramètre vbscript.regexp passé à la précieuse fonction VBA CreateObject, nous instancions la classe permettant de manipuler les expressions régulières. De fait, l'objet expReg hérite de toutes les propriétés et méthodes nécessaires.

Motifs d'expressions régulières - Pattern
Comme nous l'avons évoqué plus haut, nous connaissons la forme des fragments que nous souhaitons éliminer du résultat, mais pas le contenu exact. C'est le rôle des expressions régulières. L'objectif est de leur indiquer comment elles commencent et se terminent par exemple. Tout ce qui y ressemble sera trouvé. Grâce à elle, il devient très simple de déceler la présence d'une date dans un texte pourtant dense. Une date possède en effet une forme remarquable. Le plus souvent il s'agit de paires de chiffres séparées par des slashs ou des tirets. Un chiffre varie de 0 à 9. Il est possible de réaliser une énumération des termes à considérer par une expression régulière. Cette énumération doit intervenir entre crochets. Le motif [0-9] par exemple indique qu'à l'emplacement désigné, n'importe quel chiffre compris entre 0 et 9, peut être trouvé. Lorsqu'une séquence ou un caractère est indiqué entre parenthèse, il doit être respecté scrupuleusement. Ainsi, avec l'expression régulière suivante :

[0-9][0-9](/)[0-9][0-9](/)[0-9][0-9]

Nous indiquons explicitement que nous cherchons tout ce qui a la forme d'une date comme par exemple : 20/03/18. Et nous pourrions simplifier l'expression de la façon suivante :

[0-9]*(/)[0-9]*(/)[0-9]*

Dans les expressions régulières, le symbole de l'étoile (*) indique en effet que la séquence qui précède, est susceptible de se répéter un nombre de fois indéfini. Nous pourrions ainsi trouver tout ce qui est de la forme : 20/03/2018.

Forts de ces prérequis nous pouvons débuter simplement la mise en oeuvre par l'opération qui consiste à découper la chaîne, de façon à restituer les différents résultats indépendamment. En effet, à chaque fois que la balise d'un lien organique est fermée (</a>), nous savons qu'il s'agit de la fin d'un résultat et du début d'un autre. Nous souhaitons remplacer toutes ces occurrences par le caractère remarquable de la barre verticale (|). Le motif à construire est très simple puisqu'il ne dissimule aucune incertitude.

• A la suite du code de la fonction, ajouter les instructions suivantes :

expReg.Pattern = "(</a>)"
Set motif = expReg.Execute(chaine)

While motif.Count > 0
chaine = expReg.Replace(chaine, "|")
Set motif = expReg.Execute(chaine)

Wend

res_decoupe = chaine

Grâce à la propriété Pattern héritée lors de l'instanciation, nous définissons le motif très simple entre parenthèses. Il s'agit en effet de trouver toutes les occurrences strictes d'une balise fermante de lien organique. Ensuite, grâce à la méthode Execute de l'objet expReg, nous initialisons l'objet motif pour qu'il hérite des propriétés nécessaires à la manipulation de ce pattern. C'est alors sa propriété Count qui permet de savoir si le motif est présent. Et pour les trouver tous, nous incluons le test dans une boucle While. Ainsi, nous parcourons l'intégralité de la chaîne passée en paramètre de la fonction. De fait, nous exploitons la méthode Replace de l'objet expReg afin de remplacer ce motif par la barre verticale, à chaque fois qu'il est trouvé. Nous n'oublions de réaffecter l'objet motif avant de boucler pour trouver l'occurrence suivante et ainsi valider le critère de la boucle While. En dehors de la boucle, une fois le traitement terminé donc, nous affectons la fonction par son nom au résultat ainsi transformé, mémorisé dans la variable chaine. Il suffira de l'appeler pour récupérer le résultat.

Certes, le remplacement que nous effectuons est tellement trivial que nous aurions pu nous passer des expressions régulières. L'emploi de la fonction VBA Replace aurait suffi. Mais l'objectif est de nous familiariser avec ces dernières. Ainsi nous pourrons plus facilement comprendre le mécanisme, lorsqu'il s'agira de purger toutes les autres balises sans exception. Et c'est ce à quoi nous allons bientôt procéder. Mais avant cela, il s'agit de tester le résultat retourné par la fonction. Pour ce faire, il suffit de l'appeler par son nom, depuis la procédure validation.

• A la fin de la procédure validation, remplacer la ligne : Cells(ligne, 2) = contenu, par l'instruction suivante :

MsgBox res_decoupe(contenu)

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille (ALT + F11),
• Supprimer l'information présente dans le moteur de recherche,
• Puis, taper les premières lettres suivantes : form,
• Dans la liste des suggestions intuitives, cliquer sur format conditionnel,

Le choix est aussitôt transmis et la procédure validation exécutée. De fait, le contenu du fichier texte est passé à la fonction res_decoupe qui se charge du traitement. Et la fonction VBA MsgBox permet d'afficher le résultat à l'écran, comme l'illustre la capture ci-dessous.

Dans l'aperçu qui nous est donné en effet, vous remarquez la présence de plusieurs barres verticales, en lieu et place des balises fermantes des liens.



Nous savons désormais que nous pourrons découper cette chaîne d'information afin de détacher chacun des résultats. Mais à ce stade néanmoins, l'information reste complètement illisible. Nous devons créer un motif d'expression régulière, capable de reconnaître n'importe quelle balise Html, quelque soit son contenu. Entre le symbole inférieur et le symbole supérieur, nous devons indiquer que tout type de caractère peut être trouvé comme :

• Une lettre de l'alphabet en majuscule ou en minuscule,
• Un chiffre compris entre 0 et 9,
• Un caractère spécial comme le point, le tiret, le point-virgule, les deux points etc...

L'énumération de l'ensemble de ces caractères doit intervenir entre crochets. Et comme ils peuvent se répéter un nombre de fois indéfini, le symbole étoile (*) est de mise.

• Valider la boîte de dialogue du MsgBox,
• Revenir dans l'éditeur de code VBA Excel (ALT + F11),
• A la suite du code de la fonction, mais avant l'affectation : res_decoupe = chaine, ajouter les instructions suivantes :

expReg.Pattern = "(<)[0-9a-zA-Z_   \/.:;'=-]*(>)"
Set motif = expReg.Execute(chaine)

While motif.Count > 0
chaine = expReg.Replace(chaine, "")
Set motif = expReg.Execute(chaine)
Wend

Le motif créé grâce à la propriété Pattern est explicite. Nous cherchons toutes les occurrences commençant par le symbole inférieur (<) et se terminant par le symbole supérieur (>). Au milieu, nous énumérons tous les caractères possibles ([0-9a-zA-Z_   \/.:;'=-]) sachant qu'ils peuvent se répéter de nombreuses fois (*). Puis, selon les mêmes techniques que précédemment, nous l'exécutons sur la chaîne passée en paramètre de la fonction dans une boucle While. Et comme vous le constatez, cette fois dans la boucle, nous remplaçons chacune de ces occurrences identifiées, par une chaîne vide (expReg.Replace(chaine, "")). En d'autres termes, nous supprimons les balises Html à la volée en quelques petites lignes de code, quelle que soit leur nature et leur contenu. Pour en avoir le coeur net, il suffit de tester.

• Enregistrer les modifications et basculer sur la feuille Excel,
• Cliquer sur le bouton Ok pour valider l'expression précédemment choisie,

Comme l'illustre la capture ci-dessus, le traitement est concluant. Toutes les balises Html ont disparu et les rubriques sont désormais parfaitement lisibles. De plus elles sont toutes explicitement séparées d'un caractère remarquable grâce au traitement précédent.

Le code complet de la fonction res_decoupe, pour purger une chaîne de caractères par les expressions régulières, est le suivant :

Function res_decoupe(chaine As String) As String
Dim expReg As Object: Dim motif As Object

Set expReg = CreateObject("vbscript.regexp")
expReg.Pattern = "(</a>)"
Set motif = expReg.Execute(chaine)

While motif.Count > 0
chaine = expReg.Replace(chaine, "|")
Set motif = expReg.Execute(chaine)
Wend

expReg.Pattern = "(<)[0-9a-zA-Z_   \/.:;'=-]*(>)"
Set motif = expReg.Execute(chaine)

While motif.Count > 0
chaine = expReg.Replace(chaine, "")
Set motif = expReg.Execute(chaine)
Wend

res_decoupe = chaine

End Function

Extraire les données par découpe de chaîne
Il s'agit désormais de découper l'information ainsi nettoyée sur le caractère remarquable grâce à la fonction VBA Split. Chaque réponse sera ainsi stockée dans une rangée indépendante du tableau de variables que nous avons déclaré en début de formation. Il ne restera plus qu'à le parcourir entre sa borne inférieure et sa borne supérieure pour restituer chaque réponse, dans une cellule indépendante de la feuille Excel.

• Valider la boîte de dialogue du MsgBox,
• Revenir dans l'éditeur de code VBA Excel,
• Dans la procédure validation, passer la ligne du MsgBox en commentaire en la préfixant d'une apostrophe,
• Après le commentaire et avant le Exit Sub, ajouter les deux instructions suivantes :

contenu = res_decoupe(contenu)
tab_res = Split(contenu, "||")

Certes, une seule ligne aurait suffi : tab_res = Split(res_decoupe(contenu) , "||"). Néanmoins, avec la première ligne, nous récupérons la chaîne purgée par la fonction res_descoupe et la stockons de nouveau dans la variable contenu. Puis, avec la méthode Split, nous découpons le texte contenu dans cette variable sur le caractère remarquable de la barre verticale. Cette dernière est doublée du fait du nettoyage par les expressions régulières, qui a rapproché les rubriques. Comme nous affectons le résultat à un tableau de variables, celui-ci se dimensionne automatiquement en fonction du nombre d'éléments réceptionné, pour les ranger chacun dans des cellules indépendantes.

Il ne reste plus qu'à parcourir tous les éléments de ce tableau et de les retranscrire sur les cellules de la feuille Excel, les uns en dessous des autres.

• A la suite du code VBA précédent, ajouter la boucle de traitement suivante :

For ligne = 6 To 6 + UBound(tab_res()) - 1
Cells(ligne, 2).Value = tab_res(ligne - 6)
Next ligne

Nous débutons la boucle à partir de l'indice 6 car la première cellule de la feuille à affecter se situe en ligne 6. De fait, pour parcourir toutes les rangées du tableau de variables, nous ajoutons 6 unités à sa borne supérieure (6 + UBound(tab_res()) - 1). A chaque passage dans la boucle, il ne reste plus qu'à restituer le terme de recherche isolé, dans la cellule ainsi désignée (Cells(ligne,2).Value), avec un indice de ligne variable. Comme nous n'avons pas débuté la boucle sur la première rangée du tableau, pour atteindre l'élément correspondant, nous retranchons 6 unités en paramètre (tab_res(ligne- 6)).

• Enregistrer les modifications et basculer sur la feuille Excel,
• Supprimer l'ancienne saisie du moteur de recherche,
• Taper les trois premières lettres ext pour déclencher la saisie semi-automatique,
• Dans les propositions intuitives, cliquer sur la ligne extraction de donnee,

Les résultats apparaissent instantanément dans les cellules situées en dessous du moteur de recherche, en même temps que la zone de liste des suggestions diparaît.



Vous en conviendrez, la chaîne de texte qui était illisible et inexploitable en début de formation, est devenue limpide et explicite. Chaque résultat correspondant au terme de recherche, est effectivement restitué clairement, dans une cellule indépendante de la feuille.

Nous avons donc bien réussi à traiter l'information d'un fichier externe pour le nettoyer de toutes les occurrences qui ne convenaient pas. De plus, nous avons séparé l'information afin de la restituer de manière détachée et exploitable.

Ces trois volets nous ont donc permis de bâtir un moteur de recherche avec saisie semi-automatique complètement fonctionnelle, sur la base de fichiers de cache produits par un serveur, à partir desquels il s'est agi d'extraire et de consolider l'information. Le code Visual Basic permet donc des prouesses pour concevoir des applications professionnelles d'entreprises, sans limites. Et les expressions régulières ouvrent de nouvelles voies, au travers de la puissance qu'elles ont démontré pour traiter l'information textuelle.