How to Import CSV Into DuckDB

DuckDB is an analytical SQL database that runs inside the process that queries it. There is no server to start, no port to open, and no connection string to configure. A database can live in a single file or entirely in memory. On disk, DuckDB stores data in a compressed columnar format, while its query engine processes data in vectors rather than one row at a time. For analytical queries, this means the engine can often read only the columns the query actually needs instead of scanning entire rows. DuckDB is open source under the MIT license.

DuckDB makes the basic case small. SELECT * FROM 'readings.csv' opens a CSV and infers its dialect and column types. If inference needs help, read_csv accepts explicit options for delimiters, headers, and types. DESCRIBE SELECT * FROM read_csv('input.csv') shows the inferred schema before a table is created. CREATE TABLE readings AS FROM read_csv('input.csv') creates a table from the file. INSERT INTO readings SELECT * FROM read_csv('input.csv') appends the rows to an existing table.

All of these examples assume DuckDB can already address the file. In a browser, the CSV starts as a File selected by the user, while DuckDB-Wasm has its own file system. The application must first register the file or pass its contents to DuckDB. Only then can SQL read it.

That gap is where Updog Importer sits.

The database runs in the browser

DuckDB-Wasm compiles DuckDB to WebAssembly and runs queries in a Web Worker. Updog Importer runs on the client as well. The user selects a file, Updog Importer parses and validates it, and the accepted rows can move directly into DuckDB. The CSV does not need to pass through an application server.

import * as duckdb from "@duckdb/duckdb-wasm";

const bundle = await duckdb.selectBundle(
  duckdb.getJsDelivrBundles(),
);

const workerUrl = URL.createObjectURL(
  new Blob(
    [`importScripts("${bundle.mainWorker}");`],
    { type: "text/javascript" },
  ),
);

const worker = new Worker(workerUrl);
const db = new duckdb.AsyncDuckDB(
  new duckdb.ConsoleLogger(),
  worker,
);

await db.instantiate(
  bundle.mainModule,
  bundle.pthreadWorker,
);

URL.revokeObjectURL(workerUrl);

await db.open({
  path: "opfs://catchment.db",
  accessMode: duckdb.DuckDBAccessMode.READ_WRITE,
});

selectBundle checks the browser's WebAssembly features and chooses a compatible DuckDB build. With the default jsDelivr bundles, that means either the baseline mvp build or the faster eh build with WebAssembly exception handling. A threaded coi build is available separately for pages configured for cross-origin isolation.

The opfs:// path stores the database in the browser's Origin Private File System instead of keeping it only in memory. A later session can reopen the same database. Before relying on recent writes being durable, issue a CHECKPOINT.

This changes the shape of the import. There is no CSV upload endpoint, request body, or network transfer between Updog Importer and the database. Updog Importer prepares the rows in the browser, and DuckDB receives them in the browser.

The remaining question is how to cross that boundary.

The CSV does not fit the schema

A catchment partnership collects river samples and sends them to a lab. At the end of the month, the lab returns a CSV.

1Site,Date sampled,Det.,Result,Method,Lab ref2WYE-114,04 Mar 2026,NO3-N,6.31,Ion chromatography,L-88413WYE-114,04 Mar 2026,NO3-N,6.90,Ion chromatography,L-89024WYE-118,04 Mar 2026,PO4-P,<0.05,Colorimetric,L-88435WYE-118,05 Mar 2026,NH4-N,0.42 mg/l,Colorimetric,L-88506LUG-207,05 Mar 2026,DO,9.8,Probe,⋮473 rows not shown480LUG-207,29 Mar 2026,NO3-N,4.02,Ion chromatography,L-9310

The file makes sense to the people who produced it. It does not yet match the shape the application needs.

Det. is the lab's abbreviation for the measured substance, and the values below it use chemical codes such as NO3-N and PO4-P. Rows 2 and 3 look almost identical, but they are separate measurements with different results and lab references. <0.05 is not a number. It reports that the measurement fell below a stated laboratory threshold. The next row puts the unit inside the value as 0.42 mg/l. Row 6 has no lab reference at all.

None of these makes the CSV invalid. They make its meaning dependent on the lab that produced it.

The schema defines a reading

Suppose the application allows one reading per site, day, and substance. Those three fields form the key.

CREATE TABLE readings (
    site_code   VARCHAR NOT NULL,
    sampled_on  DATE NOT NULL,
    determinand VARCHAR NOT NULL,
    value_mg_l  DECIMAL(9, 3),
    method      VARCHAR,
    lab_ref     VARCHAR,
    loaded_at   TIMESTAMP DEFAULT current_timestamp,
    PRIMARY KEY (site_code, sampled_on, determinand)
);

DuckDB creates an Adaptive Radix Tree index for a primary key or unique constraint. The index enforces uniqueness, and ON CONFLICT can use that constraint to decide whether an insert becomes an update. The trade-off is write cost. DuckDB has to maintain the index as rows are inserted or changed, and its documentation recommends adding indexes after bulk loading when possible.

value_mg_l is DECIMAL(9, 3), which holds at most nine digits in total, with three after the decimal point. DuckDB rounds a value when it is converted to that scale, so 6.3149 becomes 6.315. That is valid database behavior, but it is a poor place to discover unwanted precision loss. Updog Importer can reject a fourth decimal before the row reaches DuckDB.

Updog Importer enforces the schema first

Updog Importer defines the same constraints before a row reaches DuckDB.

import type { DataEditorColumn } from "@updog/data-editor";

const DETERMINANDS = [
  "Nitrate",
  "Phosphate",
  "Ammonia",
  "Dissolved oxygen",
];

export const columns: DataEditorColumn[] = [
  {
    id: "siteCode",
    title: "Site code",
    size: 130,
    validators: [
      { type: "required" },
      { type: "regex", pattern: "^[A-Z]{3}-\\d{3}$" },
    ],
  },
  {
    id: "sampledOn",
    title: "Sampled on",
    size: 140,
    editor: { type: "date" },
    validators: [{ type: "required" }, { type: "date" }],
  },
  {
    id: "determinand",
    title: "Determinand",
    size: 170,
    editor: {
      type: "select",
      options: DETERMINANDS,
      enableCustomValue: false,
    },
    validators: [
      { type: "required" },
      { type: "oneOf", values: DETERMINANDS },
    ],
  },
  {
    id: "valueMgL",
    title: "Result mg/l",
    size: 130,
    editor: { type: "number" },
    validators: [{ type: "number", min: 0, decimalPlaces: 3 }],
  },
  { id: "method", title: "Method", size: 180 },
  { id: "labRef", title: "Lab ref", size: 120 },
];

decimalPlaces: 3 mirrors the scale of DECIMAL(9, 3). The grid can flag a fourth decimal while preserving the value the lab supplied. min: 0 rejects negative measurements. 04 Mar 2026 becomes 2026-03-04. The determinand is a closed list, so enableCustomValue: false prevents an unknown code from becoming a new option.

That last rule matters because determinand is part of the primary key. If a chemical code is not mapped, the row has no complete identity. It should not reach DuckDB, since primary-key columns cannot be NULL and the table would reject it anyway. The mapping therefore has to resolve NO3-N, PO4-P, and every other accepted lab code before insertion.

Numbers have a different problem. <0.05 is not a decimal value. 0.42 mg/l combines a number with its unit. Updog Importer does not silently turn either into a number, so both remain visible as validation errors.

DuckDB rejects <0.05 when it is cast to DECIMAL(9, 3) as well. A failed cast raises a conversion error.

The difference is where the error appears. In SQL, a failed conversion stops the statement. In Updog Importer, it marks the cell before insertion. The person reviewing the file can then apply the rule that dataset follows, whether that is preserving the reporting limit, replacing the value, or leaving the cell empty.

DuckDB enforces the table. Updog Importer catches the mismatch before the table sees it.

String matching does not know chemistry

Five of the six headers map automatically. Det. does not.

"Method"       → method        100
"Lab ref"      → labRef        100
"Site"         → siteCode       80
"Result"       → valueMgL       80
"Date sampled" → sampledOn      70
"Det."         → determinand     0

Updog Importer accepts a match at 60 or above. Det. never gets there. Containment requires at least four characters on the shorter side, while det has three. It shares no complete word with determinand, and their edit distance is too large for a typo match.

The values fail for a more fundamental reason. NO3-N and Nitrate are semantically related, but their strings are not. The same is true of DO and Dissolved oxygen. No edit-distance rule can infer that relationship from the text alone.

The missing information belongs to the domain, not the matching algorithm. The application has to supply it as mapping data.

Column matching in React explains the scoring algorithm in detail.

The editor opens on the table

The editor gets the same columns, identity rules, and domain mappings that the table expects.

<DataEditor<Reading>
  apiKey="your-license-key"
  open={open}
  onClose={closeEditor}
  columns={columns}
  primaryKey={["siteCode", "sampledOn", "determinand"]}
  enableDeleteRow="all"
  blockSubmitOnError
  synonyms={{
    columns: {
      determinand: ["Det.", "Analyte"],
    },
    values: {
      Nitrate: ["NO3-N", "NO3"],
      Phosphate: ["PO4-P", "SRP"],
      Ammonia: ["NH4-N", "NH3-N"],
      "Dissolved oxygen": ["DO"],
    },
  }}
  loadData={loadFromDuckDb}
  onComplete={onComplete}
/>

Column and value synonyms stay separate. Det. can map to the determinand column without competing with DO, NO3-N, or the other values inside it.

primaryKey uses the same fields as the DuckDB table. blockSubmitOnError prevents a row with an unresolved value such as <0.05 from reaching onComplete. enableDeleteRow="all" lets the reviewer remove a row before submission.

loadData works in the other direction. When the editor opens, it reads the rows already stored in DuckDB.

const CURRENT = `
  SELECT
    site_code                     AS siteCode,
    sampled_on::VARCHAR           AS sampledOn,
    determinand,
    (value_mg_l::DOUBLE)::VARCHAR AS valueMgL,
    method,
    lab_ref                       AS labRef
  FROM readings
  ORDER BY sampled_on, site_code, determinand;
`;

const loadFromDuckDb = useCallback(async (onChunk) => {
  const conn = await db.connect();

  try {
    const table = await conn.query(CURRENT);

    onChunk(
      table.toArray().map((row) => row.toJSON()),
      { source: "DuckDB", done: true },
    );
  } finally {
    await conn.close();
  }
}, []);

DuckDB-Wasm returns query results as Apache Arrow tables. toArray().map(row => row.toJSON()) converts those rows into plain JavaScript objects for the editor.

sampled_on is cast to VARCHAR, so the editor receives the date in DuckDB's text representation. value_mg_l passes through DOUBLE before becoming text to remove the fixed three-place display of DECIMAL(9, 3), so a stored 6.310 can return to the editor as 6.31. The database still keeps the column as a fixed-point decimal.

This example uses the React component. The same configuration can pass through the Updog Importer web component build in Vue, Angular, or Svelte.

Submit returns a delta

onComplete receives the rows that changed, grouped by source. Each row carries isNew, isChanged, isDeleted, and isValid. Rows imported from the CSV appear under the file's source as new. An existing table row that nobody touched does not appear at all.

import type {
  DataEditorResult,
  ResultRow,
} from "@updog/data-editor";

type Reading = {
  siteCode: string;
  sampledOn: string;
  determinand: string;
  valueMgL: string;
  method: string;
  labRef: string;
};

const toRow = (
  entry: ResultRow<Reading>,
  n: number,
) => {
  if (entry.isDeleted && entry.isNew) return [];

  return [{
    n,
    site_code: entry.row.siteCode,
    sampled_on: entry.row.sampledOn,
    determinand: entry.row.determinand,
    value_mg_l: entry.row.valueMgL,
    method: entry.row.method,
    lab_ref: entry.row.labRef,
    deleted: entry.isDeleted,
  }];
};

const onComplete = useCallback(
  async (result: DataEditorResult<Reading>) => {
    const rows = result.sources
      .flatMap((source) => source.rows)
      .flatMap((entry, index) => toRow(entry, index));

    const conn = await db.connect();

    await db.registerFileText(
      "rows.json",
      JSON.stringify(rows),
    );

    try {
      await conn.query("BEGIN TRANSACTION");

      try {
        await conn.query("DROP TABLE IF EXISTS staging");

        await conn.insertJSONFromPath("rows.json", {
          name: "staging",
        });

        await conn.query(UPSERT_READINGS);
        await conn.query(DELETE_REMOVED);
        await conn.query("COMMIT");
      } catch (error) {
        await conn.query("ROLLBACK");
        throw error;
      }

      await conn.query("CHECKPOINT");
    } finally {
      await db.dropFile("rows.json");
      await conn.close();
    }
  },
  [],
);

The changed rows become one JSON document. registerFileText places it in DuckDB-Wasm's virtual file system, and insertJSONFromPath reads that document into the staging table. DuckDB-Wasm documents this as a two-step import, where the data is registered with the database and then ingested through a connection.

Nothing crosses the network, but the copy is not free. The JSON string lives in the JavaScript heap while DuckDB builds its own representation inside WebAssembly. For this file, 479 rows serialize to 77,756 bytes, or about 162 bytes per row. At the same density, 100,000 changed rows would produce roughly 16 MB of JSON before accounting for the grid, JavaScript objects, DuckDB, or temporary allocations.

That matters in a browser. DuckDB-Wasm documents a 4 GB WebAssembly memory ceiling, and a browser may impose a lower limit. A single JSON handover is reasonable for this dataset, and at larger sizes the same boundary may need batching instead.

Errors have to escape the handler as well. Updog Importer waits for the onComplete promise. If the handler catches an error and resolves normally, it has no failure to report. If it rejects, the editor can remain in place with the user's mappings and corrections intact.

The rows stay on screen until DuckDB has accepted them.

DuckDB applies the delta

Two statements apply the changes.

const UPSERT_READINGS = `
  INSERT INTO readings
    (site_code, sampled_on, determinand, value_mg_l, method, lab_ref)
  SELECT
    site_code,
    sampled_on::DATE,
    determinand,
    nullif(value_mg_l, '')::DECIMAL(9, 3),
    nullif(method, ''),
    nullif(lab_ref, '')
  FROM staging
  WHERE NOT deleted
  QUALIFY row_number() OVER (
    PARTITION BY site_code, sampled_on, determinand
    ORDER BY n DESC
  ) = 1
  ON CONFLICT (site_code, sampled_on, determinand) DO UPDATE SET
    value_mg_l = excluded.value_mg_l,
    method     = excluded.method,
    lab_ref    = excluded.lab_ref,
    loaded_at  = now();
`;

const DELETE_REMOVED = `
  DELETE FROM readings
  WHERE (site_code, sampled_on, determinand) IN (
    SELECT
      site_code,
      sampled_on::DATE,
      determinand
    FROM staging
    WHERE deleted
  );
`;

nullif(value_mg_l, '') turns an empty editor value into NULL before the decimal cast. Without it, an empty string cannot be converted to DECIMAL(9, 3) and the statement fails. The same conversion stores an empty method or lab_ref as NULL rather than as an empty string. DuckDB treats NULL as missing data, and most aggregates ignore it.

The QUALIFY clause handles duplicate keys inside the submitted delta. If several staged rows identify the same site, day, and determinand, only the last one survives into the upsert. ON CONFLICT then inserts a new reading or updates the existing row with the same primary key.

The upsert and delete run inside one transaction. If either statement fails, neither change remains. There is no application-level upload protocol or partial batch state to reconcile, since the browser hands DuckDB one staged delta and DuckDB applies it atomically. CHECKPOINT runs after the commit to synchronize the write-ahead log with the database file in OPFS.

loaded_at uses now() deliberately. DuckDB supports current_timestamp, but inside ON CONFLICT ... DO UPDATE it binds the bare keyword against the target table and answers Binder Error: Table "readings" does not have a column named "current_timestamp". now() avoids that ambiguity.

Deduplicate before ON CONFLICT

The lab reissued one nitrate result and left both rows in the file. Updog Importer keeps both. Primary-key matching runs against rows from other sources, so two rows of one file never merge into each other and both reach the staging table.

What happens next is less obvious.

On DuckDB 1.5.5 through the Node client, and again on 1.4.3 in DuckDB-Wasm, this statement runs without error.

INSERT INTO readings
VALUES (key, 6.310), (key, 6.900)
ON CONFLICT (key) DO UPDATE
SET value_mg_l = excluded.value_mg_l;

The table keeps the first of the two values.

readings holds  6.310

Reverse the two input rows and 6.900 remains. In these tests, the first row for a key won and later rows with the same key inside the statement had no effect. Run the rows as separate statements and the second update wins instead. With DO NOTHING, the first row wins. Without ON CONFLICT, the duplicate key raises a constraint error.

Treat this behavior as an observation rather than as part of the contract. DuckDB's current documentation contains an ON CONFLICT DO UPDATE example with two input rows sharing the same key, but does not state which result should remain. An older report, issue #8147, shows DuckDB 0.8.2-dev rejecting duplicate keys inside one statement and describes that behavior as a known limitation at the time.

The safe rule is simpler. Do not make ON CONFLICT resolve duplicates inside the incoming batch.

The staging query does that first.

QUALIFY row_number() OVER (
  PARTITION BY site_code, sampled_on, determinand
  ORDER BY n DESC
) = 1

n preserves the submitted row order. For each key, the row with the highest n reaches the upsert. The conflict clause then has only one incoming row to compare with the table.

The same staging table can expose the collisions before anything is written.

SELECT
  site_code,
  sampled_on,
  determinand,
  count(*) AS rows
FROM staging
GROUP BY
  site_code,
  sampled_on,
  determinand
HAVING count(*) > 1;

That gives the application the duplicate keys directly. It can resolve them automatically, or show them to the person reviewing the import.

The browser sets the limit

Updog Importer does not ship a DuckDB connector. There is no destination to select and no webhook to configure. onComplete returns the edited rows to your code, and the SQL between that callback and DuckDB belongs to the application.

Keeping the whole path in the browser also puts the browser's limits on it. DuckDB-Wasm has at most 4 GB of WebAssembly memory, and browsers may impose a lower ceiling. It runs queries on a single thread by default, and multithreading is available through the cross-origin-isolated build but remains experimental.

Updog Importer has its own memory cost. The grid can hold roughly a million rows, while the imported rows, existing rows returned by loadData, and the JSON used for the handover all consume browser memory. Loading an entire readings table therefore stops making sense long before the table itself has to stop growing.

Load only what the person needs to edit. If they are correcting one catchment for one season, put that condition in loadData. DuckDB can keep years of readings while the editor opens only the relevant slice.

The other case does not need an importer at all. If your own system already has a clean CSV and nobody needs to inspect or correct it, DuckDB can infer the dialect and types with its CSV reader and insert the result directly into a table. Native DuckDB can also spill intermediate data to a temporary directory when grouping, joining, sorting, or windowing exceeds available memory.

Use Updog Importer where a person has to resolve the file before it becomes table data. Use DuckDB directly where the file is already trusted.

Client-side and server-side CSV import separates the workloads that fit each model. A different way to think about CSV import explains why the browser can be the right boundary when the file needs human review.

The next export uses the same path

The integration comes to one DuckDB table, six Updog Importer columns, two synonym maps, one read query, one submit handler, and two write statements. There is no upload endpoint between the CSV and the database. Updog Importer and DuckDB already run in the browser, and onComplete is the boundary between them.

The same path works through the React CSV importer component or the web component build. Only the host UI changes.

In April, the lab sends another export. Some March readings appear again with corrected results. The chemical codes already have mappings. If the file contains the same key twice, the staging query resolves it before the upsert. The rest follows the same path of inspect, correct, submit, and commit.

The next CSV is not a new integration. It is new data.