XL 2021 VBA et Power Query : ecrire des requetes avec variables

Florent74 · 29 Octobre 2024

Bonjour à tous,

Je cherche a importer par vba le contenu de fichiers pdf dans Excel. Le nombre de pages et le nombre de tableaux par pages étant variables. Il s'agit de résultats d'analyses d'eau potable. L'objectif est de pouvoir scinder le pdf contenant plusieurs analyses, chaque analyses faisant un nombre de pages variables, de scinder le pdf en autant de pdf qu'il y a d'analyses, et en les renommant selon plusieurs critères selon la date, le numéro de l'analyse et le point de prélèvement.

Je me suis orienté vers l'importation de sources de données avec Power Query. L'enregistreur de macro n'est pas satisfaisant car il écrit en dur chaque requetes selon le nombre de pages, le nombre de tableaux par page et le nombre de colonne par tableaux.

Je cherche à automatiser tout cela avec VBA mais je n'ai pas assez de connaissances en vba/Power Query pour adapter le code avec des boucles ou variables selon le nombre de pages, de tableaux et de colonnes par tableau. Et je trouve peux de ressource sur les différents forum à ce sujet.

Je dispose de la version d'Adobe. En pièce jointe, un exemple de fichier pdf (ici, le fichier ne comporte qu'une seule analyse sur une page, mais j'aimerais arriver à l'importer automatiquement par Power Query avec en résultat dans Excel, un onglet par page et tableaux).

Merci si vous arrivez à m'éclairer ou m'orienter sur une solution !!

Florent74 · 1 Décembre 2024

Bonjour à tous,

J'ai pas mal avancé pour l'intégration automatiques des fichiers pdf. Alors merci encore pour toutes vos contributions !! Je peux clore ce post !!

Florent74 · 31 Octobre 2024

jurassic pork à dit:
Hello,
Florent74 tu n'as pas demandé dans les informations à extraire la commune de prélèvement ce qui permet de repérer à quoi correspond une analyse.
J'ai légèrement modifié le code de mromain pour extraire cette info :
Pour le labo Lidal :

PowerQuery:

ExtractCommune = let txt = List.Select(BufferInfosPage1, each Text.Contains(_, "Commune* :")){0} in [Commune = Text.AfterDelimiter(txt, "Commune* : ")], ConcatResult = ExtractDatePrelevement & ExtractDateReception & ExtractRefEchantillon & ExtractTypeVisite & ExtractTypeAnalyse & ExtractCommune

Pour le labo Savoie Labo :

PowerQuery:

ExtractCommune = let txt = List.Select(BufferInfosPage1, each Text.Contains(_, "Commune :")){0} in [Commune = Text.AfterDelimiter(txt, "/",1)], ConcatResult = ExtractDates & ExtractRefEchantillon & ExtractTypes & ExtractCommune

et voici ce que cela donne :
Regarde la pièce jointe 1206245

et comme le précise mromain il faut du code différent pour chaque type de labo car leurs PDF n'a pas la même structure. J'espère qu'il n'y a pas trop de labos différents sinon cela risque d'être difficile à gérer.

Pour l'extraction des résultats voici une requête PowerQuery qui fait le minimum :

PowerQuery:

let Source = Pdf.Tables(File.Contents("D:\Temp\E24.4967.pdf"), [Implementation="1.3"]), TableRes = Source{[Id="Table003"]}[Data], #"En-têtes promus" = Table.PromoteHeaders(TableRest, [PromoteAllScalars=true]), #"Colonnes renommées" = Table.RenameColumns(#"En-têtes promus",{{"Column1", "Séléction"}}) in #"Colonnes renommées"

Il faut choisir la table qui contient les résultats (ici la Table003)
Regarde la pièce jointe 1206246

Ami calmant, J.P

Merci Jurassik Pork !!

Heureusement, il n’y a que ces deux labos !!

Pour l’extraction des résultats, tu spécifies en dur dans la requete la Table003. Est-ce que, par rapport à la structure du pdf, le numéro de table qui contient les résultats est toujours le même ?

Je vais faire quelques essais de mon côté avec les exemples que vous m’avez fournis et reviendrais vers vous ensuite pour vous indiquez mes avancées !!

Merci encore !!

jurassic pork · 31 Octobre 2024

Florent74 à dit:
Merci Jurassik Pork !!
Heureusement, il n’y a que ces deux labos !!
Pour l’extraction des résultats, tu spécifies en dur dans la requete la Table003. Est-ce que, par rapport à la structure du pdf, le numéro de table qui contient les résultats est toujours le même ?

C'est surtout mromain qu'il faut remercier, moi je n'ai fait presque rien.
Pour l'extraction des résultats, mromain dans son code à repérer les pages et tables qui sont pour la même analyse (colonne Id_Analyse). Il suffit de repérer pour une Id_Analyse quelle est la table qui contient "Paramètres analytiques" ou "PARAMETRE ANALYTIQUE" et d'extraire les données de cette table. Je suis sûr que mromain ou chris ou une autre personne sera capable de faire ce traitement. Tu n'as pas besoin de connaître le nombre de pages car la table contient TOUS les résultats.
Ensuite en mettant tous les PDF dans un même répertoire on doit être capable de boucler sur l'extraction de tous les PDF. Mais il faut savoir si tu veux tous les résultats dans un même classeur.
Dans ce cas sur la première feuille, il y a les infos des analyses avec une colonne rajoutée avec un lien
vers l'onglet où se trouvent les extractions de résultats.
Tout cela je ne sais pas si c'est facilement faisable car je ne connais pas assez Power Query.

mromain · 31 Octobre 2024

Bonjour Florent74, chris, jurassic pork, le forum

Ci-joint une autre proposition (testée avec le fichier ExempleAnalyses.pdf fourni au post #13).
Il y a toujours le tableau existant listant les différentes analyses du fichier pdf.
Il y a en plus une seconde requête qui permet d'accéder au détail d'une analyse particulière à partir de l'ID_Analyse.
Le détail renvoyé est le suivant :

pour le labo SavoieLabo : le tableau dont la première cellule vaut PARAMETRE ANALYTIQUE ;
pour le labo Lidal : le tableau contenant une cellule avec µS/cm.

Une première colonne contenant le texte concaténé de la ligne est également rajoutée.

Il est toujours possible de piloter par VBA si tu veux traiter ensuite le détail de l'analyse :

VB:

Sub Test()
Dim pathPdf As Variant
Dim idAnalyse As Long
    'sélectionner le fichier
    pathPdf = Application.GetOpenFilename("Fichier pdf (*.pdf), *.pdf", , "Fichier analyse", , False)
    If pathPdf = False Then Exit Sub
    
    With Feuil1
        'charger la liste des analyses du fichier
        .Range("Rng_PathPdf").Value = pathPdf
        .ListObjects("Tab_ExtractAnalyses").Refresh
        'boucler sur chaque analyse
        For idAnalyse = 1 To .ListObjects("Tab_ExtractAnalyses").ListRows.Count
            'charger le détail de l'analyse
            .Range("Rng_IdAnalyse").Value = idAnalyse
            .ListObjects("Tab_DetailAnalyse").Refresh
            'effectuer le traitement sur le détail de l'analyse
            '...
            '...
            Application.Goto .ListObjects("Tab_DetailAnalyse").Range, True
            MsgBox "Détail de l'analyse " & idAnalyse & " sur " & .ListObjects("Tab_ExtractAnalyses").ListRows.Count, vbInformation, "Info"
        Next idAnalyse
    End With
End Sub

comment as tu crées la requete ? Ecrite à la main ? Dans ce cas, où puis-je trouver les ressources pour comprendre toute la syntaxe de la requete ?

Une bonne partie oui, depuis l'Éditeur avancé.
Pour les ressources, il y a l'ensemble de cours Power Query M Primer de Ben Gribaudo. C'est en anglais, mais ça permet de bien appréhender le langage M (types de données, syntaxe, ...). Ensuite, il y a la documentation des fonctions soit chez Microsoft, soit une autre version un peu plus agrémentée chez PQ HOW.

A+

jurassic pork · 31 Octobre 2024

En modifiant légèrement le code VBA de mromain, on peut créer dans le classeur une feuille pour chaque analyse (qui se nomme dans mon exemple Analyse <idAnalyse> :

VB:

Sub Test()
Dim pathPdf As Variant
Dim idAnalyse As Long
Dim ws As Worksheet
Dim linkCell As Range
    'sélectionner le fichier
    pathPdf = Application.GetOpenFilename("Fichier pdf (*.pdf), *.pdf", , "Fichier analyse", , False)
    If pathPdf = False Then Exit Sub
   
    With Feuil1
        'charger la liste des analyses du fichier
        .Range("Rng_PathPdf").Value = pathPdf
        .ListObjects("Tab_ExtractAnalyses").Refresh
        'boucler sur chaque analyse
        For idAnalyse = 1 To .ListObjects("Tab_ExtractAnalyses").ListRows.Count
            'charger le détail de l'analyse
            .Range("Rng_IdAnalyse").Value = idAnalyse
            .ListObjects("Tab_DetailAnalyse").Refresh
            'effectuer le traitement sur le détail de l'analyse
            '...
            '...
            .ListObjects("Tab_DetailAnalyse").Range.Copy
            'Application.Goto .ListObjects("Tab_DetailAnalyse").Range, True
            Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
            ws.Name = "Analyse " & CStr(idAnalyse)
            ws.Range("A1").Select
            ws.Paste
            Set linkCell = .ListObjects("Tab_ExtractAnalyses").ListColumns(1).DataBodyRange(idAnalyse)
            ActiveSheet.Hyperlinks.Add Anchor:=linkCell, _
                              Address:="", _
                              SubAddress:="'" & ws.Name & "'!A1", _
                              TextToDisplay:=ws.Name
            'MsgBox "Détail de l'analyse " & idAnalyse & " sur " & .ListObjects("Tab_ExtractAnalyses").ListRows.Count, vbInformation, "Info"
        Next idAnalyse
    End With
    MsgBox ("FIN")
End Sub

A noter que le code crée aussi un lien entre les id de la colonne ID_Analyse de la Feuil1 vers la Feuille correspondante contenant l'analyse.
Ami calmant, J.P

Florent74 · 2 Novembre 2024

Bonjour à tous !! Punaise les gars, vous êtes vraiment costauds et réactifs !!

Je découvre PQ depuis mercredi et c’est vraiment puissant. Je comprends mieux maintenant pourquoi Chris indiquait que le vba est utile "à la marge" dans mon cas. Je vais décortiquer toutes vos propositions pour bien les assimiler et me mettre dans le langage M pour comprendre tout ça.

Je reviens vers vous au plus vite dès que j’aurai digérer tout ça.

Je reviens vers vous au plus vite. Dors et déjà, je vous dis un grand merci !!

Florent74 · 2 Novembre 2024

mromain à dit:
Bonjour Florent74, chris, jurassic pork, le forum

Ci-joint une autre proposition (testée avec le fichier ExempleAnalyses.pdf fourni au post #13).
Il y a toujours le tableau existant listant les différentes analyses du fichier pdf.
Il y a en plus une seconde requête qui permet d'accéder au détail d'une analyse particulière à partir de l'ID_Analyse.
Le détail renvoyé est le suivant :

pour le labo SavoieLabo : le tableau dont la première cellule vaut PARAMETRE ANALYTIQUE ;

pour le labo Lidal : le tableau contenant une cellule avec µS/cm.

Une première colonne contenant le texte concaténé de la ligne est également rajoutée.

Il est toujours possible de piloter par VBA si tu veux traiter ensuite le détail de l'analyse :

VB:

Sub Test() Dim pathPdf As Variant Dim idAnalyse As Long 'sélectionner le fichier pathPdf = Application.GetOpenFilename("Fichier pdf (*.pdf), *.pdf", , "Fichier analyse", , False) If pathPdf = False Then Exit Sub With Feuil1 'charger la liste des analyses du fichier .Range("Rng_PathPdf").Value = pathPdf .ListObjects("Tab_ExtractAnalyses").Refresh 'boucler sur chaque analyse For idAnalyse = 1 To .ListObjects("Tab_ExtractAnalyses").ListRows.Count 'charger le détail de l'analyse .Range("Rng_IdAnalyse").Value = idAnalyse .ListObjects("Tab_DetailAnalyse").Refresh 'effectuer le traitement sur le détail de l'analyse '... '... Application.Goto .ListObjects("Tab_DetailAnalyse").Range, True MsgBox "Détail de l'analyse " & idAnalyse & " sur " & .ListObjects("Tab_ExtractAnalyses").ListRows.Count, vbInformation, "Info" Next idAnalyse End With End Sub

Une bonne partie oui, depuis l'Éditeur avancé.
Pour les ressources, il y a l'ensemble de cours Power Query M Primer de Ben Gribaudo. C'est en anglais, mais ça permet de bien appréhender le langage M (types de données, syntaxe, ...). Ensuite, il y a la documentation des fonctions soit chez Microsoft, soit une autre version un peu plus agrémentée chez PQ HOW.

A+

Merci pour les références des ressources, je démarre le cours de Ben Gribaudo !

Florent74 · 3 Novembre 2024

jurassic pork à dit:

En modifiant légèrement le code VBA de mromain, on peut créer dans le classeur une feuille pour chaque analyse (qui se nomme dans mon exemple Analyse <idAnalyse> :

VB:

Sub Test()
Dim pathPdf As Variant
Dim idAnalyse As Long
Dim ws As Worksheet
Dim linkCell As Range
    'sélectionner le fichier
    pathPdf = Application.GetOpenFilename("Fichier pdf (*.pdf), *.pdf", , "Fichier analyse", , False)
    If pathPdf = False Then Exit Sub
  
    With Feuil1
        'charger la liste des analyses du fichier
        .Range("Rng_PathPdf").Value = pathPdf
        .ListObjects("Tab_ExtractAnalyses").Refresh
        'boucler sur chaque analyse
        For idAnalyse = 1 To .ListObjects("Tab_ExtractAnalyses").ListRows.Count
            'charger le détail de l'analyse
            .Range("Rng_IdAnalyse").Value = idAnalyse
            .ListObjects("Tab_DetailAnalyse").Refresh
            'effectuer le traitement sur le détail de l'analyse
            '...
            '...
            .ListObjects("Tab_DetailAnalyse").Range.Copy
            'Application.Goto .ListObjects("Tab_DetailAnalyse").Range, True
            Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
            ws.Name = "Analyse " & CStr(idAnalyse)
            ws.Range("A1").Select
            ws.Paste
            Set linkCell = .ListObjects("Tab_ExtractAnalyses").ListColumns(1).DataBodyRange(idAnalyse)
            ActiveSheet.Hyperlinks.Add Anchor:=linkCell, _
                              Address:="", _
                              SubAddress:="'" & ws.Name & "'!A1", _
                              TextToDisplay:=ws.Name
            'MsgBox "Détail de l'analyse " & idAnalyse & " sur " & .ListObjects("Tab_ExtractAnalyses").ListRows.Count, vbInformation, "Info"
        Next idAnalyse
    End With
    MsgBox ("FIN")
End Sub

A noter que le code crée aussi un lien entre les id de la colonne ID_Analyse de la Feuil1 vers la Feuille correspondante contenant l'analyse.
Ami calmant, J.P

Bonjour Jurassik Pork !! Merci pour la boucle VBA, c’est parfait !! Je vais me concentrer maintenat sur les requêtes PQ qui sont hardues quand on en maîtrise pas le langage !!

Bonne journée !!

Florent74 · 3 Novembre 2024

Bonjour mromain, et bonjour aux autres contributeurs !!

Les choses commencent à s’éclaircir, merci !! La compréhension de la syntaxe des requêtes n’est pas évidente mais j’ai l’impression d’avoir tout ce qu’il faut pour résoudre mon problème !!

Après lecture d’une partie des références que tu m’as données et la documentation Microsoft, voici un certains nombres de questions sur les requêtes que tu as rédigées :

- le numéro d’identification de l’analyse est un identifiant unique, avec une nomenclature propre à chaque laboratoire . Pour le LIDAL, le numéro commence par la lettre "E" suivie de l’année et de deux chiffres, séparés par des points (ex : E23.279.1). Pour Savoie Labo, le numéro commence par les lettres "SLA" suivies de l’année et du mois de prélèvement et de deux numéros séparés par un tiret "-" (ex : SLA2303-3330-1) :

Pour une analyse LIDAL, et dans la requete permettant de remplir la colonne IdentificationEchantillon, comment est-ce qu’il est possible d’extraire uniquement le numéro de l’analyse sous la forme E23.279.1, et non sans forme complète E23.279.1_20230123153219 ? J’ai tenté avec l’instruction "BetweenDelimieters", mais comme il faut partir de la gauche (RelativePosition.FromEnd), ma syntaxe ne fonctionne pas …
Pour la requete détaillant le résultat de l’analyse, tu te bases sur l’unité de la mesure de conductivité (µS/cm) pour repérer une analyse LIDAL. Ce n’est pas garanti que la mesure de conductivité soit réalisée systématiquement et donc peut-être pas assez fiable pour identifier l’origine du laboratoire. Est-ce qu’il serait possible de se baser sur l’identification de l’échantillon : si le numéro d’échantillon commence par un "E" => labo LIDAL, si le numéro d’échantillon commence par SLA => labo Savoie-Labo ?

- dans l’instruction "dataPage1 = Table.First(Table.SelectRows(pdfAnalyse, each [Kind]="Page"))[Data], est-ce que tu peux m’expliquer le rôle du mot “Kind" ainsi que du [Data] à la fin ? Je n’arrive pas à trouver d’infos…

- je n’arrive pas à comprendre comment le nombre de pages par analyses est comptabilisé. Je comprends bien que l’info se trouve dans l’instruction "GroupByIdAnalyse = Table.Group(AddAnalyseGeneralInfo, …" mais je ne comprends pas l’instruction "List.Count(List.Select(_[Kind], (t) => t= "Page")). Est-ce que tu arriverais à me l’expliquer ?

Je vais continuer à bosser pour comprendre le langage mais tes explications sur les points ci-dessus me permettraient d’avancer un peu mieux.

En tout cas, pour l’instant, j’ai de quoi faire et le fichier me convient bien !!

Merci encore !!

jurassic pork · 4 Novembre 2024

Hello,
en attendant le retour de mromain , je vais essayer de répondre à certaine de tes demandes :
pour le problème du numéro d’identification de l’analyse tu peux faire comme ceci pour le Lidal :

PowerQuery:

ExtractRefEchantillon = let txt = List.Select(BufferInfosPage1, each Text.StartsWith(_, "RAPPORT D'ANALYSE Eaux")){0} in [IdentificationEchantillon = Text.BetweenDelimiters(txt, " ", "_",3)],

si ta ligne est toujours du type :

RAPPORT D'ANALYSE Eaux <un mot> Exxxxxx_yyyyyyyyyyy

on capture ce qui est entre le 3ème espace et le _
Pour le deuxième problème tu peux remplacer µS/cm par TERRAIN (ou un autre mot) si celui-ci est toujours présent dans la table qui contient les données (et pas ailleurs).
Je me suis demandé pourquoi mromain avait fait comme cela : c'est parce qu'il y a un souci dans des PDF comme le E23.279.1.pdf :
En effet dans celui-ci les données ne sont pas dans la table qui contient PARAMETRE ANALYTIQUE qui ne contient alors qu'une seule ligne mais dans une autre table (qu'on repère par le mot décrit précédemment).
L'autre souci c'est que par rapport aux autres PDF on perd donc la ligne avec PARAMETRE ANALYTIQUE mais aussi la ligne avec BACTÉRIOLOGIQUE. J'espère que ce n'est pas gênant sinon il faut la recréer.
Dans les autres PDF on a tout dans une seule table.
D'autre part il y a un souci avec mon code VBA (message #19 ) qui crée des feuilles aves les résultats :
des connexions PQ sont créées. Si quelqu'un peut corriger ce problème.
Ami calmant, J.P

mromain · 5 Novembre 2024

Bonjour à tous,

@Florent74 :

Voici quelques réponses :

Pour extraire la référence du labo LIDAL :
Il te faut extraire la référence à l'aide de la fonction Text.BeforeDelimiter (aide).
Voir l'exemple 1 de la requête ci-dessous.
Pour le fait d'utiliser la mesure de conductivité (µS/cm) pour repérer une analyse LIDAL :
Comme le disait @jurassic pork, j'ai opté pour cette solution à cause de la structure du fichier E23.279.1.pdf. On peut tout à fait choisir une autre méthode pour identifier la table de résultat d'analyse.
Pour la partie suivante :

Est-ce qu'il serait possible de se baser sur l'identification de l'échantillon : si le numéro d'échantillon commence par un "E" => labo LIDAL, si le numéro d'échantillon commence par SLA => labo Savoie-Labo ?

Cliquez pour agrandir...

Je ne comprends pas trop ce que tu veux dire...
Dans les exemples précédents, on identifie le labo en cherchant le texte Page X sur X en bas de page. Si on le trouve, alors il s'agit du labo LIDAL, sinon du labo Savoie-Labo.
Ensuite, en fonction du labo, on récupère l'identification de l'échantillon.
Pour la partie suivante :

dans l'instruction "dataPage1 = Table.First(Table.SelectRows(pdfAnalyse, each [Kind]="Page"))[Data], est-ce que tu peux m'expliquer le rôle du mot “Kind" ainsi que du [Data] à la fin ? Je n'arrive pas à trouver d'infos…

Cliquez pour agrandir...

Voir l'exemple 2 de la requête ci-dessous qui détaille ce code.
- Le mot Kind sert choisir sur quelle colonne on filtre la table pdfAnalyse.
  Voir l'étape SelectKindPage de l'exemple.
- Le mot [Data] sert à choisir quel champ du record on veut.
  Voir l'étape SelectData de l'exemple.
Pour l'identification du nombre de pages de l'analyse.
Voir l'exemple 3 de la requête ci-dessous qui détaille ce code.
- _ : dans le contexte du Table.Group(AddAnalysesGeneralInfos, {"ID_Analyse", "Labo"}, ..., le _ représente la table de données pour un couple ID_Analyse / Labo donné.
- _[Kind] correspond à la sélection de la colonne Kind.
  Voir l'étape SelectColumnKind de l'exemple.
- List.Select(..., (t) => t= "Page") permet de filtrer la liste précédente pour ne sélectionner que les éléments valant Page.
  Voir l'étape SelectPages de l'exemple.
- List.Count(...) permet de compter le nombre d'éléments et donc de renvoyer le nombre de pages.
  Voir l'étape CountItems de l'exemple.

PowerQuery:

let
    // Exemple 1
    ExtractRef = Text.BeforeDelimiter("E24.4967.1_20241021112206", "_"),

    // Exemple 2
    pdfAnalyse = Table.AddColumn(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdE9CoAwDAXgq5ROCgpN/2xv4eBWHBTExfvPItrXYsQt5HvDI0lJjsu+KUWye01y7pKclvV4dnkUzaWC2ryrg7oE9W/QlKD5Dt5tNHrpute9MkDD0QItRwd0HD3QcxyAA8cADBwjMDIklRFThXgOfT3HllPafMqe6mvOJw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Name = _t, Kind = _t]), "Data", each #table ({"Column1"}, {{"DataTest"}}), type table),
    SelectKindPage = Table.SelectRows(pdfAnalyse, each ([Kind] = "Page")),
    SelectFirstRecord = Table.First(SelectKindPage),
    SelectData = SelectFirstRecord[Data],

    // Exemple 3
    underscore = pdfAnalyse,
    SelectColumnKind = underscore[Kind],
    SelectPages = List.Select(SelectColumnKind, (t) => t= "Page"),
    CountItems = List.Count(SelectPages)
in
    CountItems

A+

Florent74 · 6 Novembre 2024

Bonjour à tous et merci pour vos contributions !!

Afin de récupérer d’autres informations dont j’ai besoin pour traiter les pdf et les résultats d’analyses, j’ai essayé d’extraire le code PSV de chaque résultat. J’ai inséré les lignes suivantes dans le code de la requete Qry_ExtractAnalyses :

Dans la fonction fnExtractInfosSavoieLabo
ExtractPSV = let txt = List.Select(BufferInfosPage1, each Text.StartsWith(_, "PSV :")){0} in [NomPSV = Text.AfterDelimiter(txt, "PSV :",1)],

Dans la fonction fnExtractInfosLidal
ExtractPSV = let txt = List.Select(BufferInfosPage1, each Text.StartsWith(_, "Code et Nom PSV * :")){0} in [NomPSV = Text.BetweenDelimiters(txt, "Code et Nom PSV * :", " -")],

Et dans la variable ExpandInfosAnalyses
ExpandInfosAnalyses = Table.ExpandRecordColumn(SelectColumns, "DataAnalyse", {"DatePrélèvement", "DateRéception", "IdentificationEchantillon", "TypeDeVisite", "TypeAnalyse", "Commune", "NomPSV"}, {"DatePrélèvement", "DateRéception", "IdentificationEchantillon", "TypeDeVisite", "TypeAnalyse", "Commune", "NomPSV"}),

La requete me renvoie des valeurs null pour le champ NomPSV.

Est-ce que vous auriez une idée des erreurs de syntaxe des requêtes que j’ai rajoutées ?

D’autre part, dans la requete Qry_DétailAnalyse, serait-il possible de supprimer les lignes du tableau pour lesquelles il n’y a pas de valeur de résultats ? Dans l’exemple ci-dessous, il s’agirait de pouvoir supprimer les lignes "Organoleptique", "PHYSICO-CHIMIQUE" et "TERRAIN" qui ne sont pas des résultats en tant que telle mais des catégories de paramètres.

Merci à vous !!

Florent74 · 6 Novembre 2024

mromain à dit:
Bonjour à tous,

@Florent74 :

Voici quelques réponses :

Pour extraire la référence du labo LIDAL :
Il te faut extraire la référence à l'aide de la fonction Text.BeforeDelimiter (aide).
Voir l'exemple 1 de la requête ci-dessous.

Pour le fait d'utiliser la mesure de conductivité (µS/cm) pour repérer une analyse LIDAL :
Comme le disait @jurassic pork, j'ai opté pour cette solution à cause de la structure du fichier E23.279.1.pdf. On peut tout à fait choisir une autre méthode pour identifier la table de résultat d'analyse.

Pour la partie suivante :

Je ne comprends pas trop ce que tu veux dire...
Dans les exemples précédents, on identifie le labo en cherchant le texte Page X sur X en bas de page. Si on le trouve, alors il s'agit du labo LIDAL, sinon du labo Savoie-Labo.
Ensuite, en fonction du labo, on récupère l'identification de l'échantillon.

Pour la partie suivante :

Voir l'exemple 2 de la requête ci-dessous qui détaille ce code.

Le mot Kind sert choisir sur quelle colonne on filtre la table pdfAnalyse.
Voir l'étape SelectKindPage de l'exemple.

Le mot [Data] sert à choisir quel champ du record on veut.
Voir l'étape SelectData de l'exemple.

Pour l'identification du nombre de pages de l'analyse.
Voir l'exemple 3 de la requête ci-dessous qui détaille ce code.

_ : dans le contexte du Table.Group(AddAnalysesGeneralInfos, {"ID_Analyse", "Labo"}, ..., le _ représente la table de données pour un couple ID_Analyse / Labo donné.

_[Kind] correspond à la sélection de la colonne Kind.
Voir l'étape SelectColumnKind de l'exemple.

List.Select(..., (t) => t= "Page") permet de filtrer la liste précédente pour ne sélectionner que les éléments valant Page.
Voir l'étape SelectPages de l'exemple.

List.Count(...) permet de compter le nombre d'éléments et donc de renvoyer le nombre de pages.
Voir l'étape CountItems de l'exemple.

PowerQuery:

let // Exemple 1 ExtractRef = Text.BeforeDelimiter("E24.4967.1_20241021112206", "_"), // Exemple 2 pdfAnalyse = Table.AddColumn(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdE9CoAwDAXgq5ROCgpN/2xv4eBWHBTExfvPItrXYsQt5HvDI0lJjsu+KUWye01y7pKclvV4dnkUzaWC2ryrg7oE9W/QlKD5Dt5tNHrpute9MkDD0QItRwd0HD3QcxyAA8cADBwjMDIklRFThXgOfT3HllPafMqe6mvOJw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Name = _t, Kind = _t]), "Data", each #table ({"Column1"}, {{"DataTest"}}), type table), SelectKindPage = Table.SelectRows(pdfAnalyse, each ([Kind] = "Page")), SelectFirstRecord = Table.First(SelectKindPage), SelectData = SelectFirstRecord[Data], // Exemple 3 underscore = pdfAnalyse, SelectColumnKind = underscore[Kind], SelectPages = List.Select(SelectColumnKind, (t) => t= "Page"), CountItems = List.Count(SelectPages) in CountItems

A+

Bonjour mromain !!

Merci pour ces éclairages !! Il me manque encore pas mal de notions pour bien comprendre le role de chaque élément dans la syntaxe des requêtes. Je vais y aller petit à petit !!

Florent74 · 6 Novembre 2024

jurassic pork à dit:
Hello,
en attendant le retour de mromain , je vais essayer de répondre à certaine de tes demandes :
pour le problème du numéro d’identification de l’analyse tu peux faire comme ceci pour le Lidal :

PowerQuery:

ExtractRefEchantillon = let txt = List.Select(BufferInfosPage1, each Text.StartsWith(_, "RAPPORT D'ANALYSE Eaux")){0} in [IdentificationEchantillon = Text.BetweenDelimiters(txt, " ", "_",3)],

si ta ligne est toujours du type :

on capture ce qui est entre le 3ème espace et le _
Pour le deuxième problème tu peux remplacer µS/cm par TERRAIN (ou un autre mot) si celui-ci est toujours présent dans la table qui contient les données (et pas ailleurs).
Je me suis demandé pourquoi mromain avait fait comme cela : c'est parce qu'il y a un souci dans des PDF comme le E23.279.1.pdf :
En effet dans celui-ci les données ne sont pas dans la table qui contient PARAMETRE ANALYTIQUE qui ne contient alors qu'une seule ligne mais dans une autre table (qu'on repère par le mot décrit précédemment).
L'autre souci c'est que par rapport aux autres PDF on perd donc la ligne avec PARAMETRE ANALYTIQUE mais aussi la ligne avec BACTÉRIOLOGIQUE. J'espère que ce n'est pas gênant sinon il faut la recréer.
Dans les autres PDF on a tout dans une seule table.
D'autre part il y a un souci avec mon code VBA (message #19 ) qui crée des feuilles aves les résultats :
des connexions PQ sont créées. Si quelqu'un peut corriger ce problème.
Ami calmant, J.P

Bonjour Jurassik Pork,
C'est parfait la requete pour extraire entre délimiteurs !! Merci !!

Florent74 · 1 Décembre 2024

Bonjour à tous,

J'ai pas mal avancé pour l'intégration automatiques des fichiers pdf. Alors merci encore pour toutes vos contributions !! Je peux clore ce post !!

XL 2021 VBA et Power Query : ecrire des requetes avec variables

Florent74

XLDnaute Nouveau

Pièces jointes

Florent74

Florent74

XLDnaute Nouveau

jurassic pork

XLDnaute Occasionnel

mromain

XLDnaute Barbatruc

Pièces jointes

jurassic pork

XLDnaute Occasionnel

Florent74

XLDnaute Nouveau

Florent74

XLDnaute Nouveau

Florent74

XLDnaute Nouveau

Florent74

XLDnaute Nouveau

jurassic pork

XLDnaute Occasionnel

mromain

XLDnaute Barbatruc

Florent74

XLDnaute Nouveau

Florent74

XLDnaute Nouveau

Florent74

XLDnaute Nouveau

Florent74

XLDnaute Nouveau

Discussions similaires