POWERQUERY/POWERPIVOT :: Injecter dynamiquement un fichier .csv dans le modèle de données - pratique en cas de forte volumétrie - V0.004

oguruma

XLDnaute Occasionnel
Bonjour,
Pour donner suite à un post dans le forum voici une solution quasi "clef en main" pour charger un fichier dans powerpivot automatiquement via des requêtes powerquery dynamiques et paramétrables. Cela permet aussi de charger des fichiers .csv dépassant la capacité d'accueil d'Excel.
Bien entendu ça n'est qu'un début. Mais déjà le code présent est d'une bonne pédagogie pour les non initiés à Pwq.

On va en premier lieu démarrer d'une table de paramètres comme suit
1718038868144.png


Certains de ces paramètres sont très facilement compréhensibles. Je vais m'attarder sur les cas particuliers.
NB_COLS : à vide, toutes les colonnes sont importées. On peut aussi renseigner le nombre de colonne à retenir en partant de la gauche vers la droite pour la petite précision
ENCODE : pas de raison de le modifier. C'est le type d'encodage. Voir la documentation Pwq si besoin de le modifier
COLONNE_EN_MAJUSCULE : OUI à l'issue de l'importation les noms de colonnes du tableau sont en majuscules
COLONNES_CONSERVER : Après une 1ère importation on peut la relancer en spécifiant la liste (noms) des colonnes à conserver
MISE_EN_BUFFER : Duel entre les performances et la mémoire afin d'accélérer l'importation

Tout ceci est piloté par 3 boutons "macro"

1718039200513.png


[Actualiser la connexion au modèles de données] : Permet d'initialiser la connexion au modèles de données et d'importer le fichier dans PowerPivot
- il est nécessaire d'effectuer cette action à la 1ère création et en cas de spécification des noms de colonnes
[Actualiser les données] : Permet de rafraîchir le modèle de données
- cette action actualise également le modèle de données PowerPivot
1718039398732.png

Permet de connaître les noms des colonnes - afin de les mentionner si besoin pour ne retenir que celles désirées dans le paramètre prévu à cela
Utilisation - 1

Renseigner comme suit ces paramètres avec l'un des fichiers exemple ou les votres
1718039567542.png


puis :
1718039593636.png

Le modèle de données est créé dans PowerPivot comme ceci
1718039636150.png


Cliquer sur Gérer et voici
1718039682454.png


Le modèle Excel comporte deux onglets avec rapport et TCD qui pointent sur le modèle de données. Il suffit ensuite des les alimenter. C'est de l'Excel pur. Votre la documentation sur la création de TCD.
1718039784018.png

Les colonnes du modèle de données se trouve ici
1718039829510.png


A vous de l'alimenter !!!

Sous PowerQuery on trouve ceci
1718039903861.png


La fonction fnGetParameter (déjà présentée dans ce forum) permet de récupérer des paramètres de la table paramètres sous Excel.
La fonction fnGetCSV permet d'importer un fichier .csv

PowerQuery:
let fnGetParameters =
    (pTable as text, pName as any) =>
    let
        ParamSource = Excel.CurrentWorkbook(){[Name=pTable]}[Content],
        ParamRow = Table.SelectRows(ParamSource, each ([PARAMETRE] = pName)),
        Value=
        if Table.IsEmpty(ParamRow)=true
            then null
        else Record.Field(ParamRow{0},"VALEUR")
    in
        Value
in
    fnGetParameters

PowerQuery:
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

La requête dynamique d'importation
PowerQuery:
let
    //---------------------------------------------------------------------------------
    // Requête très simple permettant d'importer un fichier
    // via des paramètres
    //---------------------------------------------------------------------------------

    // On récupère l'option de mise en majuscule des noms de colonnes
    bColumnsToUpperCase=if Text.Trim(Text.Upper(fnGetParameter("TB_PARAMS","COLONNES_EN_MAJUSCULE"))) = "OUI" then true else false,
    bBuffer=if Text.Trim(Text.Upper(fnGetParameter("TB_PARAMS","MISE_EN_BUFFER"))) = "OUI" then true else false,
    ColumnsToKeep=Text.Trim(Text.Upper(fnGetParameter("TB_PARAMS","COLONNES_A_CONSERVER"))),
    bListColumnsToKeep=if ColumnsToKeep is null then false else true,
 
    // On importe le fichier source fourni en paramètre table table TB_PARAMS (Excel)
    Source = if bBuffer
             then Table.Buffer(fnGetCSV(fnGetParameter("TB_PARAMS","FICHIER_CSV_TXT")))
             else fnGetCSV(fnGetParameter("TB_PARAMS","FICHIER_CSV_TXT")),
 
    // Si demandé on renome les noms des colonnes en majuscule
    Source2=if bColumnsToUpperCase
            then  let
                      // Noms d'origine mis dans une liste
                      ListColumnsOrigin=Table.ColumnNames(Source),
                      // Les noms sont mis en majuscule dans autre liste
                      ListColumnsUpper=List.Transform(
                                       ListColumnsOrigin, each Text.Upper(_)
                                       ),
                      // On procède au renommage des noms de colonnes en majuscule
                      TableColumnsToUpper=Table.RenameColumns(Source,List.Zip({ListColumnsOrigin,ListColumnsUpper}))
                  in
                      // et on transmet le résultat
                      TableColumnsToUpper
            else
                  // Si pas de mise en majuscule on renvoie le tableau initial
                  Source,

    // Au final la table à traiter
    ToTable= if bListColumnsToKeep
             then let
                      // On construit la liste des colonnes à conserver
                      ListColumnsToKeep=Text.Split(ColumnsToKeep,";"),
                      // Et on retient cette liste
                      Tb=Table.SelectColumns(Source2,ListColumnsToKeep)
                  in
                      Tb
             else Source2
 
in
    ToTable

La requête permettant de récupérer les noms des colonnes
PowerQuery:
let
    // Cette requête récupère automatiquement les noms des colonnes de la table des données
 
    SourceRQ = fnGetParameter("TB_PARAMS","NOM_REQUETE"),

    //*****************************************************************************************
    // Expression.Evaluate cest le pendant de la fonction INDIRECT sous Excel
    // On récupère ainsi le nom dynamique de la requête source
    // Si celle-ci vient à être modifiée dans les paramètres ça fonctionnera toujours
    //*****************************************************************************************

    EvalSource=Expression.Evaluate(SourceRQ,#shared),
   // Source = RQ_GET_CSV_TXT,
 
    ListColumns=Table.ColumnNames(EvalSource)
in
    ListColumns

Exemple avec une liste de nom de colonnes
1718040327157.png

1718040345914.png

Sous PowerPivot :
1718040307611.png


Exemple sans mise en majuscule
Il faudra bien entendu actualiser les données puis si on souhaite connaître les colonnes actualiser les nom des colonnes
Si on désire sélectionner des colonnes particulières il faudra à nouveau les renseigner dans le champ paramètres spécifique puis actualiser les données.

Enfin si vous désirez renommer les noms de requêtes cette actions sera à effectuer dans cette table paramètres et également dans l'environnement PowerQuery.
Pour les débutants dans ce domaine : s'abstenir :)

1718040416196.png


Les fichiers de données fournis sont public (données .gouv)
Pour chacun de ces fichiers fournis il faudra activer l'ajout au modèle de données que j'ai dû désactiver afin de les charger dans XLD comme suit
1718041472650.png

CLic droit sur la requête
1718041505660.png

Cliquer sur Charger dans
1718041545746.png

Cocher :
1718041565139.png


Malheureusement même zippés je ne peux pas livrer les fichiers Exemples il faudra prendre les votres

Voici les liens cependant avec les fichiers complets et exemples


Le code VBA concernant les boutons

VB:
Private Sub GetDataConnexion()
    Dim wbActive As Workbook
    Dim sWbActive As String
    Dim sCnxRQName As String
    Dim sRQName As String
  
    Set wbActive = ActiveWorkbook
    sWbActive = wbActive.Name
    sRQName = Range("NOM_REQUETE").Value
    sCnxRQName = "Requête - " & sRQName
  
    If isConnectionExists(sCnxRQName) Then
        ActiveWorkbook.Connections(sCnxRQName).Delete
        Workbooks(sWbActive).Connections.Add2 _
            sCnxRQName, _
            "Connexion à la requête « " & sRQName & " » dans le classeur.", _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & sRQName & ";Extended Properties=" _
            , "" & sRQName & "", 6, True, False
    End If
  
    MsgBox "Terminé"
  
End Sub

Private Function isConnectionExists(hRQ As String) As Boolean
  
    Dim vCnx As WorkbookConnection
    Dim v As Connections
  
    isConnectionExists = False
    For Each vCnx In ActiveWorkbook.Connections
        If vCnx.Name = hRQ Then
            isConnectionExists = True
            Exit For
        End If
    Next
  
End Function

Private Sub ActualizeConnection()

    Dim wbActive As Workbook
    Dim sWbActive As String
    Dim sCnxRQName As String
    Dim sRQName As String
  
    Set wbActive = ActiveWorkbook
    sWbActive = wbActive.Name
    sRQName = Range("NOM_REQUETE").Value
    sCnxRQName = "Requête - " & sRQName

    ActiveWorkbook.Connections(sCnxRQName).Refresh
  
    MsgBox "Terminé"
End Sub

Private Sub ActualizeColumnsName()

    Dim wbActive As Workbook
    Dim sWbActive As String
    Dim sCnxRQName As String
    Dim sRQName As String
  
    Set wbActive = ActiveWorkbook
    sWbActive = wbActive.Name
    sRQName = Range("NOM_REQUETE_COLONNES").Value
    sCnxRQName = "Requête - " & sRQName

    ActiveWorkbook.Connections(sCnxRQName).Refresh
    MsgBox "Terminé"
End Sub
 

Pièces jointes

  • PQPowerPivot_0.003.xlsm
    86.3 KB · Affichages: 2
  • PQPowerPivot_0.002.xlsm
    85.9 KB · Affichages: 2
Dernière édition:

oguruma

XLDnaute Occasionnel
V0.004 qui corrige le bouton
1718094240813.png
. En effet la connexion au modèle de données ne se faisait pas systématiquement et on devait la refaire manuellement. Ce n'est désormais plus le cas.

Sécurisation dans la fourniture des noms de colonnes à conserver qui tient compte des espaces et les supprime entre le séparateur ";"

Voici donc la nouvelle configuration livrée dans cette version 0.004

1718094380409.png
 

Pièces jointes

  • PQPowerPivot_0.004.xlsm
    178.5 KB · Affichages: 2
  • EffectifsLyceesPro3.zip
    240.5 KB · Affichages: 2

Discussions similaires

Statistiques des forums

Discussions
313 198
Messages
2 096 142
Membres
106 504
dernier inscrit
ngomez