POWERQUERY :: Simuler la lecture séquentielle d'un fichier texte comme en VBA - While Not Eof(1) ... Wend

oguruma

oguruma

XLDnaute Occasionnel
Ce post a peut-être plus un intérêt pédagogique... qui sait... il pourrait aussi dépanner certains ou produire d'autres idées pour d'autres pour leurs futurs développements.
Il montre que l'on peut lire et traiter un fichier de type texte en examinant les enregistrements un à un afin de charger uniquement ceux qui répondent à certains critères.
D'une manière plus classique en PowerQuery on aurait d'abord charger charger le fichier intégralement puis à l'issue on aurait appliquer les filtres.
A tester sur une volumétrie conséquente. Dans mon test : 1000 enreg c'est too good

Tout est expliqué dans le source ci-dessous.
PowerQuery: 
let

    //-------------------------------------------------------------------------------------------------------
    // RQ_WHILE_NOT_EOF
    //-------------------------------------------------------------------------------------------------------

    //-------------------------------------------------------------------------------------------------------
    // A cette requête a pour but de simuler la lecture d'un fichier texte un peu comme en VBA
    // Avec une boucle de lecture enregistrement par enregistrement
    // Do While Not Eof(1)
    //    LineInput.....
    // Wend
    //-------------------------------------------------------------------------------------------------------
    // Intérêt pédogique sur l'utilisation de la boucle List.Accumulate
    // On peut certes procéder autrement
    // Ce qu'il faut retenir ce sont les possibilités qu'offre la fonction List.Accumalate
    // ainsi que l'utilisation des fonctions List.*
    //-------------------------------------------------------------------------------------------------------

    //-------------------------------------------------------------------------------------------------------
    // On prépare les paramètres de lecture
    //-------------------------------------------------------------------------------------------------------
    pFileInput   = "TestListCSV",     
    pExtension  = ".csv",
    pFolder     = null,

    //-------------------------------------------------------------------------------------------------------
    // Séparateur qui indique une fin de ligne : retour chariot, ligne suivante sous Windows
    // si fichier fourni par un système UNIX :  #(lf)
    //-------------------------------------------------------------------------------------------------------
    pSep        = "#(cr)#(lf)",

    //-------------------------------------------------------------------------------------------------------
    // Pendant la lecture on va simuler une extraction
    // Ainsi on va éviter de charger tout les fichier dans la table
    //-------------------------------------------------------------------------------------------------------
    GENRE="M",
    SERVICE="1-INFORMATIQUE",
    CDI="2-CDI",

    //-------------------------------------------------------------------------------------------------------
    // Localtion du fichier source
    //-------------------------------------------------------------------------------------------------------
    ParamPathDefaut="D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqDATA",
    ParamExtDefaut=".ini",
    DEFAULT = ParamPathDefaut & "\",
    BACK_SLASH="\",
    Extension =  if pExtension is null then
                    if ParamExtDefaut is null then
                       ".pq" else
                       ParamExtDefaut
                    else pExtension,
                    
    PathInitial = if (pFolder <> null) then pFolder else DEFAULT,
    PathProcedure = PathInitial & (if Text.End(PathInitial, 1) <> BACK_SLASH then BACK_SLASH else ""),
    FileParam = PathProcedure & pFileInput & Extension,   
  
    //-------------------------------------------------------------------------------------------------------
    // On accède au fichier sous forme brute
    //-------------------------------------------------------------------------------------------------------
    Source = Text.FromBinary(Binary.Buffer(File.Contents(FileParam))),   

    //-------------------------------------------------------------------------------------------------------
    // Le fichier est transformé en List
    //-------------------------------------------------------------------------------------------------------
    SourceList=Text.Split(Source,pSep),

    //-------------------------------------------------------------------------------------------------------
    // Calcul du nombre de lignes - cela sera utile pour déterminer le Nbr de lignes effectives à charger
    //-------------------------------------------------------------------------------------------------------
    SourceNbLignes=List.Count(SourceList),

    //-------------------------------------------------------------------------------------------------------
    // On écarte l'enregistrement comportant les titres des colonnes
    //-------------------------------------------------------------------------------------------------------
    LastRecord=SourceList{SourceNbLignes - 1},

    //-------------------------------------------------------------------------------------------------------
    // On isole la 1ère colonne du dernier enregistrement
    //-------------------------------------------------------------------------------------------------------
    FirstColumn=Text.Split(LastRecord,";"){0},

    //-------------------------------------------------------------------------------------------------------
    // On vérifier si il y a la présence du CR/LF - non imprimable donc vide
    //-------------------------------------------------------------------------------------------------------
    isCRLF=if FirstColumn = "" then true else false, 

    //-------------------------------------------------------------------------------------------------------
    // On récupère les titre de colonnes du 1er enregistrement pour le renommage final
    //-------------------------------------------------------------------------------------------------------
    ListTitreColonnes=Text.Split(List.First(SourceList),";"),
    //-------------------------------------------------------------------------------------------------------
    // On gère le cas où un CRL/LF termine le fichier afin d'éviter des erreurs sur la lecture de la liste
    //-------------------------------------------------------------------------------------------------------
    SourceListData= if isCRLF then
                       List.FirstN(List.LastN(SourceList,SourceNbLignes-1),SourceNbLignes-2)
                       else List.LastN(SourceList,SourceNbLignes-1),   

    //-------------------------------------------------------------------------------------------------------
    // Boucle de lecture des enregistrements et sélection selon le Genre, le Service et le Contrat
    //-------------------------------------------------------------------------------------------------------
    SelectGenre = List.Accumulate(
                    SourceListData,  // Liste des enregistrement à traiter
                    {},              // On prépare l'accumulateur pour renvoyer une liste

                    (state, current) => // Boucle de lecture sur la liste                   
                        let                           
                            Enreg=Text.Split(current,";"),
                            //----------------------------------------
                            // Extraction des informations à tester
                            //----------------------------------------
                            Genre=Enreg{3},
                            Service=Enreg{9},
                            Contrat=Enreg{7},
                            //----------------------------------------
                            // Application des filtres
                            //----------------------------------------
                            SelectCurrent= if Genre = GENRE and Service = SERVICE and Contrat =  CDI then
                                              state &  {current} else
                                              state
                        in
                            SelectCurrent
                ),

    //-------------------------------------------------------------------------------------------------------
    // On obtient une liste d'enregistrements où les colonnes sont séparées par des ";"
    //-------------------------------------------------------------------------------------------------------
    //-------------------------------------------------------------------------------------------------------
    // Puis transformation de la liste en table
    //-------------------------------------------------------------------------------------------------------
    ToTableSelect = Table.FromList(SelectGenre, Splitter.SplitTextByDelimiter(";"), null, null, ExtraValues.Error),

    //-------------------------------------------------------------------------------------------------------
    // On recupère les noms de colonnes mis par défaut pour les remplacer par ceux identifiés dans l'enreg 1
    //-------------------------------------------------------------------------------------------------------
    ListOldColumns=Table.ColumnNames(ToTableSelect),

    //-------------------------------------------------------------------------------------------------------
    // On positionne les noms de colonnes trouvés dans le 1er enreg et fin des traitements
    //-------------------------------------------------------------------------------------------------------
    ToTable=Table.RenameColumns(ToTableSelect,
                        List.Zip( {ListOldColumns, ListTitreColonnes} ),
                        MissingField.Ignore )   
    
in
    ToTable
 

Pièces jointes

  • TestListCSV.zip
    30.4 KB · Affichages: 0
  • GetParamsAndLoad_V0.58.xlsx
    105.2 KB · Affichages: 0
oguruma

oguruma

XLDnaute Occasionnel
Une variante avec List.Generate

PowerQuery: 
let

    //-------------------------------------------------------------------------------------------------------
    // RQ_WHILE_NOT_EOF
    // Version List.Generate
    //-------------------------------------------------------------------------------------------------------

    //-------------------------------------------------------------------------------------------------------
    // A cette requête a pour but de simuler la lecture d'un fichier texte un peu comme en VBA
    // Avec une boucle de lecture enregistrement par enregistrement
    // Do While Not Eof(1)
    //    LineInput.....
    // Wend
    //-------------------------------------------------------------------------------------------------------
    // Intérêt pédogique sur l'utilisation de la boucle List.Accumulate
    // On peut certes procéder autrement
    // Ce qu'il faut retenir ce sont les possibilités qu'offre la fonction List.Accumalate
    // ainsi que l'utilisation des fonctions List.*
    //-------------------------------------------------------------------------------------------------------

    //-------------------------------------------------------------------------------------------------------
    // On prépare les paramètres de lecture
    //-------------------------------------------------------------------------------------------------------
    pFileInput   = "TestListCSV",     
    pExtension  = ".csv",
    pFolder     = null,

    //-------------------------------------------------------------------------------------------------------
    // Séparateur qui indique une fin de ligne : retour chariot, ligne suivante sous Windows
    // si fichier fourni par un système UNIX :  #(lf)
    //-------------------------------------------------------------------------------------------------------
    pSep        = "#(cr)#(lf)",

    //-------------------------------------------------------------------------------------------------------
    // Pendant la lecture on va simuler une extraction
    // Ainsi on va éviter de charger tout les fichier dans la table
    //-------------------------------------------------------------------------------------------------------
    GENRE="M",
    SERVICE="1-INFORMATIQUE",
    CDI="2-CDI",

    //-------------------------------------------------------------------------------------------------------
    // Localtion du fichier source
    //-------------------------------------------------------------------------------------------------------
    ParamPathDefaut="D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqDATA",
    ParamExtDefaut=".ini",
    DEFAULT = ParamPathDefaut & "\",
    BACK_SLASH="\",
    Extension =  if pExtension is null then
                    if ParamExtDefaut is null then
                       ".pq" else
                       ParamExtDefaut
                    else pExtension,
                    
    PathInitial = if (pFolder <> null) then pFolder else DEFAULT,
    PathProcedure = PathInitial & (if Text.End(PathInitial, 1) <> BACK_SLASH then BACK_SLASH else ""),
    FileParam = PathProcedure & pFileInput & Extension,   
  
    //-------------------------------------------------------------------------------------------------------
    // On accède au fichier sous forme brute
    //-------------------------------------------------------------------------------------------------------
    Source = Text.FromBinary(Binary.Buffer(File.Contents(FileParam))),   

    //-------------------------------------------------------------------------------------------------------
    // Le fichier est transformé en List
    //-------------------------------------------------------------------------------------------------------
    SourceList=Text.Split(Source,pSep),

    //-------------------------------------------------------------------------------------------------------
    // Calcul du nombre de lignes - cela sera utile pour déterminer le Nbr de lignes effectives à charger
    //-------------------------------------------------------------------------------------------------------
    SourceNbLignes=List.Count(SourceList),

    //-------------------------------------------------------------------------------------------------------
    // On écarte l'enregistrement comportant les titres des colonnes
    //-------------------------------------------------------------------------------------------------------
    LastRecord=SourceList{SourceNbLignes - 1},

    //-------------------------------------------------------------------------------------------------------
    // On isole la 1ère colonne du dernier enregistrement
    //-------------------------------------------------------------------------------------------------------
    FirstColumn=Text.Split(LastRecord,";"){0},

    //-------------------------------------------------------------------------------------------------------
    // On vérifier si il y a la présence du CR/LF - non imprimable donc vide
    //-------------------------------------------------------------------------------------------------------
    isCRLF=if FirstColumn = "" then true else false, 

    //-------------------------------------------------------------------------------------------------------
    // On récupère les titre de colonnes du 1er enregistrement pour le renommage final
    //-------------------------------------------------------------------------------------------------------
    ListTitreColonnes=Text.Split(List.First(SourceList),";"),
    //-------------------------------------------------------------------------------------------------------
    // On gère le cas où un CRL/LF termine le fichier afin d'éviter des erreurs sur la lecture de la liste
    //-------------------------------------------------------------------------------------------------------
    SourceListData= if isCRLF then
                       List.FirstN(List.LastN(SourceList,SourceNbLignes-1),SourceNbLignes-2)
                       else List.LastN(SourceList,SourceNbLignes-1),   

    //-------------------------------------------------------------------------------------------------------
    // Boucle de lecture des enregistrements et sélection selon le Genre, le Service et le Contrat
    //-------------------------------------------------------------------------------------------------------
    SelectGenreGenerate=List.Generate(
                            //-------------------------------------------------------
                            // On décide de renvoyer une liste de records
                            //-------------------------------------------------------
                            () => [ e=0,
                                    SelectEnregi=SourceListData{e}   // Initialisation de la boucle
                                  ],
                            each [e] <= List.Count(SourceListData) - 1, // Condition de fin de boucle
                            each [ SelectEnregi= let
                                                    Enreg=SourceListData{e}, // Enregitrement en cours de lecture
                                                    //-------------------------------------------------------
                                                    // Extraction des colonnes concernées par le filtre
                                                    // avec un découpage préalable
                                                    //-------------------------------------------------------
                                                    Genre=Text.Split(Enreg,";"){3},
                                                    Service=Text.Split(Enreg,";"){9},
                                                    Contrat=Text.Split(Enreg,";"){7},
                                                    //-------------------------------------------------------
                                                    // Selection
                                                    //-------------------------------------------------------
                                                    SelectCurrent= if Genre = GENRE and Service = SERVICE and Contrat =  CDI then
                                                                              Enreg
                                                                              else null // Pas d'autres moyens que de placer un null
                                                 in
                                                     SelectCurrent,
                                  //-------------------------------------------------------
                                  // Indice pour le compteur d'enregistrements
                                  // et on passe au suivant
                                  //-------------------------------------------------------
                                   e = [e] + 1
                                 ]
            ),

    //-------------------------------------------------------
    // Conversion en table
    //-------------------------------------------------------
    RecordsToTable = Table.FromList(SelectGenreGenerate, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    TableExpanded = Table.ExpandRecordColumn(RecordsToTable, "Column1", {"SelectEnregi"}, {"Column1.SelectEnregi"}),
    ExpandedToList = TableExpanded[Column1.SelectEnregi],
    ExpandToTableTmp = Table.FromList(ExpandedToList, Splitter.SplitTextByDelimiter(";"), null, null, ExtraValues.Error),

    //-------------------------------------------------------
    // Suppression des null positionnés pendant la boucle
    //-------------------------------------------------------
    ToTableDeleteNull = Table.SelectRows(ExpandToTableTmp, each ([Column1] <> null)),
    ListOldColumns=Table.ColumnNames(ToTableDeleteNull),

    //-------------------------------------------------------
    // Nommage des colonnes
    //-------------------------------------------------------
    ToTable=Table.RenameColumns(ToTableDeleteNull,
                        List.Zip( {ListOldColumns, ListTitreColonnes} ),
                        MissingField.Ignore )   
in
    ToTable
 
oguruma

oguruma

XLDnaute Occasionnel
La variante ci-dessous avec Generate comportait un petit bug car le 1er enregistrement était pris en compte quelque soit les critères.

Voici le corrigé, je vous laisse comparer

PowerQuery: 
let

    //-------------------------------------------------------------------------------------------------------
    // RQ_WHILE_NOT_EOF
    // Version List.Generate
    //-------------------------------------------------------------------------------------------------------

    //-------------------------------------------------------------------------------------------------------
    // A cette requête a pour but de simuler la lecture d'un fichier texte un peu comme en VBA
    // Avec une boucle de lecture enregistrement par enregistrement
    // Do While Not Eof(1)
    //    LineInput.....
    // Wend
    //-------------------------------------------------------------------------------------------------------
    // Intérêt pédogique sur l'utilisation de la boucle List.Accumulate
    // On peut certes procéder autrement
    // Ce qu'il faut retenir ce sont les possibilités qu'offre la fonction List.Accumalate
    // ainsi que l'utilisation des fonctions List.*
    //-------------------------------------------------------------------------------------------------------

    //-------------------------------------------------------------------------------------------------------
    // On prépare les paramètres de lecture
    //-------------------------------------------------------------------------------------------------------
    pFileInput   = "TestListCSV",     
    pExtension  = ".csv",
    pFolder     = null,

    //-------------------------------------------------------------------------------------------------------
    // Séparateur qui indique une fin de ligne : retour chariot, ligne suivante sous Windows
    // si fichier fourni par un système UNIX :  #(lf)
    //-------------------------------------------------------------------------------------------------------
    pSep        = "#(cr)#(lf)",

    //-------------------------------------------------------------------------------------------------------
    // Pendant la lecture on va simuler une extraction
    // Ainsi on va éviter de charger tout les fichier dans la table
    //-------------------------------------------------------------------------------------------------------
    GENRE="M",
    SERVICE="1-INFORMATIQUE",
    CDI="2-CDI",

    //-------------------------------------------------------------------------------------------------------
    // Localtion du fichier source
    //-------------------------------------------------------------------------------------------------------
    ParamPathDefaut="D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqDATA",
    ParamExtDefaut=".ini",
    DEFAULT = ParamPathDefaut & "\",
    BACK_SLASH="\",
    Extension =  if pExtension is null then
                    if ParamExtDefaut is null then
                       ".pq" else
                       ParamExtDefaut
                    else pExtension,
                    
    PathInitial = if (pFolder <> null) then pFolder else DEFAULT,
    PathProcedure = PathInitial & (if Text.End(PathInitial, 1) <> BACK_SLASH then BACK_SLASH else ""),
    FileParam = PathProcedure & pFileInput & Extension,   
  
    //-------------------------------------------------------------------------------------------------------
    // On accède au fichier sous forme brute
    //-------------------------------------------------------------------------------------------------------
    Source = Text.FromBinary(Binary.Buffer(File.Contents(FileParam))),   

    //-------------------------------------------------------------------------------------------------------
    // Le fichier est transformé en List
    //-------------------------------------------------------------------------------------------------------
    SourceList=List.Buffer(Text.Split(Source,pSep)),

    //-------------------------------------------------------------------------------------------------------
    // Calcul du nombre de lignes - cela sera utile pour déterminer le Nbr de lignes effectives à charger
    //-------------------------------------------------------------------------------------------------------
    SourceNbLignes=List.Count(SourceList),

    //-------------------------------------------------------------------------------------------------------
    // On écarte l'enregistrement comportant les titres des colonnes
    //-------------------------------------------------------------------------------------------------------
    LastRecord=SourceList{SourceNbLignes - 1},

    //-------------------------------------------------------------------------------------------------------
    // On isole la 1ère colonne du dernier enregistrement
    //-------------------------------------------------------------------------------------------------------
    FirstColumn=Text.Split(LastRecord,";"){0},

    //-------------------------------------------------------------------------------------------------------
    // On vérifier si il y a la présence du CR/LF - non imprimable donc vide
    //-------------------------------------------------------------------------------------------------------
    isCRLF=if FirstColumn = "" then true else false, 

    //-------------------------------------------------------------------------------------------------------
    // On récupère les titre de colonnes du 1er enregistrement pour le renommage final
    //-------------------------------------------------------------------------------------------------------
    ListTitreColonnes=Text.Split(List.First(SourceList),";"),
    //-------------------------------------------------------------------------------------------------------
    // On gère le cas où un CRL/LF termine le fichier afin d'éviter des erreurs sur la lecture de la liste
    //-------------------------------------------------------------------------------------------------------
    SourceListData= List.Buffer(if isCRLF then
                       List.FirstN(List.LastN(SourceList,SourceNbLignes-1),SourceNbLignes-2)
                       else List.LastN(SourceList,SourceNbLignes-1)),   

    //-------------------------------------------------------------------------------------------------------
    // Boucle de lecture des enregistrements et sélection selon le Genre, le Service et le Contrat
    //-------------------------------------------------------------------------------------------------------
    SelectGenreGenerate=List.Generate(
                            //-------------------------------------------------------
                            // On décide de renvoyer une liste de records
                            //-------------------------------------------------------
                            () => [ e=0,
                                    SelectEnregi=""//SourceListData{0}   // Initialisation de la boucle
                                  ],
                            each [e] <= List.Count(SourceListData) - 1, // Condition de fin de boucle
                            each [ SelectEnregi= let
                                                    //-------------------------------------------------------
                                                    // Selection
                                                    //-------------------------------------------------------
                                                    Enreg=SourceListData{e}, // Enregitrement en cours de lecture
                                                    //-------------------------------------------------------
                                                    // Extraction des colonnes concernées par le filtre
                                                    // avec un découpage préalable
                                                    //-------------------------------------------------------
                                                    EnrSplit=Text.Split(Enreg,";"),
                                                    Genre=EnrSplit{3},
                                                    Service=EnrSplit{9},
                                                    Contrat=EnrSplit{7},
                                                    //-------------------------------------------------------
                                                    // Selection
                                                    //-------------------------------------------------------
                                                    SelectCurrent= if Genre = GENRE and Service = SERVICE and Contrat =  CDI then
                                                                              Enreg
                                                                              else null // Pas d'autres moyens que de placer un null
                                                 in
                                                     SelectCurrent,
                                  //-------------------------------------------------------
                                  // Indice pour le compteur d'enregistrements
                                  // et on passe au suivant
                                  //-------------------------------------------------------
                                   e = [e] + 1
                                 ]
            ),
    ToTableRecords = Table.FromList(SelectGenreGenerate, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandToTable1 = Table.ExpandRecordColumn(ToTableRecords, "Column1", {"SelectEnregi"}, {"Column1.SelectEnregi"}),
    DeleteNullAndBlank = Table.SelectRows(ExpandToTable1, each ([Column1.SelectEnregi] <> null and [Column1.SelectEnregi] <> "")),
    ExpandToList = DeleteNullAndBlank[Column1.SelectEnregi],
    ExpandToTable2 = Table.FromList(ExpandToList, Splitter.SplitTextByDelimiter(";"), null, null, ExtraValues.Error),

    ListOldColumns=Table.ColumnNames(ExpandToTable2),

    //-------------------------------------------------------
    // Nommage des colonnes
    //-------------------------------------------------------
    ToTable=Table.RenameColumns(ExpandToTable2,
                        List.Zip( {ListOldColumns, ListTitreColonnes} ),
                        MissingField.Ignore )   
in
    ToTable
 

Pièces jointes

  • GetParamsAndLoad_GENERATE_V0.022.xlsx
    101.7 KB · Affichages: 0

Discussions similaires

oguruma
POWERQUERY :: Récupérer des paramètres à partir d'un fichier texte sur disque et le transformer en table de paramètres
Réponses
1
Affichages
282
oguruma
oguruma
oguruma
POWERQUERY :: Charger des modules/fonctions à partir du disque et les exécuter à l'issue - Function.Invoke
Réponses
0
Affichages
97
oguruma
oguruma
oguruma
POWERQUERY :: Combiner un dossier Excel comportant des fichiers avec des onglets (tableaux structurés) de même structure
Réponses
2
Affichages
289
oguruma
oguruma
oguruma
POWERQUERY :: Combiner tous les fichiers .csv d'un dossier ou combiner des fichiers .csv spécifiques
Réponses
0
Affichages
216
oguruma
oguruma
oguruma
POWERQUERY :: Combiner tous les fichiers Excel d'un dossier ou combiner des fichiers Excel spécifiques
Réponses
0
Affichages
133
oguruma
oguruma
oguruma
POWERQUERY :: Lancement de requêtes à tiroir - dynamiques - paramétrables - code des requêtes sur disque - Exemple de mise en oeuvre
Réponses
0
Affichages
265
oguruma
oguruma
oguruma
POWERQUERY :: Faire la somme d'un tableau horizontalement et verticalement (somme des lignes, somme des colonnes)
Réponses
0
Affichages
185
oguruma
oguruma
oguruma
POWERQUERY :: Combinaisons (Table.Combine) de dossiers contenant des fichiers .CSV ou des fichiers Excel - V2
Réponses
0
Affichages
271
oguruma
oguruma

Membres actuellement en ligne

17 membres et 106 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 209
Messages
2 086 266
Membres
103 167
dernier inscrit
miriame