{"id":10302,"date":"2022-08-01T05:19:55","date_gmt":"2022-08-01T03:19:55","guid":{"rendered":"https:\/\/www.auditsi.eu\/?p=10302"},"modified":"2022-07-31T16:09:41","modified_gmt":"2022-07-31T14:09:41","slug":"excel-automatiser-lextraction-des-tableaux-de-donnees-dun-fichier-pdf","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=10302","title":{"rendered":"Excel : automatiser l’extraction des tableaux de donn\u00e9es d’un fichier PDF"},"content":{"rendered":"

L’automatisation<\/strong> des t\u00e2ches est dans l’air du temps ; surtout lorsqu’il s’agit de t\u00e2ches r\u00e9p\u00e9titives<\/strong> d\u00e9pourvues de valeur ajout\u00e9e. Il en est ainsi de t\u00e2ches d’extraction \/ importation de donn\u00e9es effectu\u00e9es de mani\u00e8re r\u00e9currente.<\/p>\n

\"PQ<\/p>\n

L’expos\u00e9 ci-apr\u00e8s sera illustr\u00e9 par un exemple tr\u00e8s concret : l’extraction de statistiques mensuelles des immatriculations de v\u00e9hicules publi\u00e9 par la PFA<\/a>. Dans un pr\u00e9c\u00e9dent article, l’extraction des statistiques \u00e9tait effectu\u00e9e manuellement<\/a>. Dans cet article, cette extraction sera automatis\u00e9e \u00e0 l’aide d’un programme original<\/strong>.<\/p>\n

Si le langage VBA est le langage de pr\u00e9dilection pour automatiser l’ex\u00e9cution de t\u00e2ches<\/strong>, le langage M<\/strong> est, lui, le langage d’extraction et de manipulation de donn\u00e9es<\/strong> par excellence. L’association de ces deux langages permet de b\u00e9n\u00e9ficier de leurs capacit\u00e9s respectives<\/strong>.<\/p>\n

Fonctionnement du programme (code-source d\u00e9taill\u00e9 plus loin) :<\/em><\/p>\n

Pour lancer le programme<\/strong>, ouvrir le classeur intitul\u00e9 “Automatisation import tableaux PDF.xlsm” t\u00e9l\u00e9chargeable au bas de cet article et cliquer sur le bouton “Importer tableaux PFA”<\/strong>.<\/p>\n

\"Bouton<\/p>\n

Le programme demande \u00e0 l’utilisateur de s\u00e9lectionner le fichier PDF<\/strong> (\u00e0 obtenir sur le site du PFA<\/a> ou \u00e0 t\u00e9l\u00e9charger en bas de page) \u00e0 traiter (instruction .GetOpenFileName<\/em>). Ensuite, cliquer sur le bouton Ouvrir. Le nom du fichier (et son chemin d’acc\u00e8s) est stock\u00e9 dans la variable FichierPDF<\/em>.<\/p>\n

\"PQ<\/p>\n

Ceci fait, la boucle compteur<\/strong> (For.. To… Next…<\/em>)<\/a> traite chacun des trois tableaux de donn\u00e9es d\u00e9sign\u00e9s successivement (\u00e0 l’aide de la structure conditionnelle<\/strong> Select Case… End Select<\/em><\/a>) par la variable TableEnCours<\/em>.<\/p>\n

Select Case i\r\n    Case 1:\r\n        TableEnCours = \"Table003 (Page 2)\"\r\n        SourceEnCours = \"Table003\"\r\n    Case 2:\r\n        TableEnCours = \"Table005 (Page 3)\"\r\n        SourceEnCours = \"Table005\"\r\n    Case 3:\r\n        TableEnCours = \"Table007 (Page 4)\"\r\n        SourceEnCours = \"Table007\"\r\nEnd Select<\/pre>\n
Ensuite, le programme cr\u00e9e la requ\u00eate<\/strong> (.Queries.Add Name:=”” & TableEnCours<\/em>) interrogeant le fichier PDF. La formule de la requ\u00eate est introduite par la commande “Formula:=<\/em>“. La formule est r\u00e9dig\u00e9e en langage M<\/a><\/strong>.<\/p>\n
Le langage M est le langage de manipulation de donn\u00e9es impl\u00e9ment\u00e9 par Microsoft sur le module ETL<\/strong> (Extract-Transform-Load<\/em>) Power Query d’Excel<\/strong>. Power Query permet \u00e0 Excel de se connecter \u00e0 des sources de donn\u00e9es vari\u00e9es (bases de donn\u00e9es, classeurs Excel, fichiers ASCII \/ CSV, fichiers PDF…) et de mettre en forme les donn\u00e9es pour r\u00e9pondre aux besoins de l’utilisateur. Un exemple de connexion \u00e0 un fichier ASCII (en l’occurrence un FEC) est fourni dans l’article Analyser un FEC de plus d\u2019un million de lignes dans Excel<\/a>.<\/p>\n
Un programme \u00e9crit en VBA est en mesure de prendre le contr\u00f4le de Power Query<\/strong> en vue d’automatiser des requ\u00eates r\u00e9dig\u00e9es en langage M<\/strong>. C’est pr\u00e9cis\u00e9ment ce que r\u00e9alise le programme objet de cet article.<\/p>\n
Requ\u00eate en langage M :<\/em><\/p>\n
La requ\u00eate r\u00e9dig\u00e9e en Langage M est appel\u00e9e par le programme \u00e9crit, lui, en langage VBA, ce qui appelle quelques commentaires.<\/p>\n
Le texte de la requ\u00eate est le suivant :<\/p>\n
let\r\n    Source = Pdf.Tables(File.Contents(\"C:\\Users\\COLDIR01\\Downloads\\Flash-Marche-publication-mensuelle-PFA-0522.pdf\"), [Implementation=\"1.3\"]),\r\n    TableDonn\u00e9es = Source{[Id=\"Table003\"]}[Data],\r\n    #\"En-t\u00eates promus\" = Table.PromoteHeaders(TableDonn\u00e9es, [PromoteAllScalars=true]),\r\n    #\"Type modifi\u00e9\" = Table.TransformColumnTypes(#\"En-t\u00eates promus\",{{\"Marque\", type text}, {\"Volume\", Int64.Type}, {\"%\", type number}, {\"Volume_1\", Int64.Type}, {\"%_2\", type number}, {\"%Var\", type number}, {\"Volume_3\", Int64.Type}, {\"%_4\", type number}, {\"Volume_5\", Int64.Type}, {\"%_6\", type number}, {\"%Var_7\", type number}}),\r\n    #\"Erreurs supprim\u00e9es\" = Table.RemoveRowsWithErrors(#\"Type modifi\u00e9\", {\"Volume\"})\r\nin\r\n    #\"Erreurs supprim\u00e9es\"<\/pre>\n
La requ\u00eate est introduite par l’instruction “Let<\/em>“<\/strong> ; elle est suivie de la d\u00e9finition de la source de donn\u00e9es<\/strong> puis de la liste des champs de donn\u00e9es<\/strong> \u00e0 reprendre et, le cas \u00e9ch\u00e9ant, des transformations de donn\u00e9es<\/strong> (cr\u00e9ation de champs calcul\u00e9s, d\u00e9finition des formats…).<\/p>\n
let  \r\n   Variablename = expression,  \r\n   #\"Variable name\" = expression2  \r\nin   \r\n   Variablename<\/pre>\n
La source de donn\u00e9es est d\u00e9sign\u00e9e ici par “Source =” suivi du type de source (ici PDF : Pdf.Tables<\/em>) puis de la localisation de la source. Table.PromoteHeaders<\/em><\/a> indique que la premi\u00e8re ligne du tableau correspond \u00e0 l’ent\u00eate de colonne (nom du champ de donn\u00e9es).<\/p>\n
Le commande .TransformColumnTypes<\/em><\/a> pr\u00e9cise le type de donn\u00e9es<\/strong> de chaque champ de donn\u00e9es. Type text<\/em> pr\u00e9cise que le champ de donn\u00e9es est de type alphanum\u00e9rique<\/strong>, c’est-\u00e0-dire qu’il contiendra indiff\u00e9remment du texte, des chiffres, des symboles… Type number<\/em> sp\u00e9cifie un nombre r\u00e9el (avec des d\u00e9cimales)<\/strong> alors que Int64.type<\/em> sp\u00e9cifie un nombre entier (donc sans d\u00e9cimales)<\/strong>. Si le type de donn\u00e9es sp\u00e9cifi\u00e9 est num\u00e9rique (entier ou r\u00e9el) et que le contenu du champ est alphanum\u00e9rique, un message d’erreur appara\u00eetra (“Error”) :<\/p>\n
\"PQ<\/p>\n
La commande .RemoveRowsWithErrors<\/em> supprime les enregistrements (lignes) pour lesquelles le champ de donn\u00e9es “Volume” g\u00e9n\u00e8re une erreur (car non num\u00e9rique). Dans le cas pr\u00e9sent, le tableau de donn\u00e9es int\u00e8gre inutilement la derni\u00e8re ligne citant la source AAA DATA :<\/p>\n
\"Source<\/p>\n
Cette m\u00eame requ\u00eate est repr\u00e9sent\u00e9e sous cette forme dans l’\u00e9diteur avanc\u00e9 de Power Query (accessible \u00e0 partir du classeur Excel g\u00e9n\u00e9r\u00e9 par le code VBA) :<\/p>\n
\"PQ<\/p>\n
Requ\u00eate en langage M int\u00e9gr\u00e9e au code VBA :<\/em><\/p>\n
Le programme VBA automatise la g\u00e9n\u00e9ration des requ\u00eates en langage M<\/strong> avec la commande .Queries.Add<\/em>.  La r\u00e9daction de la requ\u00eate fait l’objet de quelques am\u00e9nagements afin d’\u00eatre intelligible par le code VBA.<\/p>\n
Tout d’abord, pour le code VBA la requ\u00eate en langage M n’a pas de signification particuli\u00e8re. Aussi cette requ\u00eate, identifi\u00e9e par la commande “Formula =<\/em>“, est-elle stock\u00e9e sous la forme d’une simple cha\u00eene de caract\u00e8res. Les guillemets servent \u00e0 d\u00e9limiter des cha\u00eenes de caract\u00e8res<\/strong> tant en Langage M qu’en VBA, il est n\u00e9cessaire d’ajouter un guillemet pour chaque s\u00e9rie de guillemets pr\u00e9sente dans la requ\u00eate M.<\/p>\n
Ensuite, pour que le programme VBA cr\u00e9\u00e9 des requ\u00eates personnalis\u00e9es \u00e0 la vol\u00e9e, il est n\u00e9cessaire de remplacer le nom des sources de donn\u00e9es et des tables de donn\u00e9es initialement d\u00e9finies en dur (Table003, Table005…) par des variables. Une variable correspond \u00e0 un emplacement m\u00e9moire qui stocke des donn\u00e9es<\/strong>, des r\u00e9sultats de calculs\u2026 La valeur prise par une variable peut \u00eatre chang\u00e9e \u00e0 loisir durant l’ex\u00e9cution du programme. C’est cette possibilit\u00e9 qui est mise \u00e0 profit ici pour cr\u00e9er trois requ\u00eates semblables \u00e0 l’aide d’une seule ligne de code.<\/p>\n
Requ\u00eate en langage M int\u00e9gr\u00e9e dans le programme VBA :<\/p>\n
ActiveWorkbook.Queries.Add Name:=\"\" & TableEnCours & \"\", Formula:= _\r\n    \"let\" & Chr(13) & \"\" & Chr(10) & _\r\n    \"    Source = Pdf.Tables(File.Contents(\"\"\" & FichierPDF & \"\"\"), [Implementation=\"\"1.3\"\"]),\" & Chr(13) & \"\" & Chr(10) & _\r\n    \"    TableDonn\u00e9es = Source{[Id=\"\"Table003\"\"]}[Data],\" & Chr(13) & \"\" & Chr(10) & _\r\n    \"    #\"\"En-t\u00eates promus\"\" = Table.PromoteHeaders(TableDonn\u00e9es, [PromoteAllScalars=true]),\" & Chr(13) & \"\" & Chr(10) & _\r\n    \"    #\"\"Type modifi\u00e9\"\" = Table.TransformColumnTypes(#\"\"En-t\u00eates promus\"\",{{\"\"Marque\"\", type text}, {\"\"Volume\"\", Int64.Type}, {\"\"%\"\", type number}, {\"\"Volume_1\"\", Int64.Type}, {\"\"%_2\"\", type number}, {\"\"%Var\"\", type number}, {\"\"Volume_3\"\", Int64.Type}, {\"\"%_4\"\", type number}, {\"\"Volume_5\"\", Int64.Type}, {\"\"%_6\"\", type number}, {\"\"%Var_7\"\", type number}}),\" & Chr(13) & \"\" & Chr(10) & _\r\n    \"    #\"\"Erreurs supprim\u00e9es\"\" = Table.RemoveRowsWithErrors(#\"\"Type modifi\u00e9\"\", {\"\"Volume\"\"})\" & Chr(13) & \"\" & Chr(10) & _\r\n    \"in\" & Chr(13) & \"\" & Chr(10) & _\r\n    \"    #\"\"Erreurs supprim\u00e9es\"\"\"\r\n<\/pre>\n
Cette requ\u00eate est cr\u00e9\u00e9e \u00e0 trois reprises (du fait de la boucle compteur For… To… Next<\/em>) pour r\u00e9cup\u00e9rer chacune des trois tables d\u00e9sign\u00e9es tour \u00e0 tour.<\/p>\n
Une fois ces trois tables int\u00e9gr\u00e9es, une derni\u00e8re requ\u00eate est r\u00e9dig\u00e9e (toujours en langage M) afin de fusionner les trois premi\u00e8res. Cette fusion est op\u00e9r\u00e9e \u00e0 l’aide de la commande .Combine<\/em><\/strong> suivie du nom des tables \u00e0 fusionner :<\/p>\n
let\r\n    \/\/Fusion des trois tables pr\u00e9c\u00e9demment import\u00e9es\r\n    Source = Table.Combine({#\"Table003 (Page 2)\", #\"Table005 (Page 3)\", #\"Table007 (Page 4)\"})\r\nin\r\n    Source<\/pre>\n
Le classeur Excel nouvellement cr\u00e9\u00e9 par le programme est maintenant \u00e0 disposition de l’utilisateur. Ses donn\u00e9es peuvent servir de base pour cr\u00e9er, par exemple, un tableau de bord (cf \u00e0 ce sujet les deux s\u00e9ries d’articles d\u00e9di\u00e9es \u00e0 ce sujet : tableaux de bord dynamiques<\/a> et tableaux de bord visuels<\/a>).<\/p>\n
Les requ\u00eates cr\u00e9\u00e9es par la macro VBA peuvent \u00eatre ouvertes par le lecteur pour analyse. Pour ce faire, dans le classeur cr\u00e9\u00e9 par le programme VBA, se rendre dans le menu Donn\u00e9es puis Requ\u00eates et connexions<\/strong> :<\/p>\n
\"Excel<\/p>\n
Les requ\u00eates disponibles s’affichent \u00e0 droite de l’\u00e9cran :<\/p>\n
\"Excel<\/p>\n
Pour visualiser le contenu de la requ\u00eate<\/strong> : faire un clic droit sur une des requ\u00eates puis s\u00e9lectionner Modifier dans le menu contextuel. Power Query, le module ETL d’Excel s’ouvre alors et affiche le r\u00e9sultat de la requ\u00eate (ici avec la requ\u00eate ToutesTables) :<\/p>\n
\"PQ<\/p>\n
Pour visualiser le texte de la requ\u00eate<\/strong> (et \u00e9ventuellement l’amender), cliquer sur Editeur avanc\u00e9<\/strong> (menu Accueil) :<\/p>\n
\"PQ<\/p>\n
Le code source de la macro VBA est retranscrit in extenso ci-apr\u00e8s :<\/em><\/p>\n
'---------------------------------------------------------------------------------------\r\n'Automatisation de la r\u00e9cup\u00e9ration des tables chiffr\u00e9es des \u00e9tats d'immatriculations PFA\r\n'Ecrit par Beno\u00eet RIVIERE 07\/2022\r\n'---------------------------------------------------------------------------------------\r\n\r\n\r\nOption Explicit\r\n\r\n\r\nSub ImportTablesDonn\u00e9esPFA()\r\n    'D\u00e9finition des variables\r\n    '---Nom du fichier PDF \u00e0 traiter (comprend le chemin d'acc\u00e8s)\r\n    Dim FichierPDF As Variant\r\n    '---Variable boucle compteur\r\n    Dim i As Integer\r\n    '---Nom de la table de donn\u00e9es en cours de traitement\r\n    Dim TableEnCours As String\r\n    '---Source en cours de traitement\r\n    Dim SourceEnCours As String\r\n    '---Variable tableau contenant le nom des tables de donn\u00e9es\r\n    Dim Tables(3) As String\r\n        \r\n    'Collecte du nom du fichier PDF \u00e0 traiter\r\n    FichierPDF = Application.GetOpenFilename(\"Fichiers PDF (*.pdf),*.pdf,Tous les fichiers (*.*),*.* \", 1, \"S\u00e9lectionnez le fichier PDF \u00e0 traiter\", , False)\r\n    '---Si le traitement est annul\u00e9 par l'utilisateur (bouton annul\u00e9, touche Echap...) -> arr\u00eat du programme\r\n    If FichierPDF = False Then Exit Sub\r\n    \r\n    'Cr\u00e9ation d'un nouveau classeur Excel\r\n    Workbooks.Add\r\n    \r\n    'Boucle de traitement des tables de dnn\u00e9es PDF\r\n    '---3 tabes de donn\u00e9es \u00e0 traiter\r\n    For i = 1 To 3\r\n        'En fonction de la position du compteur (1, 2 ou 3), d\u00e9finition du tableau de donn\u00e9es \u00e0 importer\r\n        Select Case i\r\n            Case 1:\r\n                TableEnCours = \"Table003 (Page 2)\"\r\n                SourceEnCours = \"Table003\"\r\n            Case 2:\r\n                TableEnCours = \"Table005 (Page 3)\"\r\n                SourceEnCours = \"Table005\"\r\n            Case 3:\r\n                TableEnCours = \"Table007 (Page 4)\"\r\n                SourceEnCours = \"Table007\"\r\n        End Select\r\n        Tables(i) = TableEnCours\r\n        \r\n        'Cr\u00e9ation de la requ\u00eate interrogeant le fichier PDF (formule r\u00e9dig\u00e9e en langage M)\r\n        ActiveWorkbook.Queries.Add Name:=\"\" & TableEnCours & \"\", Formula:= _\r\n            \"let\" & Chr(13) & \"\" & Chr(10) & _\r\n            \"    Source = Pdf.Tables(File.Contents(\"\"\" & FichierPDF & \"\"\"), [Implementation=\"\"1.3\"\"]),\" & Chr(13) & \"\" & Chr(10) & _\r\n            \"    TableDonn\u00e9es = Source{[Id=\"\"\" & SourceEnCours & \"\"\"]}[Data],\" & Chr(13) & \"\" & Chr(10) & _\r\n            \"    #\"\"En-t\u00eates promus\"\" = Table.PromoteHeaders(TableDonn\u00e9es, [PromoteAllScalars=true]),\" & Chr(13) & \"\" & Chr(10) & _\r\n            \"    #\"\"Type modifi\u00e9\"\" = Table.TransformColumnTypes(#\"\"En-t\u00eates promus\"\",{{\"\"Marque\"\", type text}, {\"\"Volume\"\", Int64.Type}, {\"\"%\"\", type number}, {\"\"Volume_1\"\", Int64.Type}, {\"\"%_2\"\", type number}, {\"\"%Var\"\", type number}, {\"\"Volume_3\"\", Int64.Type}, {\"\"%_4\"\", type number}, {\"\"Volume_5\"\", Int64.Type}, {\"\"%_6\"\", type number}, {\"\"%Var_7\"\", type number}}),\" & Chr(13) & \"\" & Chr(10) & _\r\n            \"    #\"\"Erreurs supprim\u00e9es\"\" = Table.RemoveRowsWithErrors(#\"\"Type modifi\u00e9\"\", {\"\"Volume\"\"})\" & Chr(13) & \"\" & Chr(10) & _\r\n            \"in\" & Chr(13) & \"\" & Chr(10) & _\r\n            \"    #\"\"Erreurs supprim\u00e9es\"\"\"\r\n    Next i\r\n    \r\n    'Cr\u00e9ation d'une requ\u00eate (intutul\u00e9e \"ToutesTables\") fusionnant le r\u00e9sultat des trois requ\u00eates pr\u00e9c\u00e9dentes (toujours en langeg M)\r\n    ActiveWorkbook.Queries.Add Name:=\"ToutesTables\", Formula:= _\r\n        \"let\" & Chr(13) & \"\" & Chr(10) & _\r\n        \"    Source = Table.Combine({#\"\"\" & Tables(1) & \"\"\", #\"\"\" & Tables(2) & \"\"\", #\"\"\" & Tables(3) & \"\"\"})\" & Chr(13) & \"\" & Chr(10) & _\r\n        \"in\" & Chr(13) & \"\" & Chr(10) & _\r\n        \"    Source\"\r\n    \r\n    'Cr\u00e9ation d'une nouvelle feuille de travail dans le classeur en curs\r\n    ActiveWorkbook.Worksheets.Add\r\n    \r\n    'Le r\u00e9sultat de la requ\u00eate \"ToutesTables\" est affich\u00e9 dans l'onglet nouvellement cr\u00e9\u00e9 sous la forme d'un tableau de donn\u00e9es\r\n    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _\r\n        \"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=ToutesTables;Extended Properties=\"\"\"\"\" _\r\n        , Destination:=Range(\"$A$1\")).QueryTable\r\n        .CommandType = xlCmdSql\r\n        .CommandText = Array(\"SELECT * FROM [ToutesTables]\")\r\n        .RowNumbers = False\r\n        .FillAdjacentFormulas = False\r\n        .PreserveFormatting = True\r\n        .RefreshOnFileOpen = False\r\n        .BackgroundQuery = True\r\n        .RefreshStyle = xlInsertDeleteCells\r\n        .SavePassword = False\r\n        .SaveData = True\r\n        .AdjustColumnWidth = True\r\n        .RefreshPeriod = 0\r\n        .PreserveColumnInfo = True\r\n        .ListObject.DisplayName = \"ToutesTables\"\r\n        .Refresh BackgroundQuery:=False\r\n    End With\r\nEnd Sub\r\n<\/pre>\n
Afin de s’exercer, le lecteur trouvera ci-apr\u00e8s en libre t\u00e9l\u00e9chargement<\/strong> le classeur Excel comprenant la macro VBA, les statistiques d’immatriculations de mai et juin 2022 (fichiers PDF) ainsi qu’un classeur Excel g\u00e9n\u00e9r\u00e9 par la macro VBA.<\/p>\n
Le lecteur souhaitant approfondir ses connaissances sur le langage M pourra consulter l’abondante documentation sur le site de Microsoft<\/a>.<\/p>\n
___<\/p>\n
En savoir plus sur l\u2019analyse de donn\u00e9es<\/a> \/ Programmer en VBA<\/a> \/ Ex\u00e9cuter des requ\u00eates SQL dans du code VBA<\/a><\/p>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
2.13.0.0<\/div>\n
\"Share<\/a>