GETTING THE DATA OUT / 7 MIN READ
Exporting YouTube comments to Excel, without the garbage characters.
The file our exporter writes is UTF-8 with no byte-order mark. Excel sees an undeclared encoding, guesses your machine's legacy codepage, and turns curly quotes and emoji into mojibake. One import dialog fixes it — and it is worth knowing why before you blame the file.
A comment export is five columns and one row per comment: text_plain, author_name, like_count, reply_count, published_at. That is small enough to understand completely, which is the useful part — almost every problem people hit with YouTube comments in Excel comes from three specific properties of that shape, not from Excel being difficult.
The short answer
Open the CSV through Excel's import dialog, not by double-clicking: Data → From Text/CSV, set File Origin to 65001: Unicode (UTF-8), keep Delimiter on Comma, then Load. Double-clicking skips the encoding question entirely, and Excel's default answer is your system codepage — on most Windows machines that is 1252, which cannot represent a single character of what is in the file.
If the destination is Google Sheets, none of this applies. Sheets assumes UTF-8, so File → Import → Upload works on the same file with no settings at all. Excel is the odd one out here, not the file.
Why a double-click produces mojibake
A CSV has nowhere to declare its encoding. There is no header block, no metadata, no schema — just bytes and commas. So the reader has to guess, and Excel's guess is the ANSI codepage of the machine unless the first three bytes are a UTF-8 byte-order mark.
Our exporter writes UTF-8 without that mark, which is the right choice for anything that reads the file programmatically: a byte-order mark is invisible in Excel but arrives in Python's csv reader as an extra invisible character glued to the first header cell, so the column you asked for is not the column you get. The cost is that Excel's zero-configuration path reads the file with the wrong table.
The same rows from the same file, opened two ways.
“Best video I’ve seen — 10/10 🔥 I waited three weeks for thÃs one
“Best video I’ve seen — 10/10 🔥 I waited three weeks for thís one
Nothing is corrupted. The bytes on disk are correct; the reader applied the wrong character table on the way in. That is why the fix is a setting rather than a repair — and why re-exporting the file will never change anything.
Four ways in, in order of how much they break
- Excel 365 or 2021 — Data → From Text/CSVPick the file, then set File Origin to
65001: Unicode (UTF-8)and Delimiter to Comma. The preview repaints as you change the setting: if the accented characters look right there, they will be right in the sheet.What to do: Choose Load, not Load To — a real table is what makes pivot tables and structured references behave later. - Older Excel — Data → New Query → From File → From CSVPower Query detects UTF-8 on its own in most cases, which is why this route survives where double-clicking does not. Same file, same bytes, different reader.What to do: Close & Load. If the query editor previews the words as question-mark boxes, set the file origin in the source step before loading.
- Google Sheets — File → Import → UploadSeparator type: Detect automatically. There is no encoding dropdown to get wrong, because Sheets assumes UTF-8 by default.What to do: For a large export this is the slowest part of the whole workflow; import once and keep working in the sheet rather than re-importing.
- If you must keep double-clicking — convert onceImport correctly one time, then save as
.xlsx. Every later open reads the workbook, not the CSV, so the encoding question stops coming up for that data set.What to do: Do not 'fix' the CSV by prepending a BOM in a text editor unless you control every consumer of that file — the next script that reads it will get a BOM in its first column name.
What is actually in the five columns
| Column | Type | What to expect |
|---|---|---|
text_plain | text | The comment as it appears on the page. It can contain line breaks, commas, quotes and emoji — CSV quoting keeps all of it inside one cell, so a three-line comment stays one row. |
author_name | text | The handle YouTube returned at export time. A later rename does not rewrite an old file. |
like_count | integer | 0 means zero likes, not missing data. Sorting ascending is a quick way to surface the comments nobody read. |
reply_count | integer | How many replies the parent has. It is the only trace of a thread in this file — see the next section. |
published_at | ISO 8601 text | 2026-10-21T08:12:33Z, always UTC. Excel will not recognise that shape as a date; the fix is two sections down. |
There is no comment ID and no permalink column, deliberately: a long URL on every row pushes the text off screen, and the video ID is already in the file name (youtube-comments-<video_id>.csv). What you give up is jumping from a row back to the comment.
Replies are rows, not threads
When replies are included, each one becomes its own row, written immediately after its parent. There is no column that says which comment a reply belongs to. That keeps the file flat — one row, one comment — which is exactly what pivot tables and pandas.read_csv want, but it means a thread cannot be reconstructed from the export.
Two things still work without that column: filtering on reply_count > 0 finds the parents that had a conversation, and row order is a usable hint because replies always follow the comment they answer. If you need real thread structure, an export is the wrong artefact — read the thread on the page instead.
Making published_at sort by month
2026-10-21T08:12:33Z is ISO 8601 with a T separator and a Z for UTC. Excel's date parser does not accept that shape, so the column lands as text. Alphabetical sorting happens to keep chronological order because the format is fixed-width — but grouping by month, or subtracting two timestamps, needs a real date value.
| What you want | Formula for row 2, with the timestamp in `E2` |
|---|---|
| Date only | =DATE(LEFT(E2,4),MID(E2,6,2),MID(E2,9,2)) |
| Time of day | =TIME(MID(E2,12,2),MID(E2,15,2),MID(E2,18,2)) |
| Both, as one sortable value | Add the two helper columns: =F2+G2 |
As a single expression the combined formula runs to nearly a hundred characters — long enough to be unreadable inside a cell and to get truncated when you paste it elsewhere. Two helper columns are easier to check and easier to reuse. The Z means UTC, not your timezone: these formulas keep UTC, so apply the offset deliberately rather than letting the reader guess.
Five analyses this shape is built for
| Question | How to get it | Why it works on this file |
|---|---|---|
| Who comments most | Pivot: rows author_name, values Count of text_plain | One row per comment means a count is literally a count |
| What lands with people | Sort like_count descending | Likes sit on the row; no join, no lookup |
| Which comments started arguments | Filter reply_count > 0, sort by reply_count | The parent still carries its reply total even though the replies are flattened |
| How the conversation moved | Convert published_at, then pivot by day | The timestamp is absolute UTC, so daily buckets are stable across exports |
| What people keep mentioning | Filter text_plain contains a word | Substring filter, not a tokenizer — good enough for names, products and phrases |
The first four are pivot tables over one sheet. The fifth is the only one that needs a real tool: a substring filter cannot tell free from freedom.
What the file will not tell you
- Who replied to whom. Flattened, as above — row order is the only clue.
- Hidden or removed comments. They were never in the public thread, so they were never in the export. A missing row is not proof that something was deleted; the five things that remove a comment covers that separately.
- A link back to the source. No permalink column, by design. Keep the file name — the video ID in it is the only pointer home.
- Permanence. The download link expires (24 hours by default). A copy already on your disk keeps working; the link does not.
- Members-only or held-for-review comments. Not part of the public thread at all, so no export setting will surface them.
Paste a public video link, read the thread, then export CSV or JSON. The preview is free; opening the full thread costs 1 credit and the export adds nothing on top — a new account starts with 5.
Open the comment downloaderIs the file missing comments you can see on the page?
An export pages through the thread until YouTube stops handing out pages — but "until it stops" and "all of them" are not always the same thing.
More on getting the data out
- How to download YouTube comments without losing contextA practical path from a public video link to a clean CSV or JSON export, including exactly what ends up in the file and how to check coverage first.
- How to see all the comments on a YouTube videoThe page shows a sample and the scroll has an end. Reading the full thread outside the watch page — and knowing when "all" really is all.