POWERQUERY :: Lire et transformer un fichier .ini à la sauce Windows en Table PowerQuery et l'exploiter

[oguruma]

Bonjour, comme dit dans le titre à partir d'un fichier .ini au format Windows comme celui-ci

Le transformer en table :

Environnement PowerQuery

Pour cela une petite fonction

let

    fnGetParamWindowsFile = (
                optional ppFileParam   as text,
                optional ppExtension   as text,
                optional ppFolder      as text,
                optional ppDelimiter   as text,
                optional pSection      as text,
                optional pParametre    as text,
                optional pValeur       as text
               
            ) as table =>

        let
            //-----------------------------------------------------------------------------
            // Fonction : Construction de la table finale
            //-----------------------------------------------------------------------------
            BuildFromINI = (
                            pFile            as text,
                            pFieldSection    as text,
                            pFieldParametre  as text,
                            pFieldValeur     as text,
                            pDelim           as text) =>
            let
                Source = Table.FromColumns({Lines.FromBinary(File.Contents(pFile), null, null, 1252)}),
                DelSpace01 = Table.TransformColumns(Source,{{"Column1", Text.Trim, type text}}),
                FilterNull01 = Table.SelectRows(DelSpace01, each ([Column1] <> "") and ([Column1] <> null)),
                DrillColumns1 = FilterNull01[Column1],
                ToTable01 = Table.FromList(DrillColumns1, Splitter.SplitTextByDelimiter(pDelim), null, null, ExtraValues.Error),
                FillDown = Table.FillDown(ToTable01,{"Column2"}),
                FilterNull02 = Table.SelectRows(FillDown, each ([Column1] <> "") and ([Column1] <> null)),
                DelCarEndSection = Table.ReplaceValue(FilterNull02,"]","",Replacer.ReplaceText,{"Column2"}),
                TableToSplitt = Table.SplitColumn(DelCarEndSection, "Column1", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
                DelSpaces = Table.TransformColumns(TableToSplitt,{{"Column1.1", Text.Trim, type any}, {"Column1.2", Text.Trim, type any}, {"Column2", Text.Trim, type any}}),
                ReorgColumns = Table.ReorderColumns(DelSpaces,{"Column2", "Column1.1", "Column1.2"}),
                RenColumns = Table.RenameColumns(ReorgColumns,{{"Column2", pFieldSection}, {"Column1.1", pFieldParametre}, {"Column1.2", pFieldValeur}}),
                TypeColumnsAny = Table.TransformColumnTypes(RenColumns,{{pFieldSection, type any}, {pFieldParametre, type any}, {pFieldValeur, type any}})
            in
                TypeColumnsAny,

            //-----------------------------------------------------------------------------
            // MAIN PROCEDURE
            //-----------------------------------------------------------------------------
            FIELD_SECTION="SECTION",
            FIELD_PARAMETRE="PARAMETRE",
            FIELD_VALEUR="VALEUR",
            PARAMETRE="Parametres",
            SUBFOLDER_PARAMS="Params",
            BACK_SLASH="\",
            CurrentDirectory = Excel.CurrentWorkbook(){[Name="DOSSIER_COURANT"]}[Content]{0}[Column1],
            PARAM_PATH_DEFAULT=CurrentDirectory & BACK_SLASH & SUBFOLDER_PARAMS,        
            PARAM_EXTENSION_DEFAULT = ".ini",
            PARAM_DELIMITER_DEFAULT = "[",  
            PATH_DEFAULT = PARAM_PATH_DEFAULT & BACK_SLASH,

            FieldSection = if pSection <> null then
                     pSection
                     else FIELD_SECTION,

            FieldParametre = if pParametre <> null then
                     pSection
                     else FIELD_PARAMETRE,

            FieldValeur = if pValeur <> null then
                     pSection
                     else FIELD_VALEUR,

            pParam = if ppFileParam <> null then
                        ppFileParam
                        else PARAMETRE,

            Extension =  if ppExtension is null then
                            PARAM_EXTENSION_DEFAULT
                            else ppExtension,

            PathInitial = if ppFolder <> null then
                    ppFolder
                    else PATH_DEFAULT,

            Delimiter = if ppDelimiter <> null then
                        ppDelimiter
                        else PARAM_DELIMITER_DEFAULT,

            PathProcedure = PathInitial & (if Text.End(PathInitial, 1) <> BACK_SLASH then BACK_SLASH else ""),
            FileParam = PathProcedure & pParam & Extension,  




            ToBuild = BuildFromINI(FileParam, FieldSection, FieldParametre, FieldValeur, Delimiter)

        in
            ToBuild
in
    fnGetParamWindowsFile

Pour cela la fonction fnGetParameter a été adaptée pour donner ce nouveau nom fnGetParameterWindows

let fnGetParameterWindows =
    (
        pTable                 as any,
        pValSection            as text,
        pName                  as text,
        optional pSection      as text,
        optional pParam        as text,
        optional pVal          as text
    ) =>

    let
        ParamSource = if pTable is text then
                         Excel.CurrentWorkbook(){[Name=pTable]}[Content]
                         else pTable,

        SectionWin  = if pSection <> null then
                         pSection
                         else "SECTION",

        Parametre   = if pParam <> null then
                         pParam
                         else "PARAMETRE",

        Valeur      = if pVal <> null then
                         pVal
                         else "VALEUR",

        StrParam="each ( [" & SectionWin & "] = " & """"  & pValSection & """"  & " and [" & Parametre & "] = " & """" & pName & """" & " )",
        EvalParam=Expression.Evaluate(StrParam,[ParamSource=ParamSource]),      
       
        ParamRow = Table.SelectRows(ParamSource, EvalParam),
       
        Value= if Table.IsEmpty(ParamRow)=true
                  then null
                  else Record.Field(ParamRow{0},Valeur)
    in
        //Parametre
        Value
        //StrParam
in
    fnGetParameterWindows

Exemple d'utilisation

let
    Source = fnGetParameterWindows(TB_PARAMS_WIN,"DATA","TB_DATA")
in
    Source

Le plus simple étant de tester cela avec les fichiers joints