POWERQUERY :: Récupérer des paramètres à partir d'un fichier texte sur disque et le transformer en table de paramètres

oguruma

oguruma

XLDnaute Occasionnel
Le but de ce post est de démontrer qu'il est possible de lire un fichier texte et de le transformer. Nous connaissons déjà la lecture de fichiers .csv. La technique utilisée repose quelque part sur cette méthode.

La cible est d'obtenir
soit
1707586123488.png


soit
1707586150550.png


5 méthodes sont présentées. D'autres doivent exister certainement.

Méthode 1
Le fichier texte est sous ce format :
valeur du Param1;valeur du Param2;valeur du Param3

Les paramètres sont sur une seule ligne séparés par des ";".
On peut soit produire une table à une colonne soit une table à deux colonnes.

Production d'une table à une colonne

PowerQuery: 
let

/* Exemple
valeur du Param1;valeur du Param2;valeur du Param3
*/
    pCodeName   = "Params",     
    pExtension  = ".ini",
    pFolder      = null,

    ParamPathDefaut="D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__PQ_PARAMS",
    ParamExtDefaut=".ini",
    DEFAULT = ParamPathDefaut & "\",
    BACK_SLASH="\",
    Extension =  if pExtension is null then
                    if ParamExtDefaut is null then
                       ".pq" else
                       ParamExtDefaut
                    else pExtension,
                    
    PathInitial = if (pFolder <> null) then pFolder else DEFAULT,
    PathProcedure = PathInitial & (if Text.End(PathInitial, 1) <> BACK_SLASH then BACK_SLASH else ""),
    FileParam = PathProcedure & pCodeName & Extension,   
  
    Source = Text.FromBinary(Binary.Buffer(File.Contents(FileParam))),     
    ToTableParam = Table.RenameColumns(Table.FromList(Text.Split(Source,";")),{{"Column1", "PARAMETRE"}})

    /*
    Pour accéder au 2ème paramètre de la table soit indice 1
    ToTableParam{1}[PARAMETRE]
    ToTableParam{1} : Renvoie le record (1)
    [PARAMETRE] : Nom de la colonne créée automatiquement au moment de la conversion
    */
        
in
    ToTableParam

Pour accéder aux paramètres c'est un peu particulier et pour simplifier les choses une fonction

PowerQuery: 
(pIndex as number) =>
let
    Param = TbParamFromFileList{pIndex}[PARAMETRE]
in
    Param

Utilisation de la fonction fnGetParamTbParamFromFileList

PowerQuery: 
let
    Param = fnGetParamTbParamFromFileList(1)
in
    Param

1707587133348.png


Production d'une table à deux colonnes

Cependant à partir de cette série de paramètres sur une ligne il est possible de produire une table à deux colonnes comme présentée en introduction.
Voici le code. Pour les débutants qui ne sont pas à l'aise avec la manipulation des listes, ce code est un bon cas d'école ;) à étudier sans modération :)
Pour sa bonne compréhension je vous invite à l'exécuter étape par étape afin de bien comprendre les fonctions List.* utilisées.

PowerQuery: 
let

/* Exemple
valeur du Param1;valeur du Param2;valeur du Param3
*/
    pCodeName   = "Params",      
    pExtension  = ".ini",
    pFolder      = null,

    ParamPathDefaut="D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__PQ_PARAMS",
    ParamExtDefaut=".ini",
    DEFAULT = ParamPathDefaut & "\",
    BACK_SLASH="\",
    Extension =  if pExtension is null then 
                    if ParamExtDefaut is null then 
                       ".pq" else 
                       ParamExtDefaut
                    else pExtension,
                    
    PathInitial = if (pFolder <> null) then pFolder else DEFAULT,
    PathProcedure = PathInitial & (if Text.End(PathInitial, 1) <> BACK_SLASH then BACK_SLASH else ""),
    FileParam = PathProcedure & pCodeName & Extension,    
   
    Source = Text.FromBinary(Binary.Buffer(File.Contents(FileParam))),     
    ListValeurs = Text.Split(Source,";"),
    NbParams   = List.Count(ListValeurs),
    ListNumbers = {1..NbParams},
    ListParametres=List.Transform(ListNumbers, each "PARAMETRE" & Number.ToText(_) ),    
    ToTableListParams=
            Table.FromColumns(
                {
                    ListParametres,
                    ListValeurs
                }, 
                { "PARAMETRE", "VALEUR" }  // specifies column names
            )
in
    ToTableListParams

Le fichier contient un paramètre par ligne comme ceci
valeur du Param1
valeur du Param2
valeur du Param3
valeur du Param4

Nous obtenons une table à une colonne

PowerQuery: 
let

/* Exemple
valeur du Param1;valeur du Param2;valeur du Param3
*/
    pCodeName   = "Params11",      
    pExtension  = ".ini",
    pFolder     = null,
    Sep         = "#(cr)#(lf)",

    ParamPathDefaut="D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__PQ_PARAMS",
    ParamExtDefaut=".ini",
    DEFAULT = ParamPathDefaut & "\",
    BACK_SLASH="\",
    Extension =  if pExtension is null then 
                    if ParamExtDefaut is null then 
                       ".pq" else 
                       ParamExtDefaut
                    else pExtension,
                    
    PathInitial = if (pFolder <> null) then pFolder else DEFAULT,
    PathProcedure = PathInitial & (if Text.End(PathInitial, 1) <> BACK_SLASH then BACK_SLASH else ""),
    FileParam = PathProcedure & pCodeName & Extension,    
   
    Source = Text.FromBinary(Binary.Buffer(File.Contents(FileParam))),     
    ToTableParam = Table.RenameColumns(Table.FromList(Text.Split(Source,Sep)),{{"Column1", "PARAMETRE"}})
        
in
    ToTableParam

On obtient

1707587038523.png


On peut transformer la requête précédente pour obtenir une table à deux colonnes comme ceci

PowerQuery: 
let

/* Exemple
valeur du Param1;valeur du Param2;valeur du Param3
*/
    pCodeName   = "Params11",      
    pExtension  = ".ini",
    pFolder      = null,
    Sep         = "#(cr)#(lf)",

    ParamPathDefaut="D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__PQ_PARAMS",
    ParamExtDefaut=".ini",
    DEFAULT = ParamPathDefaut & "\",
    BACK_SLASH="\",
    Extension =  if pExtension is null then 
                    if ParamExtDefaut is null then 
                       ".pq" else 
                       ParamExtDefaut
                    else pExtension,
                    
    PathInitial = if (pFolder <> null) then pFolder else DEFAULT,
    PathProcedure = PathInitial & (if Text.End(PathInitial, 1) <> BACK_SLASH then BACK_SLASH else ""),
    FileParam = PathProcedure & pCodeName & Extension,    
   
    Source = Text.FromBinary(Binary.Buffer(File.Contents(FileParam))),     
    ListValeurs = Text.Split(Source,Sep),
    NbParams   = List.Count(ListValeurs),
    ListNumbers = {1..NbParams},
    ListParametres=List.Transform(ListNumbers, each "PARAMETRE" & Number.ToText(_) ),    
    ToTableListParams=
            Table.FromColumns(
                {
                    ListParametres,
                    ListValeurs
                }, 
                { "PARAMETRE", "VALEUR" }  // specifies column names
            )
in
    ToTableListParams

On obtient
1707587847387.png


Méthode 2 - avec un délimiteur

PARAM1=valeur du Param1
PARAM2=valeur du Param2
PARAM3=valeur du Param3
PARAM4=valeur du Param4
PARAM5=valeur du Param5

PowerQuery: 
let

/* Exemple
PARAM1;valeur du Param1
PARAM2;valeur du Param2
PARAM3;valeur du Param3
PARAM4;valeur du Param4
PARAM5;valeur du Param5
*/

    pCodeName   = "Params2",      
    pExtension  = ".ini",
    pFolder      = null,
    pDelimiter   = ";",

    ParamPathDefaut="D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__PQ_PARAMS",
    ParamExtDefaut=".ini",
    DEFAULT = ParamPathDefaut & "\",
    BACK_SLASH="\",
    Extension =  if pExtension is null then 
                    if ParamExtDefaut is null then 
                       ".pq" else 
                       ParamExtDefaut
                    else pExtension,
                    
    PathInitial = if (pFolder <> null) then pFolder else DEFAULT,
    PathProcedure = PathInitial & (if Text.End(PathInitial, 1) <> BACK_SLASH then BACK_SLASH else ""),
    FileParam = PathProcedure & pCodeName & Extension,   

    Source =  Csv.Document(File.Contents(FileParam),[Delimiter=pDelimiter]),
    ToTableParam = Table.RenameColumns(Source,{{"Column1", "PARAMETRE"}, {"Column2", "VALEUR"}})
            
        
in
   ToTableParam

1707587932429.png


ça fonctionne aussi avec ce format à condition de changer la valeur du séparateur ";" en "="

"PARAM1"="valeur du Param1"
"PARAM2=valeur du Param2"
PARAM3=valeur du Param3
PARAM4=valeur du Param4
PARAM5=valeur du Param5

pDelimiter = "=",

Accéder aux paramètres de la table - exemple

PowerQuery: 
let
    Source = fnGetParameter(TbGetParamFromFileDelim_1,"PARAM1")
in
    Source

Pour mémoire source de la fonction fnGetParameter

PowerQuery: 
let fnGetParameter =
    (
        pTable  as any, 
        pName   as text
    ) =>

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

Méthode 3 - sous forme de liste

Le fichier paramètres peut être sous ce format
{"PARAMETRE", "VALEUR"},
{
{"PARAM1","STRING 1"},
{"PARAM2",";"},
{"PARAM3",25},
{"PARAM4","16/10/2023"}

}

A partir de ce formation on va construire la table avec le constructeur #table() comme ceci

Source1 = Text.FromBinary(Binary.Buffer(File.Contents(FileParam))), --> on récupère le texte
Source2 = "#table ( " & Source1 & " )", --> on applique le constructeur
ToTable = Expression.Evaluate(Source2, #shared) --> on traduit tout ceci en table

PowerQuery: 
let
/*
{"PARAMETRE", "VALEUR"},
{
   {"PARAM1","STRING 1"},
   {"PARAM2",";"},
   {"PARAM3",25},
   {"PARAM4","16/10/2023"}

}
*/

    pCodeName   = "Params7",      
    pExtension  = ".ini",
    pFolder      = null,

    ParamPathDefaut="D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__PQ_PARAMS",
    ParamExtDefaut=".ini",
    DEFAULT = ParamPathDefaut & "\",
    BACK_SLASH="\",
    Extension =  if pExtension is null then 
                    if ParamExtDefaut is null then 
                       ".pq" else 
                       ParamExtDefaut
                    else pExtension,
                    
    PathInitial = if (pFolder <> null) then pFolder else DEFAULT,
    PathProcedure = PathInitial & (if Text.End(PathInitial, 1) <> BACK_SLASH then BACK_SLASH else ""),
    FileParam = PathProcedure & pCodeName & Extension,   

    Source1 = Text.FromBinary(Binary.Buffer(File.Contents(FileParam))),
    Source2 =  "#table ( " & Source1 & " )",
    ToTable = Expression.Evaluate(Source2, #shared)
            
        
in
   ToTable

Le fichier peut aussi avoir ce format au travers duquel on précise le type des colonnes :
type table[ PARAMETRE = Text.Type, VALEUR = Any.Type ],
{
{"PARAM1", "STRING 1" },
{"PARAM2", ";" },
{"PARAM3", 25 },
{"PARAM4", "16/10/2023" }
}

En fait on applique la syntaxe PowerQuery dans ce fichier

Méthode 4 - FromRecord

{
[ PARAMETRE = "PARAM1", VALEUR = "STRING1" ] ,
[ PARAMETRE = "PARAM2", VALEUR = "STRING2" ] ,
[ PARAMETRE = "PARAM4", VALEUR = 25 ] ,
[ PARAMETRE = "PARAM5", VALEUR = "16/10/2023" ] ,
[ PARAMETRE = "PARAM6" ]
},
null,
MissingField.UseNull
//type table[PARAMETRE = Text.Type, VALEUR = Any.Type ]

PowerQuery: 
let

/* Exemple
{  
  [ PARAMETRE = "PARAM1", VALEUR = "STRING1"     ] ,
  [ PARAMETRE = "PARAM2", VALEUR = "STRING2"     ] ,
  [ PARAMETRE = "PARAM4", VALEUR = 25            ] ,
  [ PARAMETRE = "PARAM5", VALEUR = "16/10/2023"  ] ,
  [ PARAMETRE = "PARAM6"  ]   
}, 
null,
MissingField.UseNull
//type table[PARAMETRE = Text.Type, VALEUR = Any.Type ]

*/

    pCodeName   = "Params9",      
    pExtension  = ".ini",
    pFolder      = null,

    ParamPathDefaut="D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__PQ_PARAMS",
    ParamExtDefaut=".ini",
    DEFAULT = ParamPathDefaut & "\",
    BACK_SLASH="\",
    Extension =  if pExtension is null then 
                    if ParamExtDefaut is null then 
                       ".pq" else 
                       ParamExtDefaut
                    else pExtension,
                    
    PathInitial = if (pFolder <> null) then pFolder else DEFAULT,
    PathProcedure = PathInitial & (if Text.End(PathInitial, 1) <> BACK_SLASH then BACK_SLASH else ""),
    FileParam = PathProcedure & pCodeName & Extension,
    
    Source1 = Text.FromBinary(Binary.Buffer(File.Contents(FileParam))),
    Source2 =  "Table.FromRecords ( " & Source1 & " )",
    ToTable = Expression.Evaluate(Source2, #shared)
            
        
in
   ToTable

Ou comme ceci - avec un paramètre non valorisé

{
[ PARAMETRE = "PARAM1", VALEUR = "STRING1" ] ,
[ PARAMETRE = "PARAM2", VALEUR = "STRING2" ] ,
[ PARAMETRE = "PARAM4", VALEUR = 25 ] ,
[ PARAMETRE = "PARAM5", VALEUR = "16/10/2023" ] ,
[ PARAMETRE = "PARAM6", VALEUR = null]
},

type table[PARAMETRE = Text.Type, VALEUR = Any.Type ]

Méthode 5 - au format Json

{
"PARAM1"
:"VAL1",
"PARAM2"
:"VAL2",
"PARAM3"
:25,
"PARAM4"
:"VAL4"
}

Il aurait été dommage de passer à côté de ce format que PowerQuery est capable de lire.

PowerQuery: 
let

/* Exemple
valeur du Param1;valeur du Param2;valeur du Param3
*/
    pCodeName   = "ParamJson2",      
    pExtension  = ".json",
    pFolder      = null,

    ParamPathDefaut="D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__PQ_PARAMS",
    ParamExtDefaut=".ini",
    DEFAULT = ParamPathDefaut & "\",
    BACK_SLASH="\",
    Extension =  if pExtension is null then 
                    if ParamExtDefaut is null then 
                       ".pq" else 
                       ParamExtDefaut
                    else pExtension,
                    
    PathInitial = if (pFolder <> null) then pFolder else DEFAULT,
    PathProcedure = PathInitial & (if Text.End(PathInitial, 1) <> BACK_SLASH then BACK_SLASH else ""),
    FileParam = PathProcedure & pCodeName & Extension,
    
    Source = Json.Document(File.Contents(FileParam)),    
    ToTable = Table.RenameColumns(Record.ToTable(Source),{{"Name", "PARAMETRE"}, {"Value", "VALEUR"}})
in
    ToTable
 

Pièces jointes

  • GetParamsAndLoad_V0.38.xlsx
    27.1 KB · Affichages: 6
  • $__PQ_PARAMS.zip
    3 KB · Affichages: 5

Discussions similaires

oguruma
POWERQUERY :: Lire et transformer un fichier .ini à la sauce Windows en Table PowerQuery et l'exploiter
Réponses
0
Affichages
366
oguruma
oguruma
oguruma
POWERQUERY :: RANKING - Classement par index - Classement par valeur
Réponses
0
Affichages
354
oguruma
oguruma
oguruma
POWERBI :: RANKING - Classement par index - Classement par valeur
Réponses
0
Affichages
266
oguruma
oguruma
oguruma
POWERQUERY :: RANKING - Une méthode
Réponses
0
Affichages
455
oguruma
oguruma
oguruma
POWERQUERY :: Simuler la lecture séquentielle d'un fichier texte comme en VBA - While Not Eof(1) ... Wend
Réponses
2
Affichages
408
oguruma
oguruma
oguruma
POWERQUERY :: Lancement de requêtes à tiroir - dynamiques - paramétrables - code des requêtes sur disque - Exemple de mise en oeuvre
Réponses
0
Affichages
541
oguruma
oguruma
oguruma
POWERQUERY :: Renommage statique de colonnes - sélection de colonnes - données internes au classeur Excel
Réponses
3
Affichages
337
oguruma
oguruma
oguruma
POWERQUERY :: Charger des modules/fonctions à partir du disque et les exécuter à l'issue - Function.Invoke
Réponses
0
Affichages
347
oguruma
oguruma
oguruma
POWERQUERY :: Récupérer les attributs d'un fichier, Parcourir un dossier, Récupérer le contenu d'un fichier texte, Vérifier la présence d'un fichier
Réponses
0
Affichages
307
oguruma
oguruma
oguruma
POWERQUERY :: Importation d'un fichier comportant des enregistrements de longueur fixe et les découper selon une structure définie
Réponses
0
Affichages
189
oguruma
oguruma

Membres actuellement en ligne

Aucun membre en ligne actuellement.
2 membres et 53 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
315 087
Messages
2 116 082
Membres
112 653
dernier inscrit
flapynot7x