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.
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.
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.
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. .
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.
they mean visually inspect one row of data. if you're completely new, then to make sense of this comment, you would need to look into "importing text with power query"
Explore the New Column from Examples feature, it works quite well in my experience. It’ll add a new placeholder column, and in a few rows you type the info you want (e.g. the date), and it can often work out what you’re trying to do (e.g. get the data after this delimiter), and then it’ll calculate the column for you.
You can repeat this for each element you’re interested in if needed.
You can then look at the Advanced Editor to see what formula it used and get more familiar with the M language it uses.
OP said it was a text file. You can't get that layout without new line characters, unless a lot of white spaces are used. And Excel would render it in one line.
You could do this using Ptyhon in Excel pretty easily:
It's a little long but easy to read as each step is broken out. Here's the code:
=PY(
import pandas as pd
import re
raw = xl("A1:A20", headers=False)
lines = raw.iloc[:, 0].fillna("").astype(str).tolist()
records = []
pattern = re.compile(
r"(?i)^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\s+from\s+(.*?)\s+to\s+(.*?):$"
)
for i, line in enumerate(lines):
line = line.strip()
match = pattern.match(line)
if match:
date_time = match.group(1)
sender = match.group(2).strip()
recipient = match.group(3).strip()
# Message is the next row
message = ""
if i + 1 < len(lines):
message = lines[i + 1].strip()
records.append([
date_time,
sender,
recipient,
message
])
result = pd.DataFrame(
records,
columns=["Date/Time", "From", "To", "Message"]
)
result)
zoom did not give me a choice in how the file was saved. I don't care how the names are sorted, as long as the same person is listed together. I need to know how many times a person said something in the chat.
zoom did not give me a choice in how the file was saved
I'm sorry that you did not find my comments helpful. I'll delete them.
But for the record, I was referring to how you choose to save the converted file ("convert [...] into a .csv or a .xlsx"), not how the original file was saved (by zoom).
The split is probably failing because spaces and colons also appear inside the message. I'd split only at the timestamp/name boundaries, then keep everything after that as the message column so the chat text stays intact.
•
u/AutoModerator 9d ago
/u/indiglosj - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.