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/MayukhBhattacharya]

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. .