POWERQUERY :: Combiner tous les fichiers Excel d'un dossier ou combiner des fichiers Excel spécifiques

oguruma

XLDnaute Occasionnel
Ce poste fait suite à celui-ci : Combiner des .csv
Ici nous allons combiner des fichiers Excel. Le principe est quasi identique à celui concernant des fichiers .csv.
L'assistant est aussi bien pratique pour les 1ers galops PowerQuery sauf qu'il en met un peu trop à mon gout :).

L'environnement de travail et de paramétrage

1706016929811.png


Environnement PowerQuery

1706017318815.png


Je ferai abstraction sur l'assistant. Pour autant son code source est dans le fichier joint.

Import unitaire d'un fichier Excel

PowerQuery:
let
    //------------------------------------------------------------------------------------------------
    // On importe le tableau structuré du fichier Excel
    //------------------------------------------------------------------------------------------------
    Source = Excel.Workbook(File.Contents("D:\DATA\98__SOURCES__DONNEES\Data_Ventes\Ventes\EXCEL\Ventes_2020.xlsx"), null, true),
    TB_VENTES_Table = Source{[Item="TB_VENTES",Kind="Table"]}[Data],
    #"Type modifié" = Table.TransformColumnTypes(TB_VENTES_Table,{{"Date", type date}, {"Client", type text}, {"Produit", type text}, {"Vendeur", type text}, {"Montant", Int64.Type}})
in
    #"Type modifié"
  
let
    //------------------------------------------------------------------------------------------------
    // On importe le tableau structuré du fichier Excel
    //------------------------------------------------------------------------------------------------
    Source = Excel.Workbook(File.Contents("D:\DATA\98__SOURCES__DONNEES\Data_Ventes\Ventes\EXCEL\Ventes_2021.xlsx"), null, true),
    TB_VENTES_Table = Source{[Item="TB_VENTES",Kind="Table"]}[Data],
    #"Type modifié" = Table.TransformColumnTypes(TB_VENTES_Table,{{"Date", type date}, {"Client", type text}, {"Produit", type text}, {"Vendeur", type text}, {"Montant", Int64.Type}})
in
    #"Type modifié"

On remarque que le code est assez simple et facile à reproduire avec un autre fichier. Pour autant cela peut se transformer en fonction paramétrable.

Fonction fnGetEXCEL
PowerQuery:
let fnGetEXCEL = (pFile as text, optional ppItem as text, optional ppKind as text) as table =>

    //---------------------------------------------------------------------------------
    // Imporatation d'un fichier Excel à partir d'un dossier
    //---------------------------------------------------------------------------------

    let

        //---------------------------------------------------------------------------------
        // Lecture des paramètres
        //---------------------------------------------------------------------------------
        pKind=if ppKind is null then "Sheet" else ppKind,
        pItem=if ppItem is null then "Feuil1" else ppItem,
        pFolder=fnGetParameter("TB_PARAMS","CHEMIN_EXCEL"),

        //---------------------------------------------------------------------------------
        // Le combine nécessite une liste de table à combiner
        //---------------------------------------------------------------------------------
        pPath=pFolder & "\" & pFile,

        //---------------------------------------------------------------------------------
        // Importation du fichier
        //---------------------------------------------------------------------------------
        Source = Excel.Workbook(File.Contents(pPath), null, true),
        ToTable=Source{[Item=pItem,Kind=Text.Proper(pKind)]}[Data],
        Promote=if Text.Upper(pKind)="SHEET" then
                   Table.PromoteHeaders(ToTable, [PromoteAllScalars=true])
                   else if Text.Upper(pKind)="TABLE" then
                        ToTable
                        else Source

    in
        Promote
in
    try fnGetEXCEL otherwise null

Utilisation de la fonction fnGetEXCEL

On invoque soit une table soit un onglet. La distinction s'effectue via le paramètre Kind et Item le nom de l'objet que l'on souhaite récupérer :
- Source = fnGetEXCEL ("Ventes_2020.xlsx","TB_VENTES", "Table"), (pour une table)
- Source = fnGetEXCEL ("Ventes_2020.xlsx","Query Data", "Sheet"), (pour un onglet)

PowerQuery:
let
    //------------------------------------------------------------------------------------------------
    // On importe le tableau structuré du fichier Excel
    //------------------------------------------------------------------------------------------------
    Source = fnGetEXCEL ("Ventes_2020.xlsx","TB_VENTES", "Table"),   
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Client", type text}, {"Produit", type text}, {"Vendeur", type text}, {"Montant", Int64.Type}})
in
    #"Type modifié"

let
    //------------------------------------------------------------------------------------------------
    // On importe le tableau structuré du fichier Excel
    //------------------------------------------------------------------------------------------------
    Source = fnGetEXCEL ("Ventes_2020.xlsx","Query Data", "Sheet"),   
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Client", type text}, {"Produit", type text}, {"Vendeur", type text}, {"Montant", Int64.Type}})
in
    #"Type modifié"

Combinaison manuelle des fichiers Excel - via une table

PowerQuery:
let
    //---------------------------------------------------------------------------------
    // On appel directement les tables présentes dan l'environnement powerquery
    // Tables sources présentes dans le fichier Excel
    //---------------------------------------------------------------------------------
    Source = Table.Combine({T_EXCEL_1, T_EXCEL_2})
in
    Source

Combinaison manuelle des fichiers Excel - via un onglet

PowerQuery:
let
    //---------------------------------------------------------------------------------
    // On appel directement les tables présentes dan l'environnement powerquery
    // Combine sur les feuilles (onglets) présents dans les fichiers Excel
    //---------------------------------------------------------------------------------
    Source = Table.Combine({T_QueryData_1, T_QueryData_2})
in
    Source

Combinaison manuelle en précisant le dossier

PowerQuery:
let
    FIELD_CUSTOM="ContentField",
    pFolder="D:\DATA\98__SOURCES__DONNEES\Data_Ventes\Ventes\EXCEL",
    pFilter=null,
    Source = Folder.Files(pFolder),
    Filter = if pFilter is null then
                Source
                else Table.SelectRows(Source, each Text.StartsWith([Name], pFilter)),
    Lst_1={"Name", "Data", "Item", "Kind", "Hidden"},
    Lst_2={"Name.bis", "Data", "Item", "Kind", "Hidden"},
    AddCustomContentField = Table.AddColumn(Filter, FIELD_CUSTOM, each Excel.Workbook([Content], true)),
    ExpandCustomContentField = Table.ExpandTableColumn(AddCustomContentField, FIELD_CUSTOM, Lst_1, Lst_2),   
    ExpandToTable=Table.Combine(ExpandCustomContentField[Data])
in
    ExpandToTable

Si on souhaite obtenir un fichier particulier du dossier

PowerQuery:
let
    //------------------------------------------------------------------------------------------------
    // On importe le tableau structuré du fichier Excel
    //------------------------------------------------------------------------------------------------
    Source = fnGetEXCEL ("xVentes.xlsx"),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Client", type text}, {"Produit", type text}, {"Vendeur", type text}, {"Montant", Int64.Type}})
in
    #"Type modifié"

Appel à une fonction fnGetFolderExcelFiles personnalisée pour importer un dossier Excel

PowerQuery:
let
    pPath=fnGetParameter("TB_PARAMS","CHEMIN_EXCEL"),
    pFilter=fnGetParameter("TB_PARAMS","FILTRE"),
    Source = fnGetFolderExcelFiles(pPath,pFilter)
in
   Source

Code de la fonction fnCombineExcelFiles
PowerQuery:
let fnCombineExcelFiles=(pFolder as text, optional pFilter as text) as table =>
    //-----------------------------------------------------------------------------------
    // Importation de fichiers Excel contenus dans un dossier
    //-----------------------------------------------------------------------------------
    let
        FIELD_CUSTOM="ContentField",

        //-----------------------------------------------------------------------------------
        // Dossier des fichiers Excel
        //-----------------------------------------------------------------------------------
        Source = Folder.Files(pFolder),

        //-----------------------------------------------------------------------------------
        // Eventuellement on peut filtrer sur les noms de fichiers
        //-----------------------------------------------------------------------------------
        Filter = if pFilter is null then
                    Source
                    else Table.SelectRows(Source, each Text.StartsWith([Name], pFilter)),

        //-----------------------------------------------------------------------------------
        // Pour le renommage des noms de colonnes techniques
        //-----------------------------------------------------------------------------------
        Lst_1={"Name", "Data", "Item", "Kind", "Hidden"},
        Lst_2={"Name.bis", "Data", "Item", "Kind", "Hidden"},

        //-----------------------------------------------------------------------------------
        // On récupère les données binaires dans une colonne spécifique
        //-----------------------------------------------------------------------------------
        AddCustomContentField = Table.AddColumn(Filter, FIELD_CUSTOM, each Excel.Workbook([Content], true)),

        //-----------------------------------------------------------------------------------
        // Renommage des colonnes techniques
        //-----------------------------------------------------------------------------------
        ExpandCustomContentField = Table.ExpandTableColumn(AddCustomContentField, FIELD_CUSTOM, Lst_1, Lst_2),   

        //-----------------------------------------------------------------------------------
        // On déploie les données binaires des fichiers Excel
        //-----------------------------------------------------------------------------------
        ExpandToTable=Table.Combine(ExpandCustomContentField[Data])
       
    in
        ExpandToTable
in
    fnCombineExcelFiles

Fonction personnalisée mixte capable soit d'importer un dossier .csv soit un dossier Excel

PowerQuery:
let
    //---------------------------------------------------------------------------------
    // Appel de la fonction permettant de combiner plusieurs fichiers spécifiques
    // Ces fichiers sont définis dans la table des paramètres
    //---------------------------------------------------------------------------------
    Source=fnCombineCSVExcelByList("TB_PARAMS", "LISTE_FICHIERS_EXCEL", "EXCEL", "TB_VENTES", "Table")
in
    Source

PowerQuery:
let
    //---------------------------------------------------------------------------------
    // Appel de la fonction permettant de combiner plusieurs fichiers spécifiques
    // Ces fichiers sont définis dans la table des paramètres
    //---------------------------------------------------------------------------------
    Source=fnCombineCSVExcelByList("TB_PARAMS", "LISTE_FICHIERS")
in
    Source

Fonction fnCombineCSVExcelByList
De la manière qu'un fichier CSV cette fonction permet d'importer des fichiers au cas par cas qu'il soit CSV ou qu'il soit EXCEL

PowerQuery:
let
    //---------------------------------------------------------------------------------
    // Fonction permettant de combiner plusieurs fichiers spécifiques dans un dossier
    // Ce dossier et ces fichiers sont définis dans une table paramètres
    // Soit EXCEL
    // Soit CSV
    //---------------------------------------------------------------------------------

    // La table des paramètres et le paramètre contenu les fichiers à combiner
    fnCombineExcelByList = (pTableParams as text, pParam as text, optional ppType as text, optional ppItem as text, optional ppKind as text) as table =>
   
    let
        //-------------------------------------------------------------------
        // Permet de gérer soit un import CSV soit un import EXCEL
        //-------------------------------------------------------------------
        pType=if ppType is null then "CSV" else ppType,
        LstFiles=Text.Split(fnGetParameter(pTableParams,pParam),";"),   // Liste des fichiers à combiner
        LstCombine=List.Accumulate(
                        LstFiles,            // Initialisation de la boucle sur la liste à traiter
                        #table({},{}),       // Initialisation de l'accumulateur sur une table vide
                        (state,current) =>   // Boucle de traitement pour combiner les fichiers un à un
                            let
                                //------------------------------------------------------
                                // Aiguillage soit EXCEL soit CSV
                                //------------------------------------------------------
                                Source = if Text.Upper(pType)="EXCEL" then
                                         fnGetEXCEL(current, ppItem, ppKind)     // On va chercher un fichier Excel
                                         else if Text.Upper(pType)="CSV" then
                                                 fnGetCSV(current)               // On va chercher un fichier CSV
                                                 else null,                      // Sinon on prend le parti de ne rien renvoyer

                                Combine = Table.Combine( {state , Source})       // Combinaison du fichier en cours avec ceux déja combinés dans state
                            in                           
                            Combine   // Ensemble des fichiers combinés
                        )     
    in
        LstCombine
in
    fnCombineExcelByList

Fonction fnCombineExcelByList d'importation spécifique uniquement pour les fichiers Excel

PowerQuery:
let
    //---------------------------------------------------------------------------------
    // Fonction permettant de combiner plusieurs fichiers spécifiques dans un dossier
    // Ce dossier et ces fichiers sont définis dans une table paramètres
    //---------------------------------------------------------------------------------

    // La table des paramètres et le paramètre contenu les fichiers à combiner
    fnCombineExcelByList = (pTableParams as text, pParam as text, optional ppItem as text, optional ppKind as text) as table =>
   
    let
        LstFiles=Text.Split(fnGetParameter(pTableParams,pParam),";"),   // Liste des fichiers à combiner
        LstCombine=List.Accumulate(
                        LstFiles,            // Initialisation de la boucle sur la liste à traiter
                        #table({},{}),       // Initialisation de l'accumulateur sur une table vide
                        (state,current) =>   // Boucle de traitement pour combiner les fichiers un à un
                            let
                                Source = fnGetEXCEL(current, ppItem, ppKind),     // Importation du fichier en cours
                                Combine = Table.Combine( {state , Source})  // Combinaison du fichier en cours avec ceux déja combinés dans state
                            in                           
                            Combine   // Ensemble des fichiers combinés
                        )     
    in
        LstCombine
in
    fnCombineExcelByList

Et appel de la fonction fnCombineExcelByList
PowerQuery:
let
    //---------------------------------------------------------------------------------
    // Appel de la fonction permettant de combiner plusieurs fichiers spécifiques
    // Ces fichiers sont définis dans la table des paramètres
    //---------------------------------------------------------------------------------
    Source=fnCombineExcelByList("TB_PARAMS","LISTE_FICHIERS_EXCEL","TB_VENTES", "Table")
in
    Source
 

Pièces jointes

  • FilesCombineEXCEL_V0.096.xlsx
    97.4 KB · Affichages: 4
  • EXCEL.zip
    96.2 KB · Affichages: 4
Dernière édition:

Discussions similaires

Statistiques des forums

Discussions
313 198
Messages
2 096 141
Membres
106 505
dernier inscrit
ngomez