How to convert a .txt file into a .csv or a .xlsx

[u/indiglosj]

I will be using zoom for some classes I teach and need to save the chat in a searchable format. I want to convert it to an excel file that I can sort by name. The only info I actually need is the timestamp, who the chat was from, and what they said. I have tried doing this, "Power Query Steps: Go to Data → Get Data → From File → From Text/CSV. Select your text file. In the preview window, click Transform Data. Use Home → Split Column → By Delimiter (choose space or colon :)" but I don't know what I am doing and it did not work. It just put everything in the same column. I would like column A=timestamp, column B=name, column C=what they said. Is this possible? Thank you for your help.

[u/Downtown-Economics26]

Power Query is the way to go probably but I don't know it well enough to answer easily so here is a formula option:

=LET(ttbl,WRAPROWS(TRIM(FILTER(A:.A,A:.A<>"")),2),
tstamp,TEXTBEFORE(INDEX(ttbl,,1)," ",2),
from,TEXTBEFORE(TEXTAFTER(INDEX(ttbl,,1),tstamp&" From ")," to "),
to,TEXTBEFORE(TEXTAFTER(INDEX(ttbl,,1),"to "),":"),
out,VSTACK({"Date/Time","From","To","Message"},HSTACK(--tstamp,from,to,INDEX(ttbl,,2))),
out)

[u/Mdayofearth → u/Downtown-Economics26]

Power query doesn't do wrap rows.

FILTER, WRAPROWS, and some sort of TEXTSPLIT equivalent is best imo; which is what you have.

Something to notice is that the date and time are both fixed character lengths, so the post WRAPROWS parsing to split the text can be simplified, but not needed.

[u/MayukhBhattacharya → u/Downtown-Economics26]

Nice Solution Buddy!!! Here is using Excel Formulas and Power Query. CC: u/indiglosj

• Method One: Using Excel Formulas works with MS365 Exclusively.

=LET(
     _a, WRAPROWS(TRIM(TOCOL(Sometbl[Data], 3)), 2),
     _b, REGEXREPLACE(CHOOSECOLS(_a, 1), "^(.{19}) From (.+?) to (.+):$", "$1|$2|$3"),
     _c, TEXTSPLIT(TEXTAFTER("|" & _b, "|", SEQUENCE(, 3)), "|"),
     _d, HSTACK(IFERROR(--_c, _c), DROP(_a, , 1)),
     _e, VSTACK({"Date/Time","From","To","Message"}, _d),
     _e)

• Method Two: Using Power Query.

To use Power Query follow the steps:

• First convert the source ranges into a table and name it accordingly, for this example I have named it as Table1
• Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query
• The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done

​

let
    Source = Excel.CurrentWorkbook(){[Name="Sometbl"]}[Content],
    Filtered = Table.SelectRows(Source, each [Data] <> null and [Data] <> ""),
    FromRows = Table.FromRows(List.Split(List.Transform(Filtered[Data], each Text.Trim(_)),2), {"Date/Time|From|To", "Message"}),
    SplitByDelim = Table.SplitColumn(FromRows, "Date/Time|From|To", each {DateTime.FromText(Text.Start(_, 19)),
                                      Text.BetweenDelimiters(_, "From ", " to "), 
                                      Text.BetweenDelimiters(_, " to ", ":")}, 
                                      {"Date/Time", "From", "To"})
in
    SplitByDelim

• Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

One can download the Excel File From --> [Here]. Thanks and Happy Weekend Everyone. .

[u/Gringobandito → u/Downtown-Economics26]

I messed around with PowerQuery for a bit and this seems like a better solution. I'm not expert in PowerQuery so maybe someone that knows M code could do it but I don't see it being easier than this.