How to Build a Data Import and Export Pipeline in Next.js

Many business applications need data import and export. Here is how to build a pipeline that handles CSV and Excel files with validation and progress tracking.

Install Dependencies

pnpm add papaparse xlsx zod
pnpm add -D @types/papaparse

Build the Import API

// app/api/import/route.ts
import { NextRequest, NextResponse } from "next/server";
import Papa from "papaparse";
import * as XLSX from "xlsx";
import { z } from "zod";

const RowSchema = z.object({
  name: z.string().min(1, "Name is required"),
  email: z.string().email("Invalid email"),
  phone: z.string().optional(),
  company: z.string().optional(),
});

type ValidRow = z.infer<typeof RowSchema>;

interface ImportResult {
  totalRows: number;
  successCount: number;
  errorCount: number;
  errors: { row: number; field: string; message: string }[];
  imported: ValidRow[];
}

function parseCSV(text: string): Record<string, string>[] {
  const result = Papa.parse<Record<string, string>>(text, {
    header: true,
    skipEmptyLines: true,
    transformHeader: (header) => header.trim().toLowerCase(),
  });
  return result.data;
}

function parseExcel(buffer: ArrayBuffer): Record<string, string>[] {
  const workbook = XLSX.read(buffer, { type: "array" });
  const sheetName = workbook.SheetNames[0];
  if (!sheetName) return [];
  const sheet = workbook.Sheets[sheetName];
  if (!sheet) return [];
  return XLSX.utils.sheet_to_json<Record<string, string>>(sheet, {
    defval: "",
  });
}

function validateRows(rows: Record<string, string>[]): ImportResult {
  const result: ImportResult = {
    totalRows: rows.length,
    successCount: 0,
    errorCount: 0,
    errors: [],
    imported: [],
  };

  for (let i = 0; i < rows.length; i++) {
    const parsed = RowSchema.safeParse(rows[i]);
    if (parsed.success) {
      result.imported.push(parsed.data);
      result.successCount++;
    } else {
      result.errorCount++;
      for (const issue of parsed.error.issues) {
        result.errors.push({
          row: i + 2, // +2 for header row and 0-indexed
          field: issue.path.join("."),
          message: issue.message,
        });
      }
    }
  }

  return result;
}

export async function POST(request: NextRequest) {
  const formData = await request.formData();
  const file = formData.get("file") as File | null;

  if (!file) {
    return NextResponse.json({ error: "No file provided" }, { status: 400 });
  }

  const maxSize = 10 * 1024 * 1024; // 10 MB
  if (file.size > maxSize) {
    return NextResponse.json({ error: "File too large (max 10 MB)" }, { status: 400 });
  }

  let rows: Record<string, string>[];

  if (file.name.endsWith(".csv")) {
    const text = await file.text();
    rows = parseCSV(text);
  } else if (file.name.endsWith(".xlsx") || file.name.endsWith(".xls")) {
    const buffer = await file.arrayBuffer();
    rows = parseExcel(buffer);
  } else {
    return NextResponse.json(
      { error: "Unsupported file type. Use CSV or Excel." },
      { status: 400 }
    );
  }

  if (rows.length === 0) {
    return NextResponse.json({ error: "File is empty" }, { status: 400 });
  }

  if (rows.length > 10000) {
    return NextResponse.json(
      { error: "Too many rows (max 10,000)" },
      { status: 400 }
    );
  }

  const result = validateRows(rows);

  // In production, batch insert result.imported into your database here

  return NextResponse.json(result);
}

Build the Export API

// app/api/export/route.ts
import { NextRequest, NextResponse } from "next/server";
import Papa from "papaparse";
import * as XLSX from "xlsx";

async function fetchData() {
  // Replace with actual database query
  return [
    { name: "Alice", email: "alice@example.com", phone: "555-0100", company: "Acme" },
    { name: "Bob", email: "bob@example.com", phone: "555-0200", company: "Corp" },
  ];
}

export async function GET(request: NextRequest) {
  const format = request.nextUrl.searchParams.get("format") ?? "csv";
  const data = await fetchData();

  if (format === "xlsx") {
    const worksheet = XLSX.utils.json_to_sheet(data);
    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, worksheet, "Export");
    const buffer = XLSX.write(workbook, { bookType: "xlsx", type: "buffer" });

    return new NextResponse(buffer, {
      headers: {
        "Content-Type":
          "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        "Content-Disposition": `attachment; filename="export-${Date.now()}.xlsx"`,
      },
    });
  }

  const csv = Papa.unparse(data);
  return new NextResponse(csv, {
    headers: {
      "Content-Type": "text/csv",
      "Content-Disposition": `attachment; filename="export-${Date.now()}.csv"`,
    },
  });
}

Build the Import UI

"use client";

import { useCallback, useState } from "react";

interface ImportError {
  row: number;
  field: string;
  message: string;
}

interface ImportResult {
  totalRows: number;
  successCount: number;
  errorCount: number;
  errors: ImportError[];
}

export function DataImporter() {
  const [file, setFile] = useState<File | null>(null);
  const [importing, setImporting] = useState(false);
  const [result, setResult] = useState<ImportResult | null>(null);
  const [error, setError] = useState<string | null>(null);

  const handleDrop = useCallback((e: React.DragEvent) => {
    e.preventDefault();
    const dropped = e.dataTransfer.files[0];
    if (dropped) setFile(dropped);
  }, []);

  const handleImport = async () => {
    if (!file) return;
    setImporting(true);
    setError(null);
    setResult(null);

    const formData = new FormData();
    formData.append("file", file);

    try {
      const res = await fetch("/api/import", { method: "POST", body: formData });
      const data = await res.json();

      if (!res.ok) {
        setError(data.error ?? "Import failed");
        return;
      }

      setResult(data);
    } catch {
      setError("Network error. Please try again.");
    } finally {
      setImporting(false);
    }
  };

  return (
    <div className="space-y-6">
      {/* Drop Zone */}
      <div
        onDrop={handleDrop}
        onDragOver={(e) => e.preventDefault()}
        className="border-2 border-dashed rounded-lg p-8 text-center hover:border-primary transition-colors"
      >
        {file ? (
          <div>
            <p className="font-medium">{file.name}</p>
            <p className="text-sm text-muted-foreground">
              {(file.size / 1024).toFixed(1)} KB
            </p>
            <button
              onClick={() => setFile(null)}
              className="text-sm text-red-500 mt-2 hover:underline"
            >
              Remove
            </button>
          </div>
        ) : (
          <div>
            <p className="text-muted-foreground">
              Drag and drop a CSV or Excel file, or{" "}
              <label className="text-primary cursor-pointer hover:underline">
                browse
                <input
                  type="file"
                  accept=".csv,.xlsx,.xls"
                  className="hidden"
                  onChange={(e) => setFile(e.target.files?.[0] ?? null)}
                />
              </label>
            </p>
          </div>
        )}
      </div>

      {/* Import Button */}
      <button
        onClick={handleImport}
        disabled={!file || importing}
        className="px-4 py-2 bg-primary text-primary-foreground rounded-md disabled:opacity-50"
      >
        {importing ? "Importing..." : "Import Data"}
      </button>

      {/* Error Display */}
      {error && (
        <div className="p-4 bg-red-50 text-red-700 rounded-md">{error}</div>
      )}

      {/* Results */}
      {result && (
        <div className="space-y-4">
          <div className="grid grid-cols-3 gap-4">
            <div className="p-4 bg-muted rounded-md text-center">
              <div className="text-2xl font-bold">{result.totalRows}</div>
              <div className="text-sm text-muted-foreground">Total Rows</div>
            </div>
            <div className="p-4 bg-green-50 rounded-md text-center">
              <div className="text-2xl font-bold text-green-700">
                {result.successCount}
              </div>
              <div className="text-sm text-muted-foreground">Imported</div>
            </div>
            <div className="p-4 bg-red-50 rounded-md text-center">
              <div className="text-2xl font-bold text-red-700">
                {result.errorCount}
              </div>
              <div className="text-sm text-muted-foreground">Errors</div>
            </div>
          </div>

          {result.errors.length > 0 && (
            <div>
              <h3 className="font-semibold mb-2">Errors</h3>
              <div className="border rounded-md overflow-hidden">
                <table className="w-full text-sm">
                  <thead className="bg-muted">
                    <tr>
                      <th className="text-left px-4 py-2">Row</th>
                      <th className="text-left px-4 py-2">Field</th>
                      <th className="text-left px-4 py-2">Error</th>
                    </tr>
                  </thead>
                  <tbody>
                    {result.errors.slice(0, 50).map((err, i) => (
                      <tr key={i} className="border-t">
                        <td className="px-4 py-2">{err.row}</td>
                        <td className="px-4 py-2 font-mono">{err.field}</td>
                        <td className="px-4 py-2">{err.message}</td>
                      </tr>
                    ))}
                  </tbody>
                </table>
                {result.errors.length > 50 && (
                  <p className="px-4 py-2 text-sm text-muted-foreground border-t">
                    Showing 50 of {result.errors.length} errors
                  </p>
                )}
              </div>
            </div>
          )}
        </div>
      )}
    </div>
  );
}

Export Buttons Component

"use client";

export function ExportButtons() {
  const handleExport = (format: "csv" | "xlsx") => {
    window.location.href = `/api/export?format=${format}`;
  };

  return (
    <div className="flex gap-2">
      <button
        onClick={() => handleExport("csv")}
        className="px-3 py-2 border rounded-md text-sm hover:bg-muted"
      >
        Export CSV
      </button>
      <button
        onClick={() => handleExport("xlsx")}
        className="px-3 py-2 border rounded-md text-sm hover:bg-muted"
      >
        Export Excel
      </button>
    </div>
  );
}

Need Custom Data Integration Tools?

We build data import/export pipelines, ETL workflows, and CRM integrations for business applications. Contact us to streamline your data workflows.