📧 mbox-to-excel

Turn a Gmail .mbox export into a clean Excel spreadsheet — you choose the columns.

Upload a Google Takeout .mbox, pick standard email fields and/or define custom regex rules to pull values out of the body, optionally filter by date and add a recipient report, then download an .xlsx.

---

✨ Features

• Upload .mbox by drag-and-drop or file browse (up to 100 MB).
• Standard columns — From, To, Cc, Bcc, Subject, Date, Message-ID, In-Reply-To, Reply-To, Gmail Labels, Gmail Thread-ID, plain-text & HTML body, attachment names & count.
• Custom regex rules — extract a value from each email (order ID, password, token, …) into its own column, with a live tester that shows how many emails match and sample captured values as you type.
• Date filter — limit the export to an inclusive date range.
• Recipient Summary — an optional second sheet that groups by the To address and counts how many emails each recipient received, with first/last dates.
• One .xlsx out — Sheet 1 = your columns, Sheet 2 = the recipient report.
• Private by design — files are parsed in memory for the session and cleared after 30 minutes; nothing is written to a database.

🚀 Quick start

git clone https://github.com/<you>/mbox-to-excel.git
cd mbox-to-excel
npm install
npm run dev          # open http://localhost:3000

Production:

npm run build && npm start

📥 Getting an .mbox from Gmail

1. Go to Google Takeout.
2. Deselect all, then select Mail.
3. (Optional) Choose All Mail data included → Select labels to export just one label.
4. Create the export and download the .mbox file — that's what you upload here.

🧭 How to use

1. Upload the .mbox — the app parses it and shows the email count plus a preview of the first few messages.
2. Pick columns — tick the standard fields you want.
3. (Optional) Custom rules — add a regex rule; watch the live tester confirm what it captures.
4. (Optional) Date filter + recipient report — set a range and/or enable the summary sheet.
5. Generate Excel — download the .xlsx.

🔍 Custom rules cookbook

Each rule has a Column name, a Source (which part of the email to search), a Regex pattern, and a Group number.

The value written to the cell is the text captured by the group — the part of your pattern inside parentheses ( ). Group selects which group (default 1; 0 = the whole match). If your pattern has no ( ) group, the whole match is used automatically.

Goal	Source	Pattern	Group	Result
Order ID in the body	Body (text)	Order\s*#?\s*(\d+)	1	12345
Value after a label (handles full-width ：)	Body (text)	ID[:：]\s*([A-Za-z0-9]+)	1	fukuoka0144
Password after a Japanese label	Body (text)	仮パスワード[:：]\s*([A-Za-z0-9]+)	1	nguf0khu
A token from a URL	Body (text)	token=([0-9a-f-]+)	1	d09eacf8-…

Sources: Body (text), Body (HTML), Subject, From, To, Cc. Flags are optional (i, m, s, u, y); the global g flag is ignored (only the first match is used).

📤 Output

Sheet	Contents
Emails	One row per email; one column per selected field, then one per custom rule.
Recipient Summary (optional)	One row per unique To address: To email · Count · First date · Last date, sorted by count.

The date filter, when set, applies to both sheets.

🛠 Tech stack

• Next.js (App Router) + TypeScript — one full-stack app
• Tailwind CSS + shadcn/ui — lightweight UI
• mailparser — decode MIME emails
• ExcelJS — build the workbook
• Zod — validate requests (shared client/server contracts)
• Vitest — unit tests for the domain logic

🧱 Architecture

Business logic is kept out of the framework. Everything under src/lib/ is pure and framework-agnostic (no Request/Response, no React); the Next.js route handlers and React components are a thin shell on top, which keeps the logic unit-testable and reusable.

src/
├── app/
│   ├── page.tsx                  # renders the client flow
│   └── api/
│       ├── upload/route.ts       # POST: parse mbox → cache → fields + preview
│       ├── extract/route.ts      # POST: build xlsx from cached emails → download
│       └── test-rule/route.ts    # POST: run one rule → match count + samples
├── components/
│   ├── ui/                       # shadcn/ui primitives
│   └── extractor/                # ExtractorFlow, UploadDropzone, ColumnPicker,
│                                 #   CustomRules (live tester), HowToUse
└── lib/
    ├── mbox/                     # split.ts (message boundaries) + parse.ts (mailparser)
    ├── extract/                  # fields, rules, filter, aggregate, build-sheets
    ├── excel/build.ts            # multi-sheet workbook builder (exceljs)
    ├── session/                  # SessionStore interface + in-memory impl (TTL)
    ├── schemas/extract.ts        # zod contracts (types inferred, shared client/server)
    └── http.ts                   # consistent JSON helpers
tests/                            # Vitest unit tests + sample.mbox fixture

🌐 API reference

Endpoint	Body	Returns
POST /api/upload	multipart/form-data with file	{ sessionId, availableFields, count, preview }
POST /api/extract	{ sessionId, selectedFields, customRules, dateFrom?, dateTo?, includeSummary }	.xlsx download
POST /api/test-rule	{ sessionId, rule }	{ total, matched, samples }

All parsing/Excel routes run on the Node.js runtime.

⚙️ Scripts

Script	What it does
npm run dev	Start the dev server
npm run build / npm start	Production build / serve
npm test	Run the Vitest unit tests
npm run typecheck	tsc --noEmit
npm run lint	ESLint
npm run format	Prettier write

📝 Notes & limits

• Privacy: the session store is in-memory with a 30-minute TTL — no database, no persistence.
• Deployment: in-memory sessions are correct for a single-instance / self-hosted deploy. On serverless or multi-instance hosting (e.g. Vercel), memory isn't shared across invocations — swap the SessionStore for a temp-file or Redis implementation (the interface is already in place).
• Upload limit is 100 MB; files are parsed in memory.
• Regex safety: an invalid pattern yields a blank cell rather than crashing the export; the g flag is stripped and pattern length is capped as a basic ReDoS safeguard.

🗺 Roadmap ideas

• Multiple captures per email (one row per match)
• Save/load column presets
• CSV output alongside .xlsx
• Streaming parse + persistent store for very large files / serverless

📄 License

MIT