Add CSV and Excel Import to a Bolt App

Build a small warehouse goods-in app with one page, on Vite, React and
TypeScript.

Use Bolt Database. Create a table named deliveries with these columns, and
leave the table empty.

  delivery_ref     text primary key
  supplier_name    text not null
  carrier          text
  pallets          integer not null
  gross_weight_kg  numeric(10,2) not null
  booked_for       date not null
  dock             text
  status           text
  contact_email    text

The app has no sign-in, so add row level security policies on deliveries that
let the anonymous role select, insert, update and delete. Say in the chat which
policies you created.

Render /deliveries as a page that reads every row through the database client
and shows those nine columns in a table, with the row count above it and an
empty state while the table holds nothing. Redirect / to /deliveries.

Keep the styling plain, and give the app one primary color, #9c4221. No
authentication, no seed data, no extra pages.

Build a CSV and Excel import screen for the deliveries page. A button on that
page opens it as a modal wizard, our customers walk that wizard with the exports
their old warehouse system produced, and it leaves them in a spreadsheet where
they clean what came in.

Where the fields come from
- Read the field list from my code, so the same screen serves the deliveries
  page today and a despatches page later.
- Support text, whole numbers, decimals, dates and a fixed list of options, and
  let one field carry several rules at once.
- Check required, a range, a number of decimal places, uniqueness inside the
  file, and uniqueness against the deliveries we already store.
- Show the customer our label for a field, and hand my code back the field name.
- Take delivery_ref as the key, so a second upload of the same file finds those
  deliveries and updates them.
- Drop columns the file carries beyond that list.

Opening the files
- Take several files at once, by drop or by dialog, and show a card per file.
- Detect the encoding, strip a byte order mark, and read a Windows-1252 export
  without turning accented supplier names into question marks.
- Detect the delimiter. Commas, semicolons, tabs and pipes all arrive.
- Drop a first line shaped sep=; that Excel writes for some locales.
- Read .xlsx, .xls and .ods, open a workbook as one card per sheet, and let the
  customer choose which sheets go on.
- Find the real header row when a report title and an export date sit above it,
  and handle a file with duplicate headers or no header at all.
- Keep a row carrying one field too few, and say which fields it filled.

Reading what the cells hold
- Settle the date order per column, so 04/11/2026 does not become April.
- Read 1.240,50 and 1,240.50 as the same number.
- Keep 00417 as text, including a code Excel already turned into 417.
- Tell an empty cell apart from a cell holding the word null.

Matching the columns
- Map the file's headers onto my field list, one field per column.
- Match the obvious ones on arrival, and show the customer which of my fields
  reached nothing so they can point each one at a column by hand.
- Show a few values under every header, so the customer tells two similar
  columns apart.
- Remember the pairs the customer confirmed, so the next file of that shape
  arrives matched.

Matching the values
- Collect the distinct values of carrier, dock and status as the file spells
  them.
- Match each one to an option we allow, and let the customer place the rest.

Cleaning before the write
- Hand the finished file to a spreadsheet the customer works in, with my labels
  on top and every row in it.
- Mark what the customer changed. A new row, an edited cell and a deleted row
  each read differently at a glance.
- Run every rule the field list carries, and say which cell failed and why.
- Let the customer fix a cell in place, with the editor that fits the field. A
  date opens a calendar, a list opens its options, a number takes digits.
- Sort and filter, so the customer reaches the failing rows among the hundred
  thousand that pass.
- Copy and paste blocks between it and Excel or Google Sheets, and undo an edit.
- Stay smooth at a hundred thousand rows, with the reading off the main thread
  so the tab keeps responding.

Handing the rows over
- Tell me which rows are new, which changed, and which were deleted.
- Keep every row and every mapping on screen when my write to the database
  fails.

Add a spreadsheet import screen to the deliveries page with the npm package
@updog/data-editor. Read https://updog.tech/updog.md and
https://docs.updog.tech first, and use only props documented there.

1. Install @updog/data-editor and import "@updog/data-editor/styles.css".
2. Add an "Import deliveries" button above the table, holding one open state.
   The editor opens as a modal, which is its default: pass open={open} and an
   onClose that closes it. Do not pass mode="inline". Wrap it in nothing and
   give it no height of its own, because the modal sizes itself.
3. Render <DataEditor /> with apiKey="updog-bolt-demo", which is all the key a
   Bolt preview or a bolt.host site needs, variant="uploader",
   primaryKey="delivery_ref", enableDeleteRow="all" and these nine columns,
   written as id, title, then type and rules:
   delivery_ref "Delivery ref" text, required, unique
   supplier_name "Supplier" text, required
   carrier "Carrier" select: Ravenhill Haulage, Copsey Transport,
     Marden Freight, Delwyn Logistics
   pallets "Pallets" number, whole, minimum 1, required
   gross_weight_kg "Gross weight" number, two decimals, minimum 0, required
   booked_for "Booking date" date, required
   dock "Dock door" select: Dock 1, Dock 2, Dock 3
   status "Status" select: Booked, Arrived, Unloaded
   contact_email "Contact email" text, email
   The ids are the deliveries table's own column names on purpose, so no field
   is renamed between the editor and the database.
4. In a stylesheet loaded after "@updog/data-editor/styles.css", set
   --updog-brand on :root to this app's primary color, written as a plain color
   value.
5. onComplete receives result.sources, and every entry in a source's rows is a
   wrapper shaped { row, isNew, isChanged, isDeleted, isValid }. Read the four
   flags off the wrapper and the cell values off entry.row. Skip an entry whose
   isValid is false.
6. entry.row is keyed by those same ids, so send it to the database as it is.
   Write no mapper and no Number call. Postgres casts a text parameter to the
   column's own type.
7. Write with the database client this project already has. Send the new and
   changed rows through one upsert on deliveries with delivery_ref as the
   conflict target, and delete the removed ones by delivery_ref. Both calls go
   straight from the browser, so they run under the row level security policies
   on the table.
8. Throw from onComplete when the write fails, and include the error the client
   returned. The editor clears its rows as soon as onComplete resolves, so a
   swallowed error loses the import. When it succeeds, close the modal and
   reload the deliveries list.
9. Do not build an uploader of your own, do not parse the file yourself, and do
   not invent props.

import {
  DataEditor,
  type DataEditorColumn,
  type DataEditorResult,
} from "@updog/data-editor";
import "@updog/data-editor/styles.css";

type Delivery = {
  delivery_ref: string;
  supplier_name: string;
  carrier: string;
  pallets: string;
  gross_weight_kg: string;
  booked_for: string;
  dock: string;
  status: string;
  contact_email: string;
};

const columns: DataEditorColumn[] = [
  {
    id: "delivery_ref",
    title: "Delivery ref",
    validators: [{ type: "required" }, { type: "unique" }],
  },
  {
    id: "supplier_name",
    title: "Supplier",
    validators: [{ type: "required" }],
  },
  {
    id: "carrier",
    title: "Carrier",
    editor: {
      type: "select",
      options: [
        "Ravenhill Haulage",
        "Copsey Transport",
        "Marden Freight",
        "Delwyn Logistics",
      ],
      enableCustomValue: false,
    },
  },
  {
    id: "pallets",
    title: "Pallets",
    editor: { type: "number" },
    validators: [
      { type: "required" },
      { type: "number", min: 1, decimalPlaces: 0 },
    ],
  },
  {
    id: "gross_weight_kg",
    title: "Gross weight",
    editor: { type: "number" },
    validators: [
      { type: "required" },
      { type: "number", min: 0, decimalPlaces: 2 },
    ],
  },
  {
    id: "booked_for",
    title: "Booking date",
    editor: { type: "date" },
    validators: [{ type: "required" }],
  },
  {
    id: "dock",
    title: "Dock door",
    editor: {
      type: "select",
      options: ["Dock 1", "Dock 2", "Dock 3"],
      enableCustomValue: false,
    },
  },
  {
    id: "status",
    title: "Status",
    editor: {
      type: "select",
      options: ["Booked", "Arrived", "Unloaded"],
      enableCustomValue: false,
    },
  },
  {
    id: "contact_email",
    title: "Contact email",
    validators: [{ type: "email" }],
  },
];

export function DeliveryImporter({ onImported }: Props) {
  const [open, setOpen] = useState(false);

  return (
    <>
      <button type="button" onClick={() => setOpen(true)}>
        Import deliveries
      </button>

      <DataEditor<Delivery>
        apiKey="updog-bolt-demo"
        variant="uploader"
        open={open}
        onClose={() => {
          setOpen(false);
        }}
        columns={columns}
        primaryKey="delivery_ref"
        enableDeleteRow="all"
        onComplete={handleComplete}
      />
    </>
  );
}

/* loaded after @updog/data-editor/styles.css */
:root {
  --updog-brand: #9c4221;
}

Delivery no.           →  Delivery ref
Supplier name          →  Supplier
Carrier name           →  Carrier
Pallets received       →  Pallets
Gross weight (kg)      →  Gross weight
Slot date              →  Booking date
Dock                   →  Dock door
Load status            →  Status
Booking contact email  →  Contact email

const handleComplete = async (result: DataEditorResult<Delivery>) => {
  const rows = result.sources.flatMap((source) => source.rows);

  const upserts = rows
    .filter((entry) => entry.isValid && !entry.isDeleted)
    .filter((entry) => entry.isNew || entry.isChanged)
    .map((entry) => entry.row);

  const deletes = rows
    .filter((entry) => entry.isDeleted && !entry.isNew)
    .map((entry) => entry.row.delivery_ref);

  await writeDeliveries(upserts, deletes);

  setOpen(false);
  await onImported();
};

{
  sources: [
    {
      sourceId: "source_…",
      sourceName: "aldermere-goods-in.xlsx",
      rows: [
        {
          row: {
            delivery_ref: "AG-0001",
            supplier_name: "Tarnwick Packaging Ltd",
            carrier: "Marden Freight",
            pallets: "28",
            gross_weight_kg: "16162.76",
            booked_for: "2026-09-19",
            dock: "Dock 1",
            status: "Booked",
            contact_email: "[email protected]",
          },
          isNew: true,
          isChanged: false,
          isDeleted: false,
          isValid: true,
        },
        // 279 more rows
      ],
    },
  ],
  counts: { new: 280, changed: 0, deleted: 0, invalid: 0 },
  learnedSynonyms: { columns: [], values: [] },
}

const writeDeliveries = async (
  upserts: Delivery[],
  deletes: string[],
) => {
  if (upserts.length > 0) {
    const { error } = await supabase
      .from("deliveries")
      .upsert(upserts, { onConflict: "delivery_ref" });

    if (error) {
      throw new Error(error.message);
    }
  }

  if (deletes.length > 0) {
    const { error } = await supabase
      .from("deliveries")
      .delete()
      .in("delivery_ref", deletes);

    if (error) {
      throw new Error(error.message);
    }
  }
};