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.

• Bio
• Derniers articles

Benoît RIVIERE

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)

• Nouveautés de l’interpréteur de formules de calcul (v1.1) - dimanche 3 novembre 2024
• Dématérialisation de la facturation : nouvelles mentions obligatoires - lundi 28 octobre 2024
• Interpréteur de formules de calcul en Python - dimanche 13 octobre 2024
• Les données de la facturation électronique - mercredi 9 octobre 2024
• VBA/SQL vs Power Query : deux solutions complémentaires - mercredi 2 octobre 2024