How to Import CSV Into MySQL

MySQL's own documentation covers LOAD DATA, LOAD DATA LOCAL INFILE, mysqlimport and the Workbench Table Data Import Wizard. LOAD DATA reads a CSV the server itself can reach, and it wants the FILE privilege. The LOCAL form reads a file on the client machine, and local_infile is disabled by default on both ends. mysqlimport wraps the same statement on the command line. The wizard takes CSV or JSON and asks the operator for the encoding. Every one of them starts from a seat holding database credentials. Your customer sits outside that seat, holding a menu export from the point-of-sale system they are leaving.

Their export arrives looking like this.

1PLU;Item;Menu Group;Price;Allergens;Station;Active21042;Crème Brûlée;DESSERTS;8,50;milk, eggs;Pastry;Y31043;Creme Brulee;DESSERTS;8,50;milk, eggs;Pastry;N41044;Jalapeño Poppers;App;7,25;milk, gluten;Fryer;Y51051;Sea Bass;Main;MP;fish;Grill;Y61052;Elderflower Soda;Bev;4,00;;Bar;Y⋮142 rows not shown1491236;House Lemonade;Bev;3,75;;Bar;Y

Seven headers sit between semicolons, and the till wrote the file in Windows-1252. Crème Brûlée carries three bytes a strict UTF-8 reader rejects. Two rows name the same dessert, one with accents and one without. The prices use a comma for the decimal point, and one of them reads MP for market price. The menu group column says DESSERTS, App, Main and Bev.

The franchisee opens your app and hands the menu export to the importer. Updog Importer decodes it in the browser, matches the headers to your schema, and puts every value in front of them. Your onComplete handler receives the rows. The handler cuts them into chunks and sends each chunk to a route you own. The route holds the MySQL credentials and runs one INSERT ... ON DUPLICATE KEY UPDATE. MySQL writes the table.

The chain reaches MySQL with no Updog server anywhere in it.

The table and its unique key

Pick the unique key before picking the upsert. ON DUPLICATE KEY UPDATE fires when a row would cause a duplicate value in a UNIQUE index or a PRIMARY KEY, so that key decides which rows merge and which arrive new.

create table menu_item (
  id bigint unsigned not null auto_increment primary key,
  location_id bigint unsigned not null,
  plu varchar(16) not null,
  item_name varchar(120) not null,
  category varchar(32) not null,
  price decimal(8, 2) null,
  allergens varchar(255) not null default '',
  station varchar(32) not null,
  active tinyint(1) not null default 1,
  updated_at datetime not null default current_timestamp
    on update current_timestamp,
  unique key uniq_location_plu (location_id, plu)
) engine = innodb default charset = utf8mb4 collate = utf8mb4_0900_ai_ci;

The key sits on location_id and plu, two values made of digits. Naming the dish instead would have cost a row. MySQL's default collation is utf8mb4_0900_ai_ci, and both suffixes are load-bearing. _ci means case-insensitive and _ai means accent-insensitive. MySQL's own manual runs the worked example under an accent-insensitive collation, inserting Bar and Bär into one table and returning both from a search for Bär. A unique index requires every value in it to be distinct, so Creme Brulee and Crème Brûlée would have been one key, and the second write would have updated the first row.

One unique key is also the shape the clause wants. The manual says to avoid ON DUPLICATE KEY UPDATE on tables with multiple unique indexes, and a key made of digits keeps that rule easy to hold.

default charset = utf8mb4 decides what the column can store. In MySQL 9.7 the name utf8 is still the deprecated alias for utf8mb3, which takes up to three bytes a character and covers the Basic Multilingual Plane alone. A character outside that plane does not fit a utf8mb3 column, and MySQL's error 1366 reads Incorrect string value. The manual calls utf8mb4 the recommended character set and says every new application should use it.

The columns the person sees

The columns array carries your menu_item table in the words the franchisee reads. Each entry holds a title for the header, an editor that decides how a cell gets typed, and validators that mark failures.

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

const GROUPS = ["Starters", "Mains", "Sides", "Desserts", "Drinks"];

export const columns: DataEditorColumn[] = [
  {
    id: "plu",
    title: "PLU",
    size: 100,
    transformer: (value) => String(value).trim(),
    validators: [{ type: "required" }, { type: "unique" }],
  },
  {
    id: "itemName",
    title: "Item name",
    size: 220,
    validators: [{ type: "required" }],
  },
  {
    id: "category",
    title: "Menu group",
    size: 150,
    editor: { type: "select", options: GROUPS, enableCustomValue: false },
    validators: [{ type: "oneOf", values: GROUPS }],
  },
  {
    id: "price",
    title: "Price",
    size: 110,
    editor: { type: "number" },
    validators: [{ type: "number", min: 0, decimalPlaces: 2 }],
  },
  { id: "allergens", title: "Allergens", size: 180 },
  { id: "station", title: "Station", size: 130 },
  {
    id: "active",
    title: "Active",
    size: 100,
    editor: { type: "select", options: ["Yes", "No"], enableCustomValue: false },
    validators: [{ type: "oneOf", values: ["Yes", "No"] }],
  },
];

The number editor turns 8,50 into 8.50. Number columns vote on the file's format before any value is rewritten, and four comma decimals against no dot decimals settle this file as European. MP survives that pass untouched, because stripping letters would corrupt a code like abc123, and the number validator flags the cell for the person to fix. The select editor with enableCustomValue off holds the menu group to five options and sends everything else to the value matching step. { type: "unique" } on plu mirrors the unique key in the table, so a repeated PLU is flagged in the browser before MySQL ever sees it.

The synonyms prop hands matching the words your franchisees already use, and the mount below puts every prop in one place.

<DataEditor<MenuItem>
  apiKey="your-license-key"
  variant="uploader"
  open={open}
  onClose={closeImporter}
  columns={columns}
  primaryKey="plu"
  synonyms={{
    columns: { plu: ["item code", "menu id"], category: ["course", "section"] },
    values: { Starters: ["app", "appetizer"] },
  }}
  onComplete={onComplete}
/>

Every header in this file matches on its own, so the column synonyms are there for the next till, which calls the same field Item Code or Course. DESSERTS lands on Desserts exactly, once matching lowercases it. Main scores as a string contained in Mains. Y and N reach Yes and No from the built-in table with no configuration at all. App scores nothing against Starters, which is why it comes from synonyms. Bev reaches nothing, so the person maps it by hand, and that pair comes back on the result as learnedSynonyms, ready to store and feed back next time. How to remember CSV import mappings between uploads walks that pair from the grid into the next menu file.

primaryKey decides how an imported row meets a row already in the grid. Inside one import the location is fixed, so the PLU alone identifies a menu item on the browser side. Values are compared after surrounding whitespace is trimmed. For the install and the modal wiring underneath this snippet, see how to import a CSV file into a React app.

Every snippet here is React. The web component build takes the same props, so a Vue, Angular or Svelte front end declares the same columns and the same handler.

Encoding before the write

None of that matters while the file is still bytes. Updog decodes it first, inside the parse worker, before a single header is read.

The first bytes settle the easy cases. A byte order mark names UTF-8 or UTF-16 outright, and the decoder strips it. With no mark, a strict UTF-8 decode runs over the first 64KB. A file that survives that pass is checked again with a fatal decode of the whole file, so legacy bytes hiding behind an ASCII-clean opening cannot slip through. A file that fails goes to chardet, which reads the byte statistics and names a character set. When chardet has no guess at all, Windows-1252 takes the file, because it decodes every byte sequence and never throws.

The first data row of the menu export reads two ways, depending on whether detection ran.

no detection      1042;Cr�me Br�l�e;DESSERTS;8,50
after detection   1042;Crème Brûlée;DESSERTS;8,50

Byte 0xE8 opens a three-byte UTF-8 sequence that never arrives, so a reader with no detection writes the replacement character in its place. The name reaches your handler intact instead, as UTF-8, whatever byte table the till used.

Every cell is cleaned after the decode. Zero-width characters come out, a non-breaking space becomes an ordinary space, and the text is normalised to NFC. That last one earns its place here, because Unicode writes è two ways and MySQL has no normalisation of its own. It stores the bytes it was handed, so the same name written both ways sits in the column as two different strings. The delimiter is a vote as well. Comma, semicolon, tab and pipe are each counted outside quotes across the first ten lines, and the candidate with the steadiest count wins. A candidate that misses a line entirely is out, which is what happens to the comma here, since the header line holds none while milk, eggs holds one. Common CSV import errors walks the rest of that list.

By the time the grid opens, the file is text in one encoding with one delimiter, and every cell has been trimmed and normalised. What travels next is the shape your handler reads.

The chunked post to your own route

The franchisee presses submit, and Updog Importer hands your handler every menu row grouped by source, carrying isNew, isChanged, isDeleted and isValid. Rows nobody touched stay out. A franchisee can drop three menu exports in one import, and each file lands as its own source entry, so the handler flattens before it slices.

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

const CHUNK_SIZE = 500;

const onComplete = useCallback(async (result: DataEditorResult<MenuItem>) => {
  const rows = result.sources
    .flatMap((source) => source.rows)
    .filter((entry) => entry.isValid && !entry.isDeleted)
    .map((entry) => entry.row);

  for (let start = 0; start < rows.length; start += CHUNK_SIZE) {
    const response = await fetch("/api/menu-items/import", {
      method: "POST",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify({ rows: rows.slice(start, start + CHUNK_SIZE) }),
    });

    if (!response.ok) {
      const failure = await response.json();
      throw new Error(failure.message);
    }
  }
}, []);

Five hundred is a conservative choice of ours, sitting inside two ceilings that are published. The first is the body your own route accepts. Fastify caps a request body at 1,048,576 bytes by default and answers a larger one with FST_ERR_CTP_BODY_TOO_LARGE. One menu row of these seven fields serializes to about 140 bytes.

{"plu":"1042","itemName":"Crème Brûlée","category":"Desserts","price":"8.50","allergens":"milk, eggs","station":"Pastry","active":"Yes"}

Five hundred of those reach roughly 70KB, and 1 MiB holds around 7,400 of them. Run that division against your own widest row before raising the number. A menu carrying a long description per dish changes the answer.

The second ceiling is the packet. One statement travels to MySQL as one packet, the server default for max_allowed_packet is 64MB, and the largest packet the protocol carries is 1GB. Both the client and the server hold their own copy of that variable, so raising it means raising it twice. At 500 menu rows the statement sits three orders of magnitude inside the default, which makes the body limit the ceiling that binds here. Widen the row or lower the packet limit and the two swap places.

A failed chunk has to throw. Updog holds the editor open only while your handler runs, and empties it the moment the promise resolves. A handler that catches its own failure and returns reads as success, and the menu rows leave the screen unsaved. A spinner covers the confirm dialog until the last chunk answers, which is the other reason chunks stay small. Throwing leaves every menu row, every mapping and every correction on the screen, so the franchisee submits again on data still in front of them.

Copy anything off the result you plan to keep while the handler is still running. The editor releases its rows, its sources, its history and its learned synonyms as soon as the promise resolves.

The isValid filter in that snippet costs this menu a row. A failed validator marks the cell and lets the person submit anyway, so the row holding MP reaches your handler and the filter drops it on the floor. Pass blockSubmitOnError to the editor and submit stays locked until every menu row passes. Keeping the filter open works too, once you split the rows by isValid and post the failures somewhere your support team can read them.

The write from your own server

The statement is one INSERT carrying every row in the chunk.

insert into menu_item
  (location_id, plu, item_name, category, price, allergens, station, active)
values
  (?, ?, ?, ?, ?, ?, ?, ?),
  (?, ?, ?, ?, ?, ?, ?, ?)
as new
on duplicate key update
  item_name = new.item_name,
  category  = new.category,
  price     = new.price,
  allergens = new.allergens,
  station   = new.station,
  active    = new.active;

AS new names the row being inserted, so the update half can read its values. That alias arrived in MySQL 8.0.19 and replaced the VALUES() function, which has been deprecated since 8.0.20 and is subject to removal. Affected rows counts 1 for a row inserted, 2 for a row updated, and 0 for a row set to the values it already held, so the total is a write count rather than a row count.

The browser goes no further than your route, and the MySQL credentials wait on the other side of it.

import Fastify from "fastify";
import mysql from "mysql2/promise";
import type { ResultSetHeader } from "mysql2/promise";

const pool = mysql.createPool({
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DATABASE,
  charset: "utf8mb4",
  connectionLimit: 10,
});

const app = Fastify();

app.post("/api/menu-items/import", async (request, reply) => {
  const session = await getVerifiedSession(request);
  if (!session) return reply.code(401).send({ message: "Not signed in" });

  const { rows } = request.body as { rows: MenuItem[] };
  const values = rows.map((row) => [
    session.locationId,
    row.plu,
    row.itemName,
    row.category,
    row.price === "" ? null : Number(row.price),
    row.allergens,
    row.station,
    row.active === "Yes" ? 1 : 0,
  ]);

  try {
    const [result] = await pool.query<ResultSetHeader>(
      buildUpsert(values.length),
      values.flat(),
    );
    return { affectedRows: result.affectedRows };
  } catch (cause) {
    const error = cause as { code?: string; errno?: number; message: string };
    const status = error.code === "ER_DUP_ENTRY" ? 409 : 400;
    return reply
      .code(status)
      .send({ message: error.message, code: error.code, errno: error.errno });
  }
});

buildUpsert returns the statement above with one placeholder group per row in the chunk. getVerifiedSession() stands in for your own server-side authentication check. location_id comes off that session and never off the request body, because a franchisee who can post rows can also post a different location id.

charset names the connection character set explicitly. The driver already opens utf8mb4_unicode_ci when the option is absent, and writing it out keeps the connection and the column describing the same thing. The column charset is the one that decides what gets stored.

price arrives as the string 8.50, and converting it before it enters the parameter list keeps the payload matching a decimal column. active arrives as Yes or No from the select column and becomes 1 or 0.

Duplicate keys and failed chunks

mysql2 rejects the promise on a server error, and the error carries code as a string alongside errno as a number. A duplicate on a unique index the clause did not resolve comes back as ER_DUP_ENTRY, error 1062, Duplicate entry. A statement bigger than the packet limit comes back as ER_NET_PACKET_TOO_LARGE, error 1153, and MySQL closes the connection behind it. A character the column cannot hold comes back as error 1366, Incorrect string value.

Pass code and message back to the browser. The handler throws on the first response that is not ok, and the editor keeps everything the person did.

Chunks that already landed stay landed. Running the same chunk again writes the same values a second time, so a retry after a failure costs a rewrite of rows that already exist. Those rows report 0 affected, which is how the second run says it changed nothing.

MySQL's own import paths

Updog Importer integrates with nobody. There is no MySQL connector, no destination list, no webhook and no server of ours. onComplete hands your code an object, and the route between it and MySQL is yours to write.

MySQL already ships an import for the other case. LOAD DATA needs the FILE privilege, and secure_file_priv decides which directory the server will read from, so the file has to reach the database host first. LOAD DATA LOCAL INFILE reads from the client machine and needs local_infile enabled on the server and requested by the client. MySQL disables it by default, since a patched server could ask a client for any file that client can read. mysqlimport is a command-line wrapper around the same statement, it takes the table name from the file name, and without --replace or --ignore a duplicate key aborts it and the rest of the file is ignored. MySQL Workbench reads CSV and JSON through its Table Data Import Wizard, with an encoding field the operator picks by hand.

Every one of those is the shorter path when the file belongs to the developer. Everything above exists for the menu export that belongs to a franchisee, arriving through a browser, in a session your app issued. Client-side and server-side CSV import lays out which of the two a job wants.

The finished chain

The franchisee's file never left their laptop. The rows travelled from your own front end to your own route, and from there into MySQL, and the only party added to the chain is yourself. The table declares utf8mb4, one unique key made of digits, and a collation you picked on purpose.

If the column declares utf8mb4 and the unique key names the identity your schema already uses, the rows the person cleaned in the browser reach MySQL as the same rows they saw on the screen.