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

[oguruma]

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.

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

 

[oguruma]

Une variante avec List.Generate

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]

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

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