MBOX to CSV

A mailbox archive is a wall of text you can only read one message at a time. A spreadsheet is the same mail as a table you can sort, count, filter and hand to somebody else — how many messages came from one address, what arrived in the week that matters, which thread ran longest. That is the whole job here: five columns — from, to, date, subject, size — and one row per message.

The bodies are left out by default, and the reason is practical rather than squeamish. Reading them means reading the whole archive rather than its headers, so a large export takes a while; the file gets very large; and a spreadsheet of forty thousand full email bodies is not a thing anybody can usefully open. Switch them on when the point is the text — a review, a search, a disclosure — and leave them off when the point is the pattern. When they are on, a body longer than 32,000 characters is cut there and marked with an ellipsis, because Excel refuses a cell holding more than 32,767 and would reject the row rather than shorten it.

The quoting is where a CSV of email usually goes wrong. Every one of these columns can hold the characters that break a naive writer: a recipient list is commas by definition, a display name is often written Reyes, Dana, subjects contain quotation marks, and once bodies are included almost every row has line breaks in it. So each field goes through the same RFC 4180 writer the rest of this site uses — a field is wrapped in quotes only when it contains a comma, a quotation mark or a line break, and a quotation mark inside a quoted field is doubled. A field with a newline in it stays one cell, and the row does not split.

The date column is normalised. A Date header is written in the sender’s own time zone and spelled a dozen different ways, which sorts as nonsense in the one column everybody sorts on — so it is converted to YYYY-MM-DD HH:MM:SS in UTC, which sorts correctly as text in every spreadsheet. A header that cannot be parsed at all is passed through exactly as it was written rather than replaced by a guess.

size is the message in bytes as the archive stores it: headers, text and any attachment still encoded, but not the separator line above it. It is the column that finds the message that is filling your quota.

Everything above depends on the archive being cut into messages correctly, which is the hard part of the format and is described in full on the viewer: an mbox marks each message with a line beginning From and a space, bodies contain such lines too, and the rule used here checks four things rather than one before it accepts a line as a separator. Sender and subject are decoded on the way out, so a subject that travels encoded — =?UTF-8?B?…?= — arrives in the sheet as words rather than as its encoding.

Press Load a sample, then Build the spreadsheet. Three rows under a header row, and the second one is the one to look at:

Accounts <accounts@example.org>,"you@example.com, dana@example.com",2024-03-05 14:31:00,Re: The March invoice,365

The recipient field is in quotation marks and nothing else is. That message went to two people, the two addresses are separated by a comma, and without the quotes a spreadsheet would read that row as six columns and put the date under subject. The other fields have nothing in them that needs quoting, so they are left plain — which is what makes the file readable when you open it in a text editor to check it.

The date reads 2024-03-05 14:31:00. In the archive it is Tue, 5 Mar 2024 14:31:00 +0000, and the third message is Wed, 6 Mar 2024 08:02:00 +0000 — sorted as written, Tue comes before Wed only by luck, and a month later the order collapses entirely. Sorted as converted, it cannot.

Now turn Put the body in a column of its own on and build it again. A sixth column appears, and the first row’s body is wrapped in quotes because it runs to several lines — open the file in a spreadsheet and it is still one cell with line breaks inside it, not three broken rows. The third message’s body contains a comma as well, and is quoted for that reason alone.

On a real Takeout export the same three rows become forty thousand, and the difference the switch makes is a 4 MB file that builds instantly against a 900 MB one that takes a few minutes. If what you want is to read the messages rather than count them, use the viewer; if you want them as individual files you can open in a mail client, split the archive.