CSV to TypeScript Generator

CSV to TypeScript: Zod Validation, Build-Time Type Generation, and Excel Edge Cases

TypeScript interfaces define the shape of CSV data at compile time, but CSV files are runtime input — the actual content can differ from what you expect. The professional pattern combines two layers: a Zod schema for runtime validation (so malformed rows fail fast with useful error messages) and a TypeScript interface derived from that schema (so the rest of your codebase has compile-time type safety). This guide covers typed parsing with PapaParse generics, Zod CSV row validation, Excel-specific edge cases (date serials, BOM, merged cells), and generating TypeScript interfaces from CSV headers programmatically.

Live Example: Typed PapaParse + Zod Validation

// Input CSV (monthly sales export from accounting software)
id,product,category,qty_sold,revenue,sale_date,refunded
1,Widget Pro,Electronics,52,2599.48,2024-03-15,false
2,Cable USB-C,Accessories,138,1103.62,2024-03-15,false
3,Broken Item,,0,0,2024-03-16,true

// 1. Define Zod schema — single source of truth for shape + validation
import { z } from 'zod';

const SaleRowSchema = z.object({
  id:        z.coerce.number().int().positive(),
  product:   z.string().min(1, 'product is required'),
  category:  z.string().optional().default('Uncategorized'),
  qty_sold:  z.coerce.number().int().min(0),
  revenue:   z.coerce.number().min(0),
  sale_date: z.string().regex(/^\d{4}-\d{2}-\d{2}$/, 'expected YYYY-MM-DD'),
  refunded:  z.preprocess(v => String(v).toLowerCase() === 'true', z.boolean()),
});

// 2. Derive TypeScript type from the schema — no duplication
type SaleRow = z.infer<typeof SaleRowSchema>;
// {
//   id: number; product: string; category?: string; qty_sold: number;
//   revenue: number; sale_date: string; refunded: boolean;
// }

// 3. Parse CSV with PapaParse, validate each row with Zod
import Papa from 'papaparse';

interface ParseResult {
  valid:    SaleRow[];
  invalid:  Array<{ row: number; input: unknown; errors: z.ZodError }>;
}

function parseSalesCsv(csvString: string): ParseResult {
  const { data, errors: parseErrors } = Papa.parse(csvString, {
    header:        true,
    skipEmptyLines: true,
    transformHeader: h => h.trim().toLowerCase().replace(/\s+/g, '_'),
  });

  if (parseErrors.length > 0) {
    console.warn('CSV parse errors:', parseErrors);
  }

  const valid:   SaleRow[] = [];
  const invalid: ParseResult['invalid'] = [];

  data.forEach((rawRow, index) => {
    const result = SaleRowSchema.safeParse(rawRow);
    if (result.success) {
      valid.push(result.data);
    } else {
      invalid.push({ row: index + 2, input: rawRow, errors: result.error }); // +2 for header row
    }
  });

  return { valid, invalid };
}

const { valid, invalid } = parseSalesCsv(csvString);

if (invalid.length > 0) {
  invalid.forEach(({ row, errors }) => {
    console.error(`Row ${row}:`, errors.format());
  });
}

// valid is SaleRow[] — fully typed, validated
const totalRevenue = valid.reduce((sum, row) => sum + row.revenue, 0);

Excel Date Serials: The Hidden Edge Case

// Excel stores dates as integers (days since 1900-01-01, with a leap year bug)
// When exporting to CSV, dates may be raw serials (e.g., 45367) instead of "2024-03-15"

// Excel serial → JS Date
function excelSerialToDate(serial: number): Date {
  // Excel incorrectly treats 1900 as a leap year — subtract 1 for dates after Feb 28 1900
  const EXCEL_EPOCH = new Date(1900, 0, 1);  // Jan 1 1900
  const MS_PER_DAY = 86400000;
  return new Date(EXCEL_EPOCH.getTime() + (serial - 2) * MS_PER_DAY);
}

excelSerialToDate(45367).toISOString().slice(0, 10);  // "2024-03-15"

// Detect whether a field is a date serial or a formatted string
function parseSaleDateField(v: unknown): Date {
  if (typeof v === 'number') return excelSerialToDate(v);
  if (typeof v === 'string') {
    const d = new Date(v);
    if (!isNaN(d.getTime())) return d;
  }
  throw new Error(`Cannot parse date: ${v}`);
}

// Zod schema with Excel serial support
const FlexibleDateSchema = z.preprocess(parseSaleDateField, z.date());

// Dealing with BOM (Excel UTF-8 CSV exports start with \xEF\xBB\xBF)
function stripBom(str: string): string {
  return str.startsWith('') ? str.slice(1) : str;
}

const cleanedCsv = stripBom(rawCsvString);

Generating TypeScript Interfaces from CSV Headers

// Build-time code generation: analyze a CSV file and emit TypeScript types
// Useful when CSV schemas are stable and you want zero-cost type checking

import Papa from 'papaparse';
import { readFileSync, writeFileSync } from 'fs';

function inferTsType(value: string): string {
  if (value === '' || value.toLowerCase() === 'null') return 'null';
  if (value.toLowerCase() === 'true' || value.toLowerCase() === 'false') return 'boolean';
  if (!isNaN(Number(value)) && value.trim() !== '') return 'number';
  if (/^\d{4}-\d{2}-\d{2}/.test(value)) return 'string'; // keep dates as string
  return 'string';
}

function generateInterface(csvPath: string, interfaceName: string): string {
  const csv = readFileSync(csvPath, 'utf-8');
  const { data, meta } = Papa.parse(csv, { header: true, preview: 20 });

  const headers = meta.fields ?? [];
  const types: Record<string, Set<string>> = {};

  headers.forEach(h => { types[h] = new Set(); });

  (data as Record<string, string>[]).forEach(row => {
    headers.forEach(h => {
      types[h].add(inferTsType(row[h] ?? ''));
    });
  });

  const lines = headers.map(h => {
    const typeSet = types[h];
    typeSet.delete('null');                           // null → optional modifier
    const isOptional = types[h].has('null') || typeSet.size === 0;
    const typeStr = typeSet.size > 0 ? [...typeSet].join(' | ') : 'string';
    return `  ${h.replace(/\s+/g, '_')}${isOptional ? '?' : ''}: ${typeStr};`;
  });

  return `export interface ${interfaceName} {\n${lines.join('\n')}\n}`;
}

const output = generateInterface('./sales.csv', 'SaleRow');
writeFileSync('./generated/SaleRow.ts', output);
// Generates:
// export interface SaleRow {
//   id: number;
//   product: string;
//   category?: string;
//   qty_sold: number;
//   revenue: number;
//   sale_date: string;
//   refunded: boolean;
// }

Typed Streaming with Node.js Transform Streams

// For large CSV files: typed Transform stream for csv-parse
import { createReadStream } from 'fs';
import { Transform, TransformCallback } from 'stream';
import { parse } from 'csv-parse';
import { z } from 'zod';

class ZodValidatorTransform<T extends z.ZodTypeAny> extends Transform {
  private schema:   T;
  private rowCount: number = 0;
  errorRows: Array<{ row: number; error: z.ZodError }> = [];

  constructor(schema: T, options = {}) {
    super({ ...options, objectMode: true });
    this.schema = schema;
  }

  _transform(chunk: unknown, _: BufferEncoding, callback: TransformCallback) {
    this.rowCount++;
    const result = this.schema.safeParse(chunk);
    if (result.success) {
      this.push(result.data);
    } else {
      this.errorRows.push({ row: this.rowCount, error: result.error });
    }
    callback();
  }
}

const parser    = parse({ columns: true, cast: true, bom: true });
const validator = new ZodValidatorTransform(SaleRowSchema);

createReadStream('./large-sales.csv')
  .pipe(parser)
  .pipe(validator)
  .on('data', (row: SaleRow) => {
    // Each row is fully typed and validated
    console.log(row.product, row.revenue);
  })
  .on('finish', () => {
    console.log(`Errors in ${validator.errorRows.length} rows`);
  });

Best Practices for Production

• Use Zod's coerce for numeric fields: PapaParse's dynamicTyping coerces "52" to 52, but Zod's z.coerce.number() handles it regardless of whether you enable dynamicTyping. It also gives you a clear validation error message when the value isn't numeric.
• Derive TypeScript types from Zod schemas, not separately: Write the Zod schema first, then use z.infer<typeof Schema>. Never write a TypeScript interface and a separate Zod schema by hand — they'll drift apart as the CSV format evolves.
• Handle Excel's 1-based date serial with the leap year bug: Excel mistakenly treated 1900 as a leap year, so all date serials for dates after Feb 28, 1900 are off by one. This bug is preserved for backward compatibility. If you don't account for it, dates will be exactly one day off.
• Use preview: N for header inference only: When auto-generating TypeScript interfaces, only parse the first N rows (20–50 is enough) to determine column types. For large files, reading the whole file just to infer types is unnecessary.

FAQ

Q: How do I handle CSVs with merged/multi-row headers (like Excel pivot exports)?
A: These are the hardest CSV format to parse programmatically. You'll need to read the first 2–3 rows manually, combine them to build the full header, then skip those rows and parse the data rows. PapaParse doesn't support this natively — use a custom step callback or pre-process the CSV string to merge the header rows before passing it to the parser.

Q: When should I validate at parse time vs at API submission time?
A: Both. Validate at parse time to give the user a per-row breakdown of which rows are invalid (useful for a data import UI). Validate at API submission time as a defense layer to ensure nothing slips through. The same Zod schema can serve both validation steps.

Q: Can I infer discriminated union types from a CSV with multiple row types?
A: Yes. If your CSV has a type column with values like order | refund | adjustment, define a Zod discriminated union: z.discriminatedUnion('type', [OrderSchema, RefundSchema, AdjustmentSchema]). Zod routes each row to the correct schema based on the discriminator field value.