Data Export with Claude Code: Streaming CSV and Excel Without Memory Issues

#claudecode #node #typescript #csv

"Download all users as CSV" sounds simple until you have 100,000 rows and the process runs out of memory. Claude Code generates streaming export implementations — cursor-based pagination, CSV injection prevention, and background job handling.

CLAUDE.md for Data Export Standards

## Data Export Rules

### Volume thresholds
- Under 10,000 rows: synchronous response OK
- Over 100,000 rows: background job required (BullMQ)
- Never load all rows into memory — use cursor-based streaming

### CSV specification
- Encoding: UTF-8 with BOM (prevents Excel garbling)
- Line endings: CRLF (Windows default)
- Quoting: double-quote all fields
- Headers: always include, localized labels OK
- Dates: YYYY-MM-DD HH:mm:ss (explicit timezone)

### Security
- CSV injection prevention: prefix =, +, -, @ values with single quote
- Export endpoints require authentication
- Download URLs expire in 15 minutes (signed URLs)

### Performance
- Batch size: 1,000 rows per DB query
- Large files: gzip before delivery
- Streaming output: don't buffer entire response

Streaming CSV Export

Generate a streaming CSV export API endpoint.

Requirements:
- Endpoint: GET /api/exports/users
- Up to 100k user records
- Cursor-based batch fetch (1000 rows at a time) — no full SELECT
- UTF-8 BOM for Excel compatibility
- CSV injection prevention
- Authentication required

Generate:
- src/utils/csvBuilder.ts (CSV utility)
- src/routes/export.ts (endpoint)

Generated:

// src/utils/csvBuilder.ts
export function sanitizeCsvValue(value: unknown): string {
  const str = value === null || value === undefined ? '' : String(value);

  // CSV injection prevention: prefix dangerous first characters
  const dangerous = ['=', '+', '-', '@', '\t', '\r'];
  const sanitized = dangerous.some((c) => str.startsWith(c))
    ? `'${str}`
    : str;

  // Escape double quotes
  return `"${sanitized.replace(/"/g, '""')}"`;
}

export function buildCsvRow(values: unknown[]): string {
  return values.map(sanitizeCsvValue).join(',') + '\r\n';
}

export const CSV_BOM = '\uFEFF'; // UTF-8 BOM

// src/routes/export.ts
router.get('/exports/users', authenticate, async (req, res) => {
  res.setHeader('Content-Type', 'text/csv; charset=utf-8');
  res.setHeader('Content-Disposition', 'attachment; filename="users.csv"');

  res.write(CSV_BOM); // BOM for Excel

  // Header row
  res.write(buildCsvRow(['ID', 'Name', 'Email', 'Created At', 'Role']));

  // Stream rows in cursor-based batches
  let cursor: string | undefined;
  const batchSize = 1000;

  while (true) {
    const users = await prisma.user.findMany({
      take: batchSize,
      ...(cursor && { skip: 1, cursor: { id: cursor } }),
      orderBy: { id: 'asc' },
      select: { id: true, name: true, email: true, createdAt: true, role: true },
    });

    if (users.length === 0) break;

    for (const user of users) {
      if (!res.writable) break; // Client disconnected
      res.write(buildCsvRow([
        user.id,
        user.name,
        user.email,
        user.createdAt.toISOString(),
        user.role,
      ]));
    }

    cursor = users[users.length - 1].id;
    if (users.length < batchSize) break;
  }

  res.end();
});

Background Job for Large Exports

Generate a background job system for large data exports (100k+ rows).

Flow:
1. POST /api/exports → queue BullMQ job → return { jobId }
2. Worker generates CSV → saves to S3 or local storage
3. GET /api/exports/:jobId → { status, progress, downloadUrl? }
4. Download URL expires in 15 minutes (signed URL)

Track progress with: job.updateProgress(processed / total * 100)

// Queue the export job
router.post('/exports', authenticate, async (req, res) => {
  const job = await exportQueue.add('export-users', {
    userId: req.user.id,
    filters: req.body.filters,
    requestedAt: new Date().toISOString(),
  });

  res.status(202).json({ jobId: job.id });
});

// Worker
const exportWorker = new Worker('export-jobs', async (job) => {
  const { filters, userId } = job.data;

  const totalCount = await prisma.user.count({ where: filters });
  let processed = 0;

  const filePath = `/tmp/export-${job.id}.csv`;
  const stream = createWriteStream(filePath);
  stream.write(CSV_BOM);
  stream.write(buildCsvRow(['ID', 'Name', 'Email']));

  let cursor: string | undefined;
  while (true) {
    const batch = await prisma.user.findMany({
      take: 1000,
      ...(cursor && { skip: 1, cursor: { id: cursor } }),
      where: filters,
      orderBy: { id: 'asc' },
    });

    if (batch.length === 0) break;

    for (const user of batch) {
      stream.write(buildCsvRow([user.id, user.name, user.email]));
    }

    processed += batch.length;
    await job.updateProgress(Math.round((processed / totalCount) * 100));
    cursor = batch[batch.length - 1].id;
    if (batch.length < 1000) break;
  }

  stream.end();
  return { filePath }; // Stored for download
});

Excel (.xlsx) Generation

Generate Excel file output using exceljs.

Requirements:
- Header row: bold, blue background
- Column widths: auto-sized
- Date format: YYYY/MM/DD
- Freeze top row (header)
- Up to 1,000 rows
- Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Save to: src/utils/excelBuilder.ts

import ExcelJS from 'exceljs';

export async function generateUsersExcel(users: User[]): Promise<Buffer> {
  const workbook = new ExcelJS.Workbook();
  const sheet = workbook.addWorksheet('Users');

  sheet.columns = [
    { header: 'ID', key: 'id', width: 36 },
    { header: 'Name', key: 'name', width: 20 },
    { header: 'Email', key: 'email', width: 30 },
    { header: 'Created At', key: 'createdAt', width: 20, style: { numFmt: 'YYYY/MM/DD' } },
  ];

  // Style header row
  const headerRow = sheet.getRow(1);
  headerRow.font = { bold: true, color: { argb: 'FFFFFFFF' } };
  headerRow.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF0070C0' } };

  // Freeze header row
  sheet.views = [{ state: 'frozen', ySplit: 1 }];

  users.forEach((user) => sheet.addRow(user));

  return workbook.xlsx.writeBuffer() as Promise<Buffer>;
}