Forum Discussion

Maria Baker's avatar
Maria Baker
Copper Contributor
Jul 16, 2020
Solved

Need to convert a text file to Excel - not the typical comma delineated file

I have a text file that needs to be converted to Excel however the text is grouped in a fashion that Excel doesn't not convert into columns but exactly as it appears in the text file.  The text conte...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 21, 2020

    Maria Baker 

    IMHO, it's not necessary to change delimiters, in general it's not necessary to make any changes in your text file. For the delimiters you may use split only on left most delimiter, that won't affect the dates. 

     

    Variant of layout as TheAntony  suggested is in TextTransformTwo query attached.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 27, 2020

Maria Baker , you are welcome. Please ask if there are more questions.

Yamin0724's avatar
Yamin0724
Copper Contributor
Jun 12, 2021

SergeiBaklan  Hello! I have been reading up and down this thread since the past two days, as I too have a similar file that I am continuously failing to format. I tried reading into the query steps of the files you guys have provided and apply to my files failing to do so. Could you please help me/ guide me in any way? I am attaching the format of my files below.
I copied the same data multiple times just as an example but this is how the information is going to be laid out for me in a text file. Initially it was the equals to sign '=' instead of the ';' but i replaced it to see if it worked lol. I also tried removing the white spaces. Any kind of help would be highly appreciated! 


                     Linkset index  ;  14

                      Linkset name  ;  GP National

               Link selection mask  ;  B1111

                Adjacent DSP index  ;  14

                          DSP name  ;  GP National

         International network DPC  ;  000000H

International reserved network DPC  ;  000000H

              National network DPC  ;  0011D8H

     National reserved network DPC  ;  000000H

                               OPC  ;  001278H

                          STP flag  ;  False

            Linkset selection mask  ;  B1111

                 Master/Slave type  ;  Master

 

 

                     Linkset index  ;  14

                      Linkset name  ;  GP National

               Link selection mask  ;  B1111

                Adjacent DSP index  ;  14

                          DSP name  ;  GP National

         International network DPC  ;  000000H

International reserved network DPC  ;  000000H

              National network DPC  ;  0011D8H

     National reserved network DPC  ;  000000H

                               OPC  ;  001278H

                          STP flag  ;  False

            Linkset selection mask  ;  B1111

                 Master/Slave type  ;  Master

 

 

 

 

                     Linkset index  ;  14

                      Linkset name  ;  GP National

               Link selection mask  ;  B1111

                Adjacent DSP index  ;  14

                          DSP name  ;  GP National

         International network DPC  ;  000000H

International reserved network DPC  ;  000000H

              National network DPC  ;  0011D8H

     National reserved network DPC  ;  000000H

                               OPC  ;  001278H

                          STP flag  ;  False

            Linkset selection mask  ;  B1111

                 Master/Slave type  ;  Master

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 12, 2021

    Yamin0724 

    Better to have sample of txt file, I'm not sure that text copy/pasted from web has the same format as text file.

    If the goal is to receive table like

    the script could be

    let
    sep = ";",  // change on actual one
    file = Excel.CurrentWorkbook(){[Name="filepath"]}[Content]{0}[Column1],
    Source = Table.FromColumns({Lines.FromBinary(File.Contents(file), null, null, 1252)}),
    #"Clean text" = Table.SelectRows(Source, each ([Column1] <> "" and [Column1] <> " ")),
    #"Split it" = Table.SplitColumn(
        #"Clean text",
        "Column1",
        Splitter.SplitTextByDelimiter(sep, QuoteStyle.Csv),
        {"Name", "Value"}
    ),
    #"Trim it" = Table.TransformColumns(
        #"Split it",
        {
            {"Name", Text.Trim, type text},
            {"Value", Text.Trim, type text}
        }
    ),
    // in next formula after it generated by "Group By" change in formula bar
    // each _, [...] on each _[Value]
    #"Grouped Rows" = Table.Group(#"Trim it", {"Name"}, {{"Columns", each _[Value]}}),
    // below is manually created step, hope formula is self-explained
    #"Create Table" = Table.FromColumns(#"Grouped Rows"[Columns], #"Grouped Rows"[Name])
in
    #"Create Table"

    Please check steps in attached file.

    If you mean something else please submit sample text file and Excel with manually created mock-up of the table into which it shall be transformed.

Resources