Power Query Récupérer les données des propriétés d'une liste de fichier Excel via Power Query

thibaut_excel

XLDnaute Nouveau
Bonjour.

j'ai un fichier de suivi Excel qui utilise l'obtention de données a partir d'un dossier(power query), j'arrive à récupérer toutes les données que je souhaite sauf le nom du dernière auteur( celui qui la enregistrer en dernier).
Avez-vous un idée pour réussir a obtenir ces données directement dans le fichier Excel, j'aimerais pouvoir associer le nom du dernière auteur au nom du fichier.
1672663977729.png


merci d'avance pour vos réponses.

Thibaut.
 
Solution
Bonjour Thibaut, Chris, le forum

Et bonne année !

Comme le dit Chris, selon les types de fichiers cette info n'est pas disponible.

D’après ton screenshot, tous tes fichiers sont des fichiers xlsm. Si tel est le cas, la fonction ci-dessous GetOpenXmlFileProps peut t’aider.

Elle dézippe le fichier Open XML et récupère les propriétés à partir des fichiers xml le composant et les renvoie sous forme de table.

Le code permettant de dézipper vient d’ici.

Code de la fonction (à intégrer dans une requête nommée GetOpenXmlFileProps) :
Code:
let
    
    fn = (openXmlFileBinary as binary) as table =>
        let
        
            // unzip function source...

thibaut_excel

XLDnaute Nouveau
Ce que je trouve frustrant c'est que les données de propriétés des fichiers sois extraites mais partiellement.
Power Query arrive a sortir les 3 types de date accès, modification, création toutes les données sont accessible depuis un clique droit sur le fichier:
1672669602354.png

qu'il soit un fichier Excel avec ou sans macro, doc...
n'y aurait-il pas une autre solution pour récupérer ces métadonnées, afin de les utilisées dans le fichier suivi Excel.
 

mromain

XLDnaute Barbatruc
Bonjour Thibaut, Chris, le forum

Et bonne année !

Comme le dit Chris, selon les types de fichiers cette info n'est pas disponible.

D’après ton screenshot, tous tes fichiers sont des fichiers xlsm. Si tel est le cas, la fonction ci-dessous GetOpenXmlFileProps peut t’aider.

Elle dézippe le fichier Open XML et récupère les propriétés à partir des fichiers xml le composant et les renvoie sous forme de table.

Le code permettant de dézipper vient d’ici.

Code de la fonction (à intégrer dans une requête nommée GetOpenXmlFileProps) :
Code:
let
    
    fn = (openXmlFileBinary as binary) as table =>
        let
        
            // unzip function source: https://github.com/ibarrau/PowerBi-code/blob/master/PowerQuery/ExtractZIP.pq
            UnzipFile = (ZIPFile as binary) =>
                let
                    Header = BinaryFormat.Record([      Signature            = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32,ByteOrder.LittleEndian),
                                                        Version         = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),
                                                        Flags             = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),
                                                        Compression         = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),
                                                        ModTime         = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),
                                                        ModDate         = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),
                                                        CRC32             = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32,ByteOrder.LittleEndian),
                                                        CompressedSize         = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32,ByteOrder.LittleEndian),
                                                        UncompressedSize     = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32,ByteOrder.LittleEndian),
                                                        FileNameLen         = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),
                                                        ExtraFieldLen         = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian)
                                                    ]),
                
                    FileEntry = BinaryFormat.Choice(Header, each if _[Signature] <> 0x4034B50 then BinaryFormat.Null else
                                                             BinaryFormat.Record([
                                                                            Header           = _,
                                                                            FileName         = BinaryFormat.Text(_[FileNameLen]),
                                                                            ExtraField       = BinaryFormat.Text(_[ExtraFieldLen]),
                                                                            UncompressedData = BinaryFormat.Transform(BinaryFormat.Binary(_[CompressedSize]),(x) => try Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) otherwise null)
                                                                            ]), type binary),
                    ZipFormat = BinaryFormat.List(FileEntry, each _<> null),    
                
                    Entries = List.Transform(
                            List.RemoveLastN( ZipFormat(ZIPFile), 1),
                        (e) => [FileName = e[FileName], Content = e[UncompressedData] ]
                    )
                in
                   Table.FromRecords(Entries),
        
            UnzipedFileTable = try UnzipFile(openXmlFileBinary) otherwise error "Source file is not an OpenXml file.",
            FilterProperties = let tblProps = Table.SelectRows(UnzipedFileTable, each Text.StartsWith([FileName], "docProps/") and Text.EndsWith([FileName], ".xml")) in if Table.RowCount(tblProps)=0 then error "Source file is not an OpenXml file." else tblProps,
            TransformXmlBinary = Table.TransformColumns(FilterProperties,{{"Content", each Xml.Document(_), type table}}),
            RemoveXmlErrors = Table.RemoveRowsWithErrors(TransformXmlBinary, {"Content"}),
            RecoverXmlData = Table.ExpandTableColumn(RemoveXmlErrors, "Content", {"Value"}, {"Value"}),
            RecoverProperties = Table.ExpandTableColumn(RecoverXmlData, "Value", {"Name", "Value"}, {"Name", "Value"}),
            RemoveEmptyProperties = Table.SelectRows(RecoverProperties, each (try Table.RowCount([Value]) otherwise 1)>0),
            AddColumnKind = Table.AddColumn(RemoveEmptyProperties, "Kind", each Text.BetweenDelimiters([FileName], "/", "."), type text),
            SelectColumns = Table.SelectColumns(AddColumnKind,{"Kind", "Name", "Value"})
        in
            SelectColumns,
    
    DocumentationMetadata = 
        [
            Documentation.Name = "GetOpenXmlFileProps",
            Documentation.Description = "This function extract properties of an OpenXml file (docx, xlsm, ppts, ...).", 
            Documentation.Examples = 
                {
                    [
                        Description = "Extract properties of a .docx file:",
                        Code = "GetOpenXmlFileProps(File.Contents(""c:\folder\document.docx""))",
                        Result = "Table with properties kind, name and values."
                    ],
                    [
                        Description = "Extract 'last modified by' property of a .xlsm file:",
                        Code = "GetOpenXmlFileProps(File.Contents(""c:\folder\workbook.xlsm"")){[Kind=""core"", Name=""lastModifiedBy""]}[Value]",
                        Result = "Text with 'last modified by' property."
                    ]
                }
        ]

in
    Value.ReplaceType(fn, Value.ReplaceMetadata(Value.Type(fn), DocumentationMetadata))

Exemple 1 - extraire les propriétés d’un fichier Open XML (emplacement du fichier à adapter) :
Code:
let
    EmplacementFichierOpenXml = "C:\Dossier\FichierOpenXml.docx",
    FichierBinaire = File.Contents(EmplacementFichierOpenXml),
    PropriétésOpenXml = GetOpenXmlFileProps(FichierBinaire)
in
    PropriétésOpenXml

Exemple 2 - extraire le nom du dernier auteur sur une liste de fichiers (emplacement du dossier à adapter) :
Code:
let
    EmplacementDossier = "C:\Dossier",
    FichiersDuDossier = Folder.Files(EmplacementDossier),
    AjoutColonneDernierAuteur = Table.AddColumn(FichiersDuDossier, "Dernier auteur", each try GetOpenXmlFileProps([Content]){[Kind="core", Name="lastModifiedBy"]}[Value] otherwise null)
in
    AjoutColonneDernierAuteur

Vu que la fonction dézippe le(s) fichier(s) pour récupérer ces informations, la requête peut être vite lente à l’exécution.

A+

Edit (07/01/23) - mise à jour du code de la fonction GetOpenXmlFileProps suite aux différents retours.
 
Dernière édition:

thibaut_excel

XLDnaute Nouveau
Bonjour et bonne année a tous et a toutes.

Merci pour vos réponses, ca fais super plaisir.

j'ai hâte de tester cette solution, cependant a ta lecture "mromain" je me pose certaine question concernant l'application de cette méthode. Pourrais tu m'expliquer comme tu intègres la requête "GetOpenXmlFileProps".
Je débute sur Excel et power Query.

merci d'avance ;).

 

mromain

XLDnaute Barbatruc
Bonjour Thibaut,

Il te faut créer une requête vide. Par défaut elle devrait se nommer Requête1. Il faut la renommer GetOpenXmlFileProps. Ensuite, dans l’éditeur avancé, il faut remplacer le code par défaut par le code fourni dans le précédent post.
Tu dois alors te retrouver avec cette requête (de type fonction).
Image1.png

Tu peux ensuite faire appel à cette fonction, comme avec les exemples précédemment fournis.

A+
 

thibaut_excel

XLDnaute Nouveau
Bonjour mromain,

Je doit être nul... , je fait un récap histoire d'être bien sur.

J'ai un fichier Excel avec 3 feuilles:
1672925723026.png

Le premier(Suivi des Visas) concatène les informations issue depuis les feuilles 01-RECU et 02-TRAITE.
les feuilles 01-RECU et 02-TRAITE sont des feuilles qui font référence a des requêtes qui lisent 2 dossiers diffèrent sur un réseau.
1672926195332.png


Il te faut créer une requête vide. Par défaut elle devrait se nommer Requête1. Il faut la renommer GetOpenXmlFileProps. Ensuite, dans l’éditeur avancé, il faut remplacer le code par défaut par le code fourni dans le précédent post.
Tu dois alors te retrouver avec cette requête (de type fonction).

Pour crée la requête vide je dois bien aller dans l'éditeur Power Query, mais ensuite je ne vois pas bien comment crée une requête vide. Si je fais un clique droit au niveau des requêtes, je peux crée une nouvelle requête mais je n'ai pas la possibilité d'en avoir une vide.
1672927812268.png


merci d'avance.
 

Hasco

XLDnaute Barbatruc
Repose en paix
bonjour,
Si je fais un clique droit au niveau des requêtes, je peux crée une nouvelle requête mais je n'ai pas la possibilité d'en avoir une vide.
Faut pas exagérer non plus ! Balader votre souris sur les différents sous-menu est si fatigant ? Ou avez-vous peur qu'elle explose ?
J'ai vraiment du mal avec ce manque de curiosité. Franchement j'arrive pas à m'y faire !
 

thibaut_excel

XLDnaute Nouveau
Ok super j'ai crée la requête vide, renommé la requête "GetOpenXmlFileProps", insérer le code fournis plus haut:
1672932487831.png


j'arrive enfin au bout avec les 2 exemples fourni:

Exemple 1 - extraire les propriétés d’un fichier Open XML (emplacement du fichier à adapter) :
Code:
let
EmplacementFichierOpenXml = "C:\Dossier\FichierOpenXml.docx",
FichierBinaire = File.Contents(EmplacementFichierOpenXml),
PropriétésOpenXml = GetOpenXmlFileProps(FichierBinaire)
in
PropriétésOpenXml

Exemple 2 - extraire le nom du dernier auteur sur une liste de fichiers (emplacement du dossier à adapter) :
Code:
let
EmplacementDossier = "C:\Dossier",
FichiersDuDossier = Folder.Files(EmplacementDossier),
AjoutColonneDernierAuteur = Table.AddColumn(FichiersDuDossier, "Dernier auteur", each try GetOpenXmlFileProps([Content]){[Kind="core", Name="lastModifiedBy"]}[Value] otherwise null)
in
AjoutColonneDernierAuteur

Vu que la fonction dézippe le(s) fichier(s) pour récupérer ces informations, la requête peut être vite lente à l’exécution.

Tu dois alors te retrouver avec cette requête (de type fonction).
Image1.png


Tu peux ensuite faire appel à cette fonction, comme avec les exemples précédemment fournis.
je fais ensuite appel ensuite au fonction.
en changeant le répertoire.
1672933308109.png

La fonction appelée fonctionne j'ai bien les noms du dernier auteur dans une colonne dédiée.
Cependant j'ai quelque lignes ou j'ai un retour de Power Query avec une info "null".
j'ai vérifier toutes les lignes qui ne fonctionne pas et c'est toujours le même auteur qui renvoie cette info "null" pourtant quand je vérifie manuellement le nom est bien là, dans les informations du fichier Excel.
le nom en particulier utilise la même structure que les autres noms qui fonctionne(pas de caractères spéciaux).

si vous avez une idée pour cette exception je suis preneur.
 

Pièces jointes

  • 1672996188772.png
    1672996188772.png
    140.4 KB · Affichages: 29

thibaut_excel

XLDnaute Nouveau
Merci pour la solution apporté mon problème, je commençais a désespérer le mois dernier.

Encore merci pour votre pédagogie et votre bienveillance.;)

bonjour,

Faut pas exagérer non plus ! Balader votre souris sur les différents sous-menu est si fatigant ? Ou avez-vous peur qu'elle explose ?
J'ai vraiment du mal avec ce manque de curiosité. Franchement j'arrive pas à m'y faire !
Hasco je vous remercie aussi, même si votre intervention ne m'a pas permis de résoudre mon problème,
m'a souris va bien, merci de vous en inquiété, ce sujet la très certainement usée un peux plus mais elle reste pleinement fonctionnel et opérationnel.
 

mromain

XLDnaute Barbatruc
Cependant j'ai quelque lignes ou j'ai un retour de Power Query avec une info "null".
j'ai vérifier toutes les lignes qui ne fonctionne pas et c'est toujours le même auteur qui renvoie cette info "null" pourtant quand je vérifie manuellement le nom est bien là, dans les informations du fichier Excel.
Bonjour,

En regardant mieux, je suis tombé sur des fichiers qui posaient problème chez moi aussi. Jai mis à jour la fonction et ça a marché.

Code de la fonction GetOpenXmlFileProps mis à jour :
Code:
let
    
    fn = (openXmlFileBinary as binary) as table =>
        let
        
            // unzip function source: https://github.com/ibarrau/PowerBi-code/blob/master/PowerQuery/ExtractZIP.pq
            UnzipFile = (ZIPFile as binary) =>
                let
                    Header = BinaryFormat.Record([      Signature            = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32,ByteOrder.LittleEndian),
                                                        Version         = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),
                                                        Flags             = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),
                                                        Compression         = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),
                                                        ModTime         = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),
                                                        ModDate         = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),
                                                        CRC32             = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32,ByteOrder.LittleEndian),
                                                        CompressedSize         = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32,ByteOrder.LittleEndian),
                                                        UncompressedSize     = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32,ByteOrder.LittleEndian),
                                                        FileNameLen         = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),
                                                        ExtraFieldLen         = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian)
                                                    ]),
                
                    FileEntry = BinaryFormat.Choice(Header, each if _[Signature] <> 0x4034B50 then BinaryFormat.Null else
                                                             BinaryFormat.Record([
                                                                            Header           = _,
                                                                            FileName         = BinaryFormat.Text(_[FileNameLen]),
                                                                            ExtraField       = BinaryFormat.Text(_[ExtraFieldLen]),
                                                                            UncompressedData = BinaryFormat.Transform(BinaryFormat.Binary(_[CompressedSize]),(x) => try Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) otherwise null)
                                                                            ]), type binary),
                    ZipFormat = BinaryFormat.List(FileEntry, each _<> null),    
                
                    Entries = List.Transform(
                            List.RemoveLastN( ZipFormat(ZIPFile), 1),
                        (e) => [FileName = e[FileName], Content = e[UncompressedData] ]
                    )
                in
                   Table.FromRecords(Entries),
        
            UnzipedFileTable = try UnzipFile(openXmlFileBinary) otherwise error "Source file is not an OpenXml file.",
            FilterProperties = let tblProps = Table.SelectRows(UnzipedFileTable, each Text.StartsWith([FileName], "docProps/") and Text.EndsWith([FileName], ".xml")) in if Table.RowCount(tblProps)=0 then error "Source file is not an OpenXml file." else tblProps,
            TransformXmlBinary = Table.TransformColumns(FilterProperties,{{"Content", each Xml.Document(_), type table}}),
            RecoverXmlData = Table.ExpandTableColumn(TransformXmlBinary, "Content", {"Value"}, {"Value"}),
            RecoverProperties = Table.ExpandTableColumn(RecoverXmlData, "Value", {"Name", "Value"}, {"Name", "Value"}),
            RemoveEmptyProperties = Table.SelectRows(RecoverProperties, each (try Table.RowCount([Value]) otherwise 1)>0),
            AddColumnKind = Table.AddColumn(RemoveEmptyProperties, "Kind", each Text.BetweenDelimiters([FileName], "/", "."), type text),
            SelectColumns = Table.SelectColumns(AddColumnKind,{"Kind", "Name", "Value"})
        in
            SelectColumns,
    
    DocumentationMetadata = 
        [
            Documentation.Name = "GetOpenXmlFileProps",
            Documentation.Description = "This function extract properties of an OpenXml file (docx, xlsm, ppts, ...).", 
            Documentation.Examples = 
                {
                    [
                        Description = "Extract properties of a .docx file:",
                        Code = "GetOpenXmlFileProps(File.Contents(""c:\folder\document.docx""))",
                        Result = "Table with properties kind, name and values."
                    ],
                    [
                        Description = "Extract 'last modified by' property of a .xlsm file:",
                        Code = "GetOpenXmlFileProps(File.Contents(""c:\folder\workbook.xlsm"")){[Kind=""core"", Name=""lastModifiedBy""]}[Value]",
                        Result = "Text with 'last modified by' property."
                    ]
                }
        ]

in
    Value.ReplaceType(fn, Value.ReplaceMetadata(Value.Type(fn), DocumentationMetadata))

Si le problème persiste sur tes fichiers, il faudra m’en envoyer un (anonymisé) pour regarder de plus près.

A+
 

Discussions similaires

Statistiques des forums

Discussions
311 735
Messages
2 082 024
Membres
101 873
dernier inscrit
excellllll