Les fichiers PDF de sources diverses (factures, liasses fiscales, plaquettes des comptes annuels…) contiennent de nombreuses données. L’extraction de données de documents PDF est facilitée par le module Power Query d’Excel. Pour autant, la collecte de ces données n’est pas toujours très intuitive.
Cet article s’appuie sur un exemple concret. Un concessionnaire automobile est amené à réparer des véhicules de clients tombés en panne pendant la période de garantie constructeur. Dans ce cas le concessionnaire engage la prestation de réparation et dans un second temps le constructeur le rembourse du coût des pièces de rechange et de la main d’oeuvre. Les constructeurs Citroën et Peugeot (Stellantis) émettent à cette occasion des avis de crédit (ou avoirs) de garantie et communiquent à leurs concessionnaires tous les mois un état récapitulatif de l’ensemble des avis de crédit de la période au format PDF. C’est de cet état que je vous propose d’extraire les données pour reconstituer pour chaque opération de réparation le montant de la dépense dont le remboursement est attendu du constructeur.
La macro VBA présentée ci-après collectera puis listera dans un tableau Excel le numéro VIN (Vehicle Identification Number ou numéro d’identification du véhicule, composé de 17 caractères) du véhicule réparé ainsi que le coût de la réparation. Ces deux données sont repérées en jaune sur l’état ci-avant.
Cette collecte de données passe par la création de requêtes M pilotées par du code VBA.
Le code VBA de collecte de données issues d’un fichier PDF à l’aide de Power Query a déjà fait l’objet d’un exposé détaillé. Le lecteur désireux d’approfondir la partie VBA se reportera aux deux articles suivants :
- Excel : importer un tableau de données contenu dans un fichier PDF ;
- Excel : automatiser l’extraction des tableaux de données d’un fichier PDF.
L’exposé qui suit s’attardera sur la partie requête M (Power Query).
Le code VBA crée des requêtes en fonction des données de l’utilisateur (nom et chemin d’accès au fichier PDF) et des caractéristiques du fichier PDF (nombre de pages).
Il crée une requête par page importée. Exemple avec la première page (requête Page001) :
let
Source = Pdf.Tables(File.Contents("C:\TESTS\20231019-SAGL281 RECAPITULATIF_DES_AVIS_DE_CREDIT.pdf"), [Implementation="1.3"]),
Page1 = Source{[Id="Page001"]}[Data]
in
Page1
Puis, il crée une seconde requête combinant l’ensemble des pages (requête Pages_PDF), ici 34 pages :
let
Source = Table.Combine({Page001,Page002,Page003,Page004,Page005,Page006,Page007,Page008,Page009,Page010,Page011,Page012,Page013,Page014,Page015,Page016,Page017,Page018,Page019,Page020,Page021,Page022,Page023,Page024,Page025,Page026,Page027,Page028,Page029,Page030,Page031,Page032,Page033,Page034}),
#"Type modifié" = Table.TransformColumnTypes(Source,{{"Column4", type text}, {"Column7", type text}, {"Column12", type text}, {"Column16", type text}, {"Column17", type text}}),
#"Colonnes fusionnées" = Table.CombineColumns(Table.TransformColumnTypes(#"Type modifié", {{"Column7", type text}, {"Column12", type text}, {"Column16", type text}, {"Column17", type text}}, "fr-FR"),{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17"},Combiner.CombineTextByDelimiter("##", QuoteStyle.None),"Fusionné"),
#"Lignes filtrées" = Table.SelectRows(#"Colonnes fusionnées", each Text.Contains([Fusionné], "##VIN:") or Text.Contains([Fusionné], "!MT.INC:")),
#"Autres colonnes supprimées" = Table.SelectColumns(#"Lignes filtrées",{"Fusionné"}),
#"Duplication de la colonne" = Table.DuplicateColumn(#"Autres colonnes supprimées", "Fusionné", "Fusionné - Copier"),
#"Texte extrait entre les délimiteurs" = Table.TransformColumns(#"Duplication de la colonne", {{"Fusionné", each Text.BetweenDelimiters(_, "##VIN:", "##"), type text}}),
#"Texte extrait entre les délimiteurs1" = Table.TransformColumns(#"Texte extrait entre les délimiteurs", {{"Fusionné - Copier", each Text.BetweenDelimiters(_, "!MT.INC:####", " "), type text}}),
#"Type modifié1" = Table.TransformColumnTypes(#"Texte extrait entre les délimiteurs1",{{"Fusionné - Copier", type number}}),
#"Valeur remplacée" = Table.ReplaceValue(#"Type modifié1","",null,Replacer.ReplaceValue,{"Fusionné"}),
#"Rempli vers le bas" = Table.FillDown(#"Valeur remplacée",{"Fusionné"}),
#"Lignes filtrées1" = Table.SelectRows(#"Rempli vers le bas", each ([#"Fusionné - Copier"] <> null)),
#"Colonnes renommées" = Table.RenameColumns(#"Lignes filtrées1",{{"Fusionné", "VIN"}, {"Fusionné - Copier", "MT_INC"}})
in
#"Colonnes renommées"
Pas-à-pas de la requête Pages_PDF et visualisation de chaque étape sur les données dans le module Power Query :
- Source = Table.Combine : Combinaison de l’ensemble des pages
- #”Colonnes fusionnées” = Table.CombineColumns : fusion des champs de données en un seul (chaque champ est séparé par un double ##) pour faciliter l’extraction des données :
#”Lignes filtrées” = Table.SelectRows : cette ligne de la requête filtre sur toutes les lignes qui contiennent les identifiants des deux données recherchées “##VIN:” et “!MT.INC:“
- #”Autres colonnes supprimées” = Table.SelectColumns : supprime tous les champs (le cas échéant) autres que celui celui nommé Fusionné
- #”Duplication de la colonne” = Table.DuplicateColumn : cette ligne duplique le champ Fusionné :
- #”Texte extrait entre les délimiteurs” = Table.TransformColumns : cette ligne nettoie les données afin de n’obtenir que les numéros VIN et le montant des garanties
- #”Valeur remplacée” = Table.ReplaceValue : remplace les données vides par Null
- #”Rempli vers le bas” = Table.FillDown : permet de remplir le numéro VIN de haut en bas (en remplacement des valeurs Null)
- #”Lignes filtrées1″ = Table.SelectRows : supprime toutes les lignes pour lesquelles le montant est Null
- #”Colonnes renommées” = Table.RenameColumns : renomme les deux champs en VIN et MT_INC
Le résultat de tous ces calculs est retranscrit dans un tableau Excel : 128 avoirs pour un total de 51 030,37 € :
Ce qui correspond bien à la dernière page du fichier PDF (synthèse des pages précédentes) :
Le classeur Excel généré par la macro est à enregistrer sous le nom de votre choix…
Code source intégral :
Le code source comprend des commentaires expliquant son fonctionnement.
'---------------------------------------------------------------------------------------
'PROGRAMME DE TRAITEMENT DES RECAPITULATIFS MENSUELS DES AVIS DE CREDIT
'Un avis de crédit (ou avoir) de garantie est émis par les constructeurs Citroën ou Peugeot
'pour rembourser le concessionnaire qui a réparé un véhicule d'un client qui a connu une
'panne pendant la période de garantie
'Le constructeur envoie tous les mois au concessionnaire un état récapitulatif de tous les
'avoirs de la période
'Cette macro récupère le numéro VIN et le montant de la garantie associée (MO+PR) dans un
'tableau Excel
'
'Ecrit par Benoît RIVIERE 11/2023 (sauf code tiers)
'Plus d'infos : https://www.auditsi.eu/?p=11980
'---------------------------------------------------------------------------------------
Option Explicit
'---------------------------------------------------------------------------------------
'Cette fonction détermine le nombre de pages d'un fichier PDF
'Code tiers, source :
'https://excel-downloads.com/threads/vba-compter-le-nombre-de-page-dun-pdf-resolu.164261/
'---------------------------------------------------------------------------------------
Function GetPageNum(PDF_File As Variant)
'Haluk 19/10/2008
Dim FileNum As Long
Dim strRetVal As String
Dim RegExp
Set RegExp = CreateObject("VBscript.RegExp")
RegExp.Global = True
RegExp.Pattern = "/Type\s*/Page[^s]"
FileNum = FreeFile
Open PDF_File For Binary As #FileNum
strRetVal = Space(LOF(FileNum))
Get #FileNum, , strRetVal
Close #FileNum
GetPageNum = RegExp.Execute(strRetVal).Count
End Function
'---------------------------------------------------------------------------------------
'Cette routine combine toutes les pages du PDF sélectionné dans Power Query et en
'extrait les données VIN et montant de garantie
'puis les restituent dans un tableau Excel
'---------------------------------------------------------------------------------------
Sub PQ_PDF_RECAP_AVIS_CREDIT()
'Déclaration des variables
Dim NbPages As Integer
Dim FichierPDF As Variant
Dim i As Integer
Dim Formule As String
'Sélection du fichier PDF à traiter (cf https://www.auditsi.eu/?p=6456)
FichierPDF = Application.GetOpenFilename("Fichiers PDF (*.pdf),*.pdf,Tous les fichiers (*.*),*.* ", 1, "Sélectionnez le récapitulatif des avis de crédit à traiter", , False)
'---Arrête la routine si l'utilise ne sélectionne aucun fichier (touche Echap ou bouton Annuler)
If FichierPDF = False Then Exit Sub
'Crée un nouveau classeur Excel pour accueillir les données
Workbooks.Add
'Détermine le nombre de pages du fichier PDF
'et retire 1 : la dernière est une page qui totalise tous les avis de crédit du mois et qui ne contient pas les données recherchées
NbPages = GetPageNum(FichierPDF) - 1
'POWER QUERY :
'1. Intégration de toutes les pages du PDF (boucle i=1 To NbPages...)
For i = 1 To NbPages
'La variable Formule calcule à la volée la requête M permettant d'intégrer chaque page du PDF dans Power Query
'---Ouverture de la requête
Formule = "let" & Chr(13) & "" & Chr(10)
'---Source des données = FichierPDF...
Formule = Formule & " Source = Pdf.Tables(File.Contents(""" & FichierPDF & """), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10)
'---... et page numérotée i
Formule = Formule & " Page" & i & " = Source{[Id=""Page" & Format(i, "000") & """]}[Data]" & Chr(13) & "" & Chr(10)
Formule = Formule & "in" & Chr(13) & "" & Chr(10)
Formule = Formule & " Page" & i
'Crée une requête pour chacune des pages
ActiveWorkbook.Queries.Add Name:="Page" & Format(i, "000"), Formula:=Formule
Next i
'2. Combinaison de toutes les pages en une seule requête
'Création du texte de la requête
Formule = "let" & Chr(13) & "" & Chr(10)
Formule = Formule & " Source = Table.Combine({"
'---Boucle successivement sur chacune des pages détectées
For i = 1 To NbPages
Formule = Formule & "Page" & Format(i, "000")
'Chaque Page est séparée des autres par une virgule
If i <> NbPages Then Formule = Formule & ","
Next i
Formule = Formule & "})," & Chr(13) & "" & Chr(10)
'---Modification du format des champs de données : texte
Formule = Formule & " #""Type modifié"" = Table.TransformColumnTypes(Source,{{""Column4"", type text}, {""Column7"", type text}, {""Column12"", type text}, {""Column16"", type text}, {""Column17"", type text}})," & Chr(13) & "" & Chr(10)
'---Tous les champs de données sont fusionnés en un seul et délimités les uns des autres par ##
Formule = Formule & " #""Colonnes fusionnées"" = Table.CombineColumns(Table.TransformColumnTypes(#""Type modifié"", {{""Column7"", type text}, {""Column12"", type text}, {""Column16"", type text}, {""Column17"", type text}}, ""fr-FR""),{""Column1"", ""Column2"", ""Column3"", ""Column4"", ""Column5"", ""Column6"", ""Column7"", ""Column8"", ""Column9"", ""Column10"", ""Column11"", ""Column12"", ""Column13"", ""Column14"", ""Column15"", ""Column16"", ""Column17""},Combiner.CombineTextByDelimiter(""##"", QuoteStyle.None),""Fusionné"")," & Chr(13) & "" & Chr(10)
'---Filtre les lignes contenant le n° VIN (libellé ##VIN:) ou le montant total (libellé !MT.INC:)
Formule = Formule & " #""Lignes filtrées"" = Table.SelectRows(#""Colonnes fusionnées"", each Text.Contains([Fusionné], ""##VIN:"") or Text.Contains([Fusionné], ""!MT.INC:""))," & Chr(13) & "" & Chr(10)
'---Le cas échéant supprimes les autres champs
Formule = Formule & " #""Autres colonnes supprimées"" = Table.SelectColumns(#""Lignes filtrées"",{""Fusionné""})," & Chr(13) & "" & Chr(10)
'---Duplique la colonne
Formule = Formule & " #""Duplication de la colonne"" = Table.DuplicateColumn(#""Autres colonnes supprimées"", ""Fusionné"", ""Fusionné - Copier"")," & Chr(13) & "" & Chr(10)
'---Extrait le numéro VIN (donnée délimitée par ##VIN: et ##)
Formule = Formule & " #""Texte extrait entre les délimiteurs"" = Table.TransformColumns(#""Duplication de la colonne"", {{""Fusionné"", each Text.BetweenDelimiters(_, ""##VIN:"", ""##""), type text}})," & Chr(13) & "" & Chr(10)
'---Extrait le montant (donnée délimitée par !MT.INC:#### et Espace)
Formule = Formule & " #""Texte extrait entre les délimiteurs1"" = Table.TransformColumns(#""Texte extrait entre les délimiteurs"", {{""Fusionné - Copier"", each Text.BetweenDelimiters(_, ""!MT.INC:####"", "" ""), type text}})," & Chr(13) & "" & Chr(10)
'---Convertit le champ montant en un nombre décimal
Formule = Formule & " #""Type modifié1"" = Table.TransformColumnTypes(#""Texte extrait entre les délimiteurs1"",{{""Fusionné - Copier"", type number}})," & Chr(13) & "" & Chr(10)
'---Remplace les données manquantes par Null
Formule = Formule & " #""Valeur remplacée"" = Table.ReplaceValue(#""Type modifié1"","""",null,Replacer.ReplaceValue,{""Fusionné""})," & Chr(13) & "" & Chr(10)
'---Remplit les données vers le bas
Formule = Formule & " #""Rempli vers le bas"" = Table.FillDown(#""Valeur remplacée"",{""Fusionné""})," & Chr(13) & "" & Chr(10)
'---Supprime les lignes dont le montant est Null
Formule = Formule & " #""Lignes filtrées1"" = Table.SelectRows(#""Rempli vers le bas"", each ([#""Fusionné - Copier""] <> null))," & Chr(13) & "" & Chr(10)
'---Renomme les deux champs en VIN et MT_INC
Formule = Formule & " #""Colonnes renommées"" = Table.RenameColumns(#""Lignes filtrées1"",{{""Fusionné"", ""VIN""}, {""Fusionné - Copier"", ""MT_INC""}})" & Chr(13) & "" & Chr(10)
'---Clôture la requête M
Formule = Formule & "in" & Chr(13) & "" & Chr(10)
Formule = Formule & " #""Colonnes renommées"""
'Ajoute la requête
ActiveWorkbook.Queries.Add Name:="Pages_PDF", Formula:=Formule
'3. Crée le tabeau Excel à partir du résultat de la requête M Pages_PDF
Application.CutCopyMode = False
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Pages_PDF;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Pages_PDF]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Pages_PDF"
.Refresh BackgroundQuery:=False
End With
End Sub
___
Approfondir le sujet : extraire des tableaux de données depuis une photo / importer de données d’un fichier ASCII / CSV (exemple avec un FEC) / En savoir plus sur l’analyse de données
The following two tabs change content below.
Après seize années passées en cabinet d’expertise-comptable et de commissariat aux comptes (où j’ai exercé comme expert-comptable et chef de mission audit), j’ai pris le poste de directeur comptable d’un groupe de distribution automobile en novembre 2014. Au cours de ma carrière, j’ai acquis une expérience significative en audit et en exploitation des systèmes d’information (analyse de données, automatisation des tâches, programmation informatique) au service de la production des comptes annuels et consolidés. C’est cette expérience personnelle et ma passion pour l’informatique que je partage sur ce blog.
Mon CV / Réalisations personnelles et projets informatiques / Ma collection / Me contacter
Derniers articles parBenoît RIVIERE (voir tous)
- Analyse de données & FEC provisoire : créer des numéros d’écriture avec un script Python - samedi 27 avril 2024
- Excel : collecter des données juridiques sur les entreprises avec l’API SIRENE – 1ère partie : base SIREN (entreprises) - lundi 15 avril 2024
- Obtenir les données financières de sociétés au format Excel - mardi 2 avril 2024
- Robotic Process Automation (RPA) et Python : piloter des applications web - dimanche 24 mars 2024
- Initiation à la programmation en Basic : nouveau compilateur FreeBASIC - lundi 18 mars 2024