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

[oguruma]

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

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"

[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

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

puis :

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

Cliquer sur Gérer et voici

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.

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

A vous de l'alimenter !!!

Sous PowerQuery on trouve ceci

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

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

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

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

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

Sous PowerPivot :

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

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

CLic droit sur la requête

Cliquer sur Charger dans

Cocher :

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

PQPowerPivot-0.003.xlsm

Le service des pièces jointes, CJoint.com est un service de partage de fichier gratuit pour partager vos documents dans vos courriels, sur les forums ou dans vos petites annonces.

www.cjoint.com

EffectifsLyceesPro.zip

Le service des pièces jointes, CJoint.com est un service de partage de fichier gratuit pour partager vos documents dans vos courriels, sur les forums ou dans vos petites annonces.

www.cjoint.com

fr-esr-atlas-regional-effectifs-d-etudiants-inscrits.zip

Le service des pièces jointes, CJoint.com est un service de partage de fichier gratuit pour partager vos documents dans vos courriels, sur les forums ou dans vos petites annonces.

www.cjoint.com

PQPowerPivot-0.002.zip

Le service des pièces jointes, CJoint.com est un service de partage de fichier gratuit pour partager vos documents dans vos courriels, sur les forums ou dans vos petites annonces.

www.cjoint.com

Le code VBA concernant les boutons

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

 

[oguruma]

V0.004 qui corrige le bouton

. 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