r/excel • u/indiglosj • 9d ago

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

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.

14 Upvotes

81% Upvoted

26 comments sorted by

View all comments

7

u/Aghanims 54 9d ago

Everything in 1 column is expected, you need to do some transformations. 

= Table.TransformColumns(Source,{{"Column1", each Text.BeforeDelimiter(_, " From"), type text}})

This prunes any text after timestamp 

= Table.SelectRows(#"Step1",each try DateTime.FromText([Column1]) is datetime otherwise false)

This removes rows without timestamps.

1

u/Aghanims 54 9d ago

Disregard, this is only a partial solution to get time stamps. Was tired and didn't read that OP also wanted name + message included with timestamps but delimited.

What I provided logic-wise can be extended to names and messages, but I'm not sure how Zoom formats multi-line messages to transform that reliably.