POWERQUERY :: Récupérer les attributs d'un fichier, Parcourir un dossier, Récupérer le contenu d'un fichier texte, Vérifier la présence d'un fichier

oguruma · 16 Juin 2024

Bonjour,
le titre du POST montre déjà qu'il sera bien garni d'une série de fonctions qui pourraient rendre de nombreux services.
A défaut des éléments fonctionnels que renvoient les fonctions elles ont aussi un intérêt pédagogique pour ceux et celles qui souhaitent franchir le pas du langage M.

Ce POST met aussi en application les fonctions présentées dans Post sur la manière de récupérer le contenu d'une plage nommée.

Environnement Excel

Ces tableaux feront office de paramètres pour les fonctions. On accède à ceux via : manière de récupérer le contenu d'une plage nommée.

1 - Récupérer les attributs d'un fichier

Il s'agit de récupérer l'un de ceux-ci
{"Date modified", "Date created", "Date accessed","Folder Path", "Name", "Content", "Extension",
"Size", "ReadOnly", "Hidden","Kind","ChangeTime", "Directory", "Archive", "System", "Compressed", "Encrypted"},

La fonction - fnGetFileAttributesV1

Exemple : = fnGetFileAttributesV1(FullFilePathName,1)

PowerQuery:

let
    FullFilePathName=fnGetRangeValue("FULLFILEPATHNAME"),
    Source = fnGetFileAttributesV1(FullFilePathName,1)
in
    Source

Source = fnGetFileAttributesV1(FullFilePathName,1) : on récupère le paramètre "Date created" de la liste ListAttribute
(l'indice d'une liste démarre à 0 pour le 1ère élément)

L'extraction de l'attribut se fait comme suit :

PowerQuery:

                    FA = if FileExists then
                            if AttributeNumber < 7 
                                then
                                        let  
                                            StrFileAttribute="Folder.Contents(FolderPathName){[Name=FileName]}[" & AttributeName & "]" ,
                                            RecEval=[FolderPathName = FolderPathName, Folder.Contents=Folder.Contents, FileName=FileName],                
                                            FileAttribute=Expression.Evaluate(StrFileAttribute, RecEval)            
                                        in  
                                            FileAttribute
                                else  
                                        let  
                                            StrFileAttribute="RecordAttributes[" & AttributeName & "]",
                                            RecEval=[RecordAttributes = RecordAttributes],
                                            FileAttribute=Expression.Evaluate(StrFileAttribute, RecEval)            
                                        in 
                                            FileAttribute
                        else "#N/A"

Etant donné que l'on accède à une colonne de manière dynamique (AttributeName=ListAttribute{AttributeNumber},) nous sommes donc contraints de passer par un Expression.Evaluate en passant cette chaine qui construit automatiquement la requête pour obtenir l'attribut souhaité (StrFileAttribute="Folder.Contents(FolderPathName){[Name=FileName]}[" & AttributeName & "]" ,).

Concernant la seconde partie des attributs ( "Size", "ReadOnly", "Hidden","Kind","ChangeTime", "Directory", "Archive", "System", "Compressed", "Encrypted"}, ) c'est plus délicat car on doit d'abord adresser la colonne [Attributes] de type Record pour ensuite obtenir l'attribut concerné.
Afin de rendre le code plus souple tous les attributs sont mis dans une seule liste. La différence entre les deux parties se fait via l'indice 7 dans la liste afin de débrayer sur une autre méthode d'extraction comme suit :

PowerQuery:

                                     let  
                                            StrFileAttribute="RecordAttributes[" & AttributeName & "]",
                                            RecEval=[RecordAttributes = RecordAttributes],
                                            FileAttribute=Expression.Evaluate(StrFileAttribute, RecEval)            
                                        in 
                                            FileAttribute

On remarquera au passage que dans un bloc if...then...else on peut effectuer plusieurs traitements en passant par un bloc let...in imbriqué.

Code complet de la fonction

PowerQuery:

let fnGetFileInfoDateAttributes = (
                    pFilePathFullName as any,
                    optional pAttrib as number
        ) as any => 
        let
                    FilePathNameString = pFilePathFullName,
                    AttributeNumber=if pAttrib is null then 0 else pAttrib,
                    ListAttribute={"Date modified", "Date created", "Date accessed","Folder Path", "Name", "Content", "Extension", 
                    //                 0                  1               2               3           4        5         6         
                                   "Size", "ReadOnly", "Hidden","Kind","ChangeTime", "Directory", "Archive", "System", "Compressed", "Encrypted"}, 
                    //                7          8       9        10           11           12       13           14            15        16

                    AttributeName=ListAttribute{AttributeNumber},

                    //=========================================================================================
                    // Longueur totale du nom de fichier avec son chemin
                    //=========================================================================================
                    Length = Text.Length(FilePathNameString),

                    //=========================================================================================
                    // On calcule la position du dernier slash pour identifer le fichier
                    //=========================================================================================
                    PositionLastSlash = Text.PositionOf(FilePathNameString,"\",Occurrence.Last),

                    //=========================================================================================
                    // Chemin du fichier
                    //=========================================================================================
                    FolderPathName = Text.Start(FilePathNameString,PositionLastSlash + 1),

                    //=========================================================================================
                    // Nom du fichier
                    //=========================================================================================
                    FileName = Text.End(FilePathNameString,Length - PositionLastSlash - 1),

                    //=========================================================================================
                    // Traitement de la seconde partie des attributs                    
                    //=========================================================================================
                    RecordAttributes = try Folder.Contents(FolderPathName){[Name=FileName]}[Attributes] otherwise false,  
                    FileExists=if RecordAttributes is record then true else false,

                    //=========================================================================================
                    // Aiguillage sur les attributs                                        
                    // Retour de l'attribut 
                    //=========================================================================================
                    FA = if FileExists then
                            if AttributeNumber < 7 
                                then
                                        let  
                                            StrFileAttribute="Folder.Contents(FolderPathName){[Name=FileName]}[" & AttributeName & "]" ,
                                            RecEval=[FolderPathName = FolderPathName, Folder.Contents=Folder.Contents, FileName=FileName],                
                                            FileAttribute=Expression.Evaluate(StrFileAttribute, RecEval)            
                                        in  
                                            FileAttribute
                                else  
                                        let  
                                            StrFileAttribute="RecordAttributes[" & AttributeName & "]",
                                            RecEval=[RecordAttributes = RecordAttributes],
                                            FileAttribute=Expression.Evaluate(StrFileAttribute, RecEval)            
                                        in 
                                            FileAttribute
                        else "#N/A"
                in
                    FA
in  
    fnGetFileInfoDateAttributes

Les versions 2 à 4 sont des variantes sur la manière de passer les paramètres (utilisation de fnGetParameters et de fnGetRangeValue) déjà expliqués dans de nombreux posts de ce forum.

Tester la présence d'un fichier : fnFileExists

PowerQuery:

let fnFileExists = (
                    pFilePathFullName as any
        ) as logical => 
        let
            FilePathNameString = pFilePathFullName,

            //=========================================================================================
            // Longueur totale du nom de fichier avec son chemin
            //=========================================================================================
            Length = Text.Length(FilePathNameString),

            //=========================================================================================
            // On calcule la position du dernier slash pour identifer le fichier
            //=========================================================================================
            PositionLastSlash = Text.PositionOf(FilePathNameString,"\",Occurrence.Last),

            //=========================================================================================
            // Chemin du fichier
            //=========================================================================================
            FolderPathName = Text.Start(FilePathNameString,PositionLastSlash + 1),

            //=========================================================================================
            // Nom du fichier
            //=========================================================================================
            FileName = Text.End(FilePathNameString,Length - PositionLastSlash - 1),

            //=========================================================================================
            // Vérification de l'existance du fichier
            //=========================================================================================
            RecordAttributes = try Folder.Contents(FolderPathName){[Name=FileName]}[Attributes] otherwise false,  
            FileExists=if RecordAttributes is record then true else false,
            isExists = if FileExists then true else false
        in
            isExists
in  
    fnFileExists

Utilisation
= fnFileExists("D:\DATA\98__SOURCES__DONNEES\__BDD__VILLES\communes-departement-region.csv") ==> renvoie true
= fnFileExists("D:\DATA\98__SOURCES__DONNEES\__BDD__VILLES\communes-departement-regionS.csv") ==> renvoie false
A vous d'aménager les autres formes d'appels possibles comme les fonctions précédentes.

Fonction : fnGetCsvDocumentV1

A travers l'attribut Content on récupère le contenu du fichier

Utilisation :
= fnGetCsvDocumentV1("FILEPATH","FILENAME",true,",",null,65001)
= fnGetCsvDocumentV2("FULLFILEPATHNAME",true,",",null,65001)
= fnGetCsvDocumentV3(FullName,true,",",null,65001)
Les versions 2 et 3 sont d'autres méthode de construction du nom de fichier.
Ps : Encoding 1252 pour Windows - 65001 : UTF8

PowerQuery:

let fnGet_Csv_DocumentV1 = (
        pFilePath as any,
        pFileName as any,
        optional pPromote as logical,
        optional pDelimiter as text,
        optional pNbColumns as number,
        optional pEncoding as number
) as table =>
    let

        CONTENT_ATTRIBUT=5,

        // Gestion des paramètres optionnels
        Promote=if pPromote is null then true else pPromote,
        Delim=if pDelimiter is null then ";" else pDelimiter,
        NbColumns=if pNbColumns is null or pNbColumns=0  then null else pNbColumns,
        Encode=if pEncoding is null then 65001 else pEncoding,
        
        // Chemin du fichier        
        FolderPathName = fnGetParameters("TB_PARAMS",pFilePath),

        // Nom du fichier
        FileName = fnGetParameters("TB_PARAMS",pFileName),

        // Nom complet du fichier
        FullFileName= FolderPathName & "\" & FileName,

        // Si le fichier est présent on l'importe
        ToTableIfExists = if fnFileExists(FullFileName)
                then
                    let 
                        Source = fnGetFileAttributesV1(FullFileName,CONTENT_ATTRIBUT),
                        GetCSVFile = Csv.Document(Source,[Delimiter=Delim, Columns=NbColumns, Encoding=Encode, QuoteStyle=QuoteStyle.None]),                        
                        TablePromoteHeader = if Promote then Table.PromoteHeaders(GetCSVFile, [PromoteAllScalars=true]) else GetCSVFile
                    in  
                        TablePromoteHeader
                else  
                    #table({},{})
    in
        ToTableIfExists
in  
    fnGet_Csv_DocumentV1

Fonction : fnGetAllFilesFromFolderV1
Cette fonction permet de produire la liste des fichiers à partir d'un répertoire (Directory). A l'issue on peut filtrer sur l'extension et la taille et aussi la possibilité de réorganiser les colonnes pour avoir un contenu plus fonctionnel.

= fnGetAllFilesFromFolderV1("D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\",null,null,".csv",false)
= fnGetAllFilesFromFolderV1("D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\",null,null,".xlsx",true)
= fnGetAllFilesFromFolderV1("D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\",30000,null,".xlsx",true)
= fnGetAllFilesFromFolderV1(PathName,null,null,null,true)
= fnGetAllFilesFromFolderV1(PathName,null,null,".csv",true)
= fnGetAllFilesFromFolderV2(PathName,null,null,".csv",true)

PowerQuery:

let fnGetAllFilesFromFolder = (
        pPath as text, 
        optional pSizeFilter as number, 
        optional pOperator as text,
        optional pExtension as text,
        optional pReorg as logical,
        optional pListOptions as nullable record ) as any =>

    let
        //************************************************************************************************
        // Gestion des paramètres
        //************************************************************************************************
        SizeFilter=if pSizeFilter is null then 0 else pSizeFilter,
        Operator=if pOperator is null then ">" else pOperator,
        Extent=if pExtension is null then "*" else pExtension, 
        Reorg=if pReorg is null then false else pReorg,

        //************************************************************************************************
        // Contenu de la directory à traiter
        //************************************************************************************************
        TableContents = Table.Buffer(Folder.Contents(pPath, pListOptions)),        

        //************************************************************************************************
        // On va parcourir la Directory
        //************************************************************************************************
        RTable=
            let
                ListSubfolders = Table.AddColumn(
                    Table.SelectRows(TableContents, each [Attributes][Directory] ),
                    "File Path",
                    each Text.Combine({[Folder Path], [Name]}),
                    type text)[File Path],
                //*****************************************************************************************
                // Fusion de la Directory en cours avec la précédente et on passe à la suivante
                //*****************************************************************************************
                ToTableList = Table.Combine(
                                            List.Combine({{TableContents},
                                            List.Transform(ListSubfolders, @fnGetAllFilesFromFolder)})
                                            )
            in 
                ToTableList,
        
        //************************************************************************************************
        // On positionne le filtre sur l'extension à l'issue du parcours de toutes les Directory
        //************************************************************************************************
        TblExtension=if Extent = "*" then RTable else Table.SelectRows(RTable, each [Extension]=Extent),

        //************************************************************************************************
        // On positionne le filtre sur la taille du fichier
        //************************************************************************************************
        //StrTbSel="each [Attributes][Directory] = true or [Attributes][Size]" & Operator & Number.ToText(SizeFilter),
        StrTbSel="each [Attributes][Size]" & Operator & Number.ToText(SizeFilter),
        EvalTbSel=Expression.Evaluate(StrTbSel,[Operator=Operator, SizeFilter=SizeFilter, TblExtension=TblExtension]),
        
        //************************************************************************************************
        // On active la sélection sur la taille du fichier
        //************************************************************************************************
        TblSelect=Table.SelectRows(TblExtension, EvalTbSel),

        //************************************************************************************************
        // On réorganise les colonnes si demandé
        //************************************************************************************************
        TblReorg=if Reorg 
                 then let 
                        // On insère la taille du fichier (et la colonne Attributes est supprimée)
                        TblSize = Table.ExpandRecordColumn(TblSelect, "Attributes", {"Size"}, {"Size"}),
                        // On supprime les colonnes inutiles
                        TblDelColumns=Table.RemoveColumns(TblSize,{"Content", "Extension"}),
                        // Le chemin est placé en tête des colonnes
                        TblReorderColumns=Table.ReorderColumns(TblDelColumns,{"Folder Path", "Name", "Size", "Date accessed", "Date modified", "Date created"})
                      in  
                        TblReorderColumns
                 else 
                      TblSelect
    in  
        //************************************************************************************************
        // Table finale
        //************************************************************************************************
        TblReorg
in 
    fnGetAllFilesFromFolder

Je vous invite à consulter le fichier joint pour plus de compréhension. Pour les débutants ne pas hésiter à s'en inspirer pour vos futurs développement en M.

POWERQUERY :: Récupérer les attributs d'un fichier, Parcourir un dossier, Récupérer le contenu d'un fichier texte, Vérifier la présence d'un fichier

oguruma

XLDnaute Occasionnel

Pièces jointes

Discussions similaires