Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

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

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

soit

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



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



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


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



ç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
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

POWERQUERY :: Lire et transformer un fichier .ini à la sauce Windows en Table PowerQuery et l'exploiter
Réponses
0
Affichages
366
oguruma
POWERQUERY :: RANKING - Classement par index - Classement par valeur
Réponses
0
Affichages
354
oguruma
POWERBI :: RANKING - Classement par index - Classement par valeur
Réponses
0
Affichages
268
oguruma
POWERQUERY :: RANKING - Une méthode
Réponses
0
Affichages
455
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
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
POWERQUERY :: Renommage statique de colonnes - sélection de colonnes - données internes au classeur Excel
Réponses
3
Affichages
338
oguruma
POWERQUERY :: Charger des modules/fonctions à partir du disque et les exécuter à l'issue - Function.Invoke
Réponses
0
Affichages
348
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
POWERQUERY :: Importation d'un fichier comportant des enregistrements de longueur fixe et les découper selon une structure définie
Réponses
0
Affichages
190
oguruma
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…