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

[oguruma]

L'idée de ce post de fournir un ensemble de fonctions permettant de combiner des fichiers d'un dossier ou éventuellement ne combiner que certains fichiers bien précis en les passant en paramètres.

Présentation de l'environnement d'étude

La table de paramètres

Environnement PowerQuery

Importation simplifiée d'un fichier .csv

let
    //---------------------------------------------------------------------------------
    // Exemple d'importation d'un fichier .csv non dynamique
    //---------------------------------------------------------------------------------
    Source = Csv.Document(File.Contents("D:\DATA\98__SOURCES__DONNEES\__BDD__RH\BDDRH\CSV\BDDRH_1.csv"),[Delimiter=";", Columns=11, Encoding=1250, QuoteStyle=QuoteStyle.None]),
    #"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    #"En-têtes promus"

Importation des deux tables T1 et T2

let
    //---------------------------------------------------------------------------------
    // Requête très simple permettant d'importer un fichier
    // via des paramètres
    //---------------------------------------------------------------------------------
    Source = fnGetCSV(fnGetParameter("TB_PARAMS","FICHIER_1"))

in
    Source

let
    //---------------------------------------------------------------------------------
    // Requête très simple permettant d'importer un fichier
    // via des paramètres
    //---------------------------------------------------------------------------------
    Source = fnGetCSV(fnGetParameter("TB_PARAMS","FICHIER_2"))

in
    Source

Ces deux importations font appel à la fonction fnGetCSV

let fnGetCSV = (pFile as any) as table =>

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

    let

        //---------------------------------------------------------------------------------
        // Lecture des paramètres
        //---------------------------------------------------------------------------------
        pFolder=fnGetParameter("TB_PARAMS","CHEMIN_CSV"),
        pNbCols=fnGetParameter("TB_PARAMS","NB_COLS"),
        pDelim=fnGetParameter("TB_PARAMS","DELIMITEUR"),
        pEncoding=fnGetParameter("TB_PARAMS","ENCODE"),

        //---------------------------------------------------------------------------------
        // Paramètres techniques d'importation d'un fichier .csv
        //---------------------------------------------------------------------------------
        pParamCSV=[Delimiter=pDelim, Columns=pNbCols, Encoding=pEncoding, QuoteStyle=QuoteStyle.None],

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

        //---------------------------------------------------------------------------------
        // Importation du fichier
        //---------------------------------------------------------------------------------
        Source = Csv.Document(File.Contents(pPath),pParamCSV),

        //---------------------------------------------------------------------------------
        // Titre des colonnes
        //---------------------------------------------------------------------------------
        ToTable = Table.PromoteHeaders(Source, [PromoteAllScalars=true])

    in
        ToTable
in
    try fnGetCSV otherwise null

Combinaison des deux tables T1 et T2

let
    //---------------------------------------------------------------------------------
    // Combinaison de deux fichiers
    // On passe par des variables intermédiaires
    //---------------------------------------------------------------------------------
    T1=T_T1,
    T2=T_T2,
    //---------------------------------------------------------------------------------
    // Le combine nécessite une liste de table à combiner
    //---------------------------------------------------------------------------------
    Source = Table.Combine({T1,T2})
in
    Source

autres méthodes

let
    //---------------------------------------------------------------------------------
    // On appel directement les tables présentes dan l'environnement powerquery
    //---------------------------------------------------------------------------------
    Source = Table.Combine({T_T1, T_T2})
in
    Source

let
    //---------------------------------------------------------------------------------
    // Combinaison de deux tables spécifiques déclarées dans la table des paramètres
    //---------------------------------------------------------------------------------

    //---------------------------------------------------------------------------------
    // Pour cela on fait appel à une fonction spécifique fnGETCSV
    //---------------------------------------------------------------------------------

    Source1 = fnGetCSV(fnGetParameter("TB_PARAMS","FICHIER_1")),
    Source2 = fnGetCSV(fnGetParameter("TB_PARAMS","FICHIER_2")),

    //---------------------------------------------------------------------------------
    // On prépare la liste des fichiers à combiner dans une liste
    //---------------------------------------------------------------------------------
    LstCombine={Source1, Source2},

    //---------------------------------------------------------------------------------
    // Combinaison des fichiers via cette liste
    //---------------------------------------------------------------------------------
    Combine = Table.Combine(LstCombine)
 
in
    try Combine otherwise null

Dans les méthodes ci-dessus on précise nommément les noms de fichiers. Certes ils sont passés en paramètres.

Nous allons voir ci-dessous comment importer le contenu d'un dossier. Il y a bien certes les assistants mais c'est un peu comme l'enregistreur de Macros Excel ils en mettent un peu trop pour faire des choses très simples. Voici donc une manière sommaire (critiquable peut-être) de réduire le code et de passer par la bufferisation des binaires qui seront lus directement.

Le code principal de cette requête fait appel à cette fonction fnGetFromFolder

let
    //-------------------------------------------------------------------------------------
    // Combinaison des fichiers contenus dans un dossier
    //-------------------------------------------------------------------------------------

    //-------------------------------------------------------------------------------------
    // On récupère les paramètrs
    //-------------------------------------------------------------------------------------
    pPath=fnGetParameter("TB_PARAMS","CHEMIN_CSV"),
    pDelim=fnGetParameter("TB_PARAMS","DELIMITEUR"),
    pEncoding=fnGetParameter("TB_PARAMS","ENCODE"),
 
    //-------------------------------------------------------------------------------------
    // Fonction permettant de combiner les fichiers présents dans un dossier
    //-------------------------------------------------------------------------------------
    Source = fnGetFromFolder(pPath, pDelim, pEncoding)
in
    Source

fnGetFromFolder

let fnGetFromFolder = (pPath as text, pDelim as text, pEncoding as number) as table =>

    //---------------------------------------------------------------------------------
    // Combinaison du contenu d'un dossier
    // Ici nous passons pas par le code mis automatiquement quand on fait appel à l'assistant
    // L'importation s'effectue directement via les binaires des fichiers
    //---------------------------------------------------------------------------------

    let
        SOURCE_CONTENT="Content",

        //-------------------------------------------------------------------------------------
        // Fonction pour la lecture des binaires bufferisés
        //-------------------------------------------------------------------------------------
        fnReadBinaryFile=(pFile as binary) as table =>     
            Table.PromoteHeaders( Csv.Document(pFile, [Delimiter=pDelim,Encoding=pEncoding])),
    
        //-------------------------------------------------------------------------------------
        // Définition de la source de données
        //-------------------------------------------------------------------------------------
        Source = Folder.Files(pPath),

        //-------------------------------------------------------------------------------------
        // Bufferisation des binaires
        //-------------------------------------------------------------------------------------
        DrillDownContent = Table.SelectColumns(Source,{SOURCE_CONTENT}),
        ListTransform = List.Transform (DrillDownContent[Content], Binary.Buffer),

        //-------------------------------------------------------------------------------------
        // Transformation et lecture des binaires
        //-------------------------------------------------------------------------------------
        ReadBinaryFiles = List.Transform(ListTransform, fnReadBinaryFile),

        //-------------------------------------------------------------------------------------
        // Combinaison du fichier
        //-------------------------------------------------------------------------------------
        TableCombine = Table.Combine(ReadBinaryFiles )
    in
        TableCombine
in
    try fnGetFromFolder otherwise null

Cas spécifique où ne veut pas importer tous les fichiers du dossier mais seulement certains bien précis

On peut le faire comme montrer en introduction et faire autant de requêtes que de fichiers à importer. Si de nouveaux fichiers sont à prendre en compte il faudra bien entendu faire les requêtes spécifiques et les combiner.

Cependant on peut les décrire dans un paramètre contenant cette liste et faire appel à une boucle en passant par cette fonction puissante List.Accumulate. Il suffit ainsi de faire une requête maître et une fonction d'importation ET le tour est joué !

1ère version de la requête maître

let

    //---------------------------------------------------------------------------------
    // Combinaison de plusieurs fichiers spécifiques présents dans un répertoire
    //---------------------------------------------------------------------------------

    //---------------------------------------------------------------------------------
    // On récupère la liste des fichiers à combiner indiqués dans la table des paramètres
    //---------------------------------------------------------------------------------
    LstFiles=Text.Split(fnGetParameter("TB_PARAMS","LISTE_FICHIERS"),";"),

    //---------------------------------------------------------------------------------
    // On passe par une boucle List.Accumulate
    // Cette boucle va lire le contenu des fichier à combiner dans la liste
    // Etant donné que l'on doit renvoyer une table l'accumulateur doit être initialisé à une table vide
    // puis on combine les fichiers un à un
    //---------------------------------------------------------------------------------
    LstCombine=List.Accumulate(
                    LstFiles,             // liste des fichiers à combiner
                    #table({},{}),        // initialisation de l'accumulateur
                    (state,current) =>    // appel de la fonction interne pour la boucle
                        let
                            Source = fnGetCSV(current),     // On importe le nom du fichier en cours de lecture dans current
                            Combine = Table.Combine( {state , Source})  // Combinaison du fichier aux précédents identifiés par state
                        in                        
                           Combine       // Ensemble des fichiers combinés
                    )  
in
    LstCombine

La fonction d'importation permettant l'importation en boucle : fnCombineByList

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
    fnCombineByList = (pTableParams as text, pParam 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 = fnGetCSV(current),     // 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
    fnCombineByList    // Résultat de la fonction

2ème version de la requête maître

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

Voir le fichier Sources joints ainsi que les fichiers d'exemples utilisés. Dézipper les fichiers de données dans un dossier et changez le paramètre CHEMIN_CSV

Pour vos fichiers : jonglez avec le DELIMITEUR, ENCODE (encodage du fichier) ici c'est UTF-8 ainsi que le nombre de colonnes que comportent vos fichiers NB_COLS.