How to stop typescript api crashes from excel uploads with a zod schema
- Step 1Reproduce the crash with a real sample — Grab the upload (or a representative one) that broke the API. Parse it:
const rows = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]). Take one row that has all expected columns. - Step 2Generate the baseline schema — Paste that row at /tool/json-to-zod. Set
rootNametouploadRow. Copy thez.object()and itsz.infer<>alias. - Step 3Harden the columns that crashed — Wrap blank-prone columns in
.optional(), swap numeric columns toz.coerce.number(), and date columns toz.coerce.date()so Excel's string/serial quirks are absorbed. - Step 4Add safeParse at the boundary — Before any business logic:
const r = uploadRow.safeParse(row). If!r.success, push to an errors array with the row index — never let it through. - Step 5Return 400 with details, not 500 — Respond with
{ status: 400, errors }listing the failing rows andr.error.flatten(). The client sees actionable validation feedback instead of a stack trace. - Step 6Lock the template if needed — Regenerate with
strictObjects: trueso any unexpected column fails validation — closes the door on tampered or stale templates feeding garbage into your DB.
Crash sources and the Zod fix
Generated structure plus the refinement you add to stop the crash.
| Crash cause | Generated | Your fix |
|---|---|---|
Text N/A in a numeric column | z.number().int() | Keep it strict -> 400, or z.coerce.number() to absorb |
| Blank optional column rejects rows | z.string() | .optional() |
Date arrived as Excel serial 45000 | z.string()/z.number() | z.coerce.date() after fixing the parse |
| Missing required header | field present in schema | safeParse fails -> 400 (this is the catch) |
| Injected extra column | stripped by z.object() | strictObjects: true -> reject |
| Null where value expected | z.null() | z.string().nullable().optional() |
500 vs 400: why safeParse matters
The generator writes no usage code — you choose the call.
| Without validation | With schema.parse() | With schema.safeParse() |
|---|---|---|
| Bad cell -> unhandled exception | Bad cell -> throws ZodError | Bad cell -> { success: false, error } |
| Client sees opaque 500 | Needs try/catch to avoid 500 | Return 400 with field errors directly |
| Hard to debug in prod | Stack trace in logs | Clean, actionable error per row |
Generator options for hardening
Source: lib/json-to-zod.ts. There is no per-Excel options panel.
| Option | Default | Hardening use |
|---|---|---|
strictObjects | false | true -> reject unexpected/injected columns |
rootName | schema | Name it uploadRow for clarity in handlers |
exportAll | true | Keep export for cross-file imports |
indent | 2 | Match your formatter |
Cookbook
Each recipe starts from a real crash, shows the JSON sample, the generated Zod, and the refinement that turns the 500 into a 400. Data anonymised.
Text in a numeric column — the classic 500
A user typed N/A in qty. z.number() rejects it, so safeParse returns a clean failure instead of crashing downstream math.
Crashing row: { sku: "A1", qty: "N/A" }
Generated from a good sample { sku: "A1", qty: 50 }:
export const uploadRow = z.object({
sku: z.string(),
qty: z.number().int()
});
uploadRow.safeParse({ sku: "A1", qty: "N/A" })
-> { success: false, error: 'Expected number, received string' }
-> respond 400, do not run inventory math on "N/A"Coerce away the Excel string-number quirk
Some export pipelines stringify cells. If you want to accept "50" as 50 instead of 400-ing, coerce.
Generated: qty: z.number().int(),
Hardened: qty: z.coerce.number().int(),
uploadRow.safeParse({ sku: "A1", qty: "50" })
-> { success: true, data: { sku: "A1", qty: 50 } }
Text like "N/A" still fails -> still a clean 400.Blank optional column no longer crashes
The sample had a notes value, so it inferred z.string(); a later blank row failed. Add .optional().
Generated: notes: z.string(),
Hardened: notes: z.string().optional(),
Now { sku: "A1", qty: 5 } (no notes) passes safeParse.
The generator never adds .optional() for you — this is the manual step.Reject a tampered upload before it hits the DB
Strict mode turns an injected privileged column into a validation failure.
strictObjects: true ->
export const uploadRow = z.strictObject({
sku: z.string(),
qty: z.number().int()
});
Malicious row { sku: "A1", qty: 1, role: "admin" }:
safeParse -> success:false, 'Unrecognized key: role'
role never reaches db.insert().Per-row error report instead of one 500
Validate every row, collect failures with indices, and return them all in one 400 the client can act on.
const rows = XLSX.utils.sheet_to_json(sheet);
const errors: { row: number; issues: unknown }[] = [];
rows.forEach((row, i) => {
const r = uploadRow.safeParse(row);
if (!r.success) errors.push({ row: i + 2, issues: r.error.flatten() });
});
if (errors.length) return Response.json({ errors }, { status: 400 });
// row i+2 accounts for the header line in the user's spreadsheetEdge cases and what actually happens
Feeding the .xlsx straight to the Zod tool
Not acceptedThe redirect target /tool/json-to-zod takes JSON only. You still parse the upload with SheetJS in your API; the generator just turns one sample row into the schema text.
Expecting the generator to add safeParse examples
By designOutput is the schema, an import, and a z.infer<> alias — no usage snippets. Use safeParse so a bad upload returns 400 rather than throwing a 500. Earlier copy on this page wrongly implied usage comments are generated.
Date cell arrives as an Excel serial number
Coerce after parseExcel stores dates as serial numbers (e.g. 45000). If SheetJS hands you the serial, the schema sees a number — fix it with SheetJS cellDates: true so you get a real Date string, then validate with z.coerce.date().
Schema passes but data still wrong
Add refinementsStructural validation accepts any string in an email column or any number in a percentage column. Add .email(), .min(), .max(), and .refine() for semantic checks the generator cannot infer.
Extra column silently dropped, masking a template change
Strip by defaultDefault z.object() strips unknown keys, so a renamed column passes but its data vanishes from result.data. Use strictObjects: true to surface template drift as a 400.
Optional column not optional in the schema
Add optionalIf your sample row had the column filled, it infers a required type and later blank rows 400. The generator never adds .optional() — add it, or use the tRPC Router Builder where every field is optional.
Invalid JSON pasted from a partial copy
Parse errorJSON.parse throws on a partial object or trailing comma and no schema is produced. Stringify cleanly with JSON.stringify(rows[0], null, 2) and paste that.
Huge upload pasted whole
413 limitJSON to Zod is Pro with a 2 MB free cap. You only need one row to infer the schema; never paste the entire workbook's JSON.
Schema running in a serverless/Edge function
SupportedGenerated Zod has no Node-only APIs, so it validates in Edge, Lambda, Deno Deploy, and Bun. Keep the SheetJS parse step where Node APIs are available.
Number precision beyond Excel's 15 digits
Preserved as stringIf you export long IDs as text to dodge Excel's 15-digit float limit, the schema infers z.string() — correct. If exported as bare numbers, precision may already be lost upstream; validate as z.string() and fix the export.
Frequently asked questions
Will a Zod schema stop my API crashing on bad Excel uploads?
Yes, when you call safeParse at the boundary: a bad cell returns { success: false, error }, so you respond 400 instead of throwing a 500 deep in your logic. Generate the schema at /tool/json-to-zod from a sample row, then add .optional() and coercions.
Can I upload the crashing Excel file to generate the schema?
No — the tool takes a JSON sample. Parse the upload with SheetJS, then paste one row. For direct .xlsx schema generation use the tRPC Router Builder.
parse() or safeParse() to prevent crashes?
safeParse(). It never throws, so an invalid upload becomes a controlled 400. parse() throws a ZodError you would have to catch to avoid a 500.
How do I handle a numeric column that sometimes has text like N/A?
Keep z.number() to reject N/A outright (clean 400), or use z.coerce.number() if numeric strings like "50" should be accepted while real text still fails.
What about Excel dates that come through as serial numbers?
Enable SheetJS cellDates: true so dates parse to JS dates/ISO strings, then validate with z.coerce.date(). The generator itself never detects dates — every date infers z.string().
Does it auto-mark optional columns?
No. It reads one sample value and never adds .optional(). Add it to blank-prone columns yourself, or use the tRPC Router Builder, which wraps every field in .optional().
How do I stop injected or extra columns reaching my DB?
Generate with strictObjects: true (-> z.strictObject()). Any column not in the schema fails safeParse. The default z.object() silently strips extras instead, which can hide template drift.
Will the schema work in serverless and Edge runtimes?
Yes — it is plain Zod with no Node-only APIs, so it runs in Edge, Lambda, Deno, and Bun. Only the SheetJS parsing step needs a full runtime.
Is my upload data uploaded to JAD when generating?
No. The generator runs in your browser; the sample row never reaches a server. Only an anonymous run counter is recorded. Your .xlsx is parsed inside your own API.
What's the free size limit?
JSON to Zod is Pro with a 2 MB free file cap. Since you only paste one representative row, you will not hit it.
How do I report which spreadsheet row failed?
Validate rows in a loop, capturing the index. Add 2 to the index for the human-facing row number (one for zero-based arrays, one for the header line), and return error.flatten() per failing row.
Is there an Excel tool that generates the whole API layer?
Yes — the tRPC Router Builder emits a Zod schema, TypeScript interface, and CRUD procedures from your sheet. For SQL inserts to seed a DB defensively, see Excel to SQL.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.