Add CSV and Excel Import to a Lovable App

Build a small CRM with one page. Use Lovable Cloud for the database.

Create a table named accounts with these columns.
  account_ref      text, unique, not null
  account_name     text, not null
  website          text
  tier             text
  seats            integer
  renewal_date     date
  region           text
  primary_contact  text
  contact_email    text
Leave the table empty.

Render /accounts as a table of every row with those nine columns, the row count
above it, and an empty state while the table holds nothing. Redirect / to
/accounts. Keep the styling plain and give the app one primary color.

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

The schema it works from
- Take the fields from a list my code hands it, so the same screen serves the
  accounts page today and any other page later.
- Support a field of text, number, date, time, or a list of options we allow,
  and let one field carry several rules at once.
- Support required, a pattern, a range, uniqueness inside the file, and
  uniqueness against the records we already store.
- Show the person our title for a field, and hand my code back its name.
- Take one field as the key, so a second import of the same records finds them
  and updates them.
- Drop the columns a file carries beyond the schema.

Screen 1, 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
  file without turning accented letters 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 person choose which sheets go on.
- Find the real header row when title rows sit above it, and handle a file
  that carries duplicate headers or no header at all.
- Keep a row that carries one field too few, and say which fields it filled.

Reading the values
- 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.

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

Screen 3, the values
- Collect the distinct values of every list field as the file spells them.
- Match each one to an option we allow, and let the person place the rest.

Data cleaning
- Hand the finished file to a spreadsheet the person works in, with our field
  titles on top and every row in it.
- Mark what the person changed. A new row, an edited cell and a deleted row
  each read differently at a glance.
- Run every rule the schema carries, and say which cell failed and why.
- Let the person 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 person 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.

Submitting
- 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 accounts 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. Render <DataEditor /> with apiKey="updog-lovable-demo", which is all the
   key a lovable.app host needs, variant="uploader", primaryKey="accountRef"
   and these columns:
   accountRef (text, required, unique), accountName (text, required),
   website (text), tier (select: Starter, Growth, Enterprise),
   seats (number), renewalDate (date), region (select: EMEA, AMER, APAC),
   primaryContact (text), contactEmail (text, email).
3. 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.
4. In onComplete, walk result.sources, split each source's rows on isNew,
   isChanged and isDeleted, write them to the accounts table, and close the
   modal once the write returns.
5. Throw from onComplete when that write fails. The editor clears its rows
   as soon as onComplete resolves, so a swallowed error loses the import.
6. 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 Account = {
  accountRef: string;
  accountName: string;
  website: string;
  tier: string;
  seats: string;
  renewalDate: string;
  region: string;
  primaryContact: string;
  contactEmail: string;
};

const columns: DataEditorColumn[] = [
  {
    id: "accountRef",
    title: "Account ref",
    validators: [{ type: "required" }, { type: "unique" }],
  },
  {
    id: "accountName",
    title: "Account name",
    validators: [{ type: "required" }],
  },
  {
    id: "website",
    title: "Website",
  },
  {
    id: "tier",
    title: "Tier",
    editor: {
      type: "select",
      options: ["Starter", "Growth", "Enterprise"],
      enableCustomValue: false,
    },
  },
  {
    id: "seats",
    title: "Seats",
    editor: { type: "number" },
  },
  {
    id: "renewalDate",
    title: "Renewal date",
    editor: { type: "date" },
  },
  {
    id: "region",
    title: "Region",
    editor: {
      type: "select",
      options: ["EMEA", "AMER", "APAC"],
      enableCustomValue: false,
    },
  },
  {
    id: "primaryContact",
    title: "Primary contact",
  },
  {
    id: "contactEmail",
    title: "Contact email",
    validators: [{ type: "email" }],
  },
];

<DataEditor<Account>
  apiKey="updog-lovable-demo"
  variant="uploader"
  open={open}
  onClose={() => {
    setOpen(false);
  }}
  columns={columns}
  primaryKey="accountRef"
  onComplete={handleComplete}
/>

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

Company name       →  Account name
Website URL        →  Website
Subscription tier  →  Tier
Main contact       →  Primary contact

const handleComplete = async (result: DataEditorResult<Account>) => {
  const inserts: AccountRecord[] = [];
  const updates: AccountRecord[] = [];
  const deletes: string[] = [];

  for (const source of result.sources) {
    for (const resultRow of source.rows) {
      const record = toAccountRecord(resultRow.row);

      if (resultRow.isDeleted) {
        if (!resultRow.isNew) deletes.push(record.account_ref);
      } else if (resultRow.isValid && resultRow.isNew) {
        inserts.push(record);
      } else if (resultRow.isValid && resultRow.isChanged) {
        updates.push(record);
      }
    }
  }

  await writeImportedAccounts({ data: { inserts, updates, deletes } });
  setOpen(false);
};

{
  sources: [
    {
      sourceId: "source_…",
      sourceName: "calderbrook-accounts.xlsx",
      rows: [
        {
          row: {
            accountRef: "CB-0001",
            accountName: "Alverston Analytics Ltd",
            website: "alverstonanaly.example",
            tier: "Starter",
            seats: "310",
            renewalDate: "2027-02-13",
            region: "AMER",
            primaryContact: "Adela Ashgrove",
            contactEmail: "[email protected]",
          },
          isNew: true,
          isChanged: false,
          isDeleted: false,
          isValid: true,
        },
        // 179 more rows
      ],
    },
  ],
  counts: { new: 180, changed: 0, deleted: 0, invalid: 0 },
  learnedSynonyms: { columns: [], values: [] },
}

const upserts = [...data.inserts, ...data.updates];

if (upserts.length > 0) {
  const { error } = await supabaseAdmin
    .from("accounts")
    .upsert(upserts, { onConflict: "account_ref" });

  if (error) throw new Error(`Failed to save accounts: ${error.message}`);
}