A spreadsheet export into SQL INSERTs

The product list lives in a spreadsheet that someone else maintains, and you need it in a database: a local PostgreSQL for development today, the shop’s MySQL next week. The spreadsheet exports CSV. Writing the table and the inserts by hand is tedious for ten rows and error-prone for a thousand, and a throwaway script tends to get the quoting of the one product with an apostrophe in its name wrong. Convert goes from the CSV to the SQL in one step, with the types read from the data and every string quoted for the dialect you choose.

One conversion, read in two tabs

Neither CSV nor SQL is JSON, so Convert takes the scenic route and shows you the view: it reads the CSV into JSON, then writes the SQL from that. The JSON step is where to look when a column comes out with a type you did not expect.

sku,name,price
TEA-12,Assam tea,4.50
CUP-04,Stoneware cup,3

PostgreSQL, CREATE TABLE and INSERT
JSON
[
  {
    "sku": "TEA-12",
    "name": "Assam tea",
    "price": 4.50
  },
  {
    "sku": "CUP-04",
    "name": "Stoneware cup",
    "price": 3
  }
]

SQL
CREATE TABLE "products" (
  "sku" TEXT NOT NULL,
  "name" TEXT NOT NULL,
  "price" NUMERIC NOT NULL
);

INSERT INTO "products" ("sku", "name", "price") VALUES
  ('TEA-12', 'Assam tea', 4.50),
  ('CUP-04', 'Stoneware cup', 3);

comma-separated
header row
2 rows × 3 columns

MySQL, INSERT only
INSERT INTO `products` (`sku`, `name`, `price`) VALUES
  ('TEA-12', 'Assam tea', 4.50),
  ('CUP-04', 'Stoneware cup', 3);

comma-separated
header row
2 rows × 3 columns

Reading the first run

The JSON tab is the spreadsheet as records, one object per row, keyed by the header. The CSV reader decided that the price column holds numbers and the other two text, and it kept 4.50 exactly as written rather than shortening it to 4.5 — Convert never rounds a number on its way through, which matters more for prices than for anything else.

The SQL tab follows from those types. Two TEXT columns and a NUMERIC one, all NOT NULL because no row left a cell empty, then a single INSERT carrying both rows. The table is named after the file: Try it brings the source across as products.csv, while a paste arrives untitled, which is why the steps have you type the name into Table name. The three lines under the result are the CSV reader explaining itself: the delimiter it found, that the first line was taken as the header, and the size of what it read.

Reading the second run

Changing Dialect to MySQL and Statements to INSERT only rewrites the result without touching the source. The quoting changes — backticks around names instead of double quotes — and the CREATE TABLE is left out, which is what you want when the table already exists on the other side and only its rows are new. Nothing else moved: same rows, same values, same order.

Both choices are remembered for next time, along with the rest of the SQL options. That is why the steps name the dialect and the statements even for the first run, when both are at their defaults: a reader who last exported for SQLite would otherwise get a different answer from the one printed.

Before running it against a database

Look at the column types once more, in the JSON tab, before the SQL goes anywhere. A cell left empty becomes null, and its column loses NOT NULL, which may or may not be what the database should allow. Codes with leading zeros, such as 007, are kept as text so the zeros survive, but a column of plain digits becomes a number column. The inference is deliberate and visible, not clever, so the fix is usually to adjust the CREATE TABLE by hand before running it, and let the inserts stand as written.

Large exports are split into batches of five hundred rows per INSERT, so a spreadsheet of several thousand products does not produce one statement too big for the server to accept. The download button writes the SQL tab or the JSON tab, whichever is showing, as a file named after the source.

When the spreadsheet is not quite CSV

Exports from European spreadsheets often use semicolons, and some tools write tabs. Convert detects the delimiter from the text and says which one it chose in that first line under the result, so a wrong guess is visible at once. The header is guessed too, and a first line of data can be mistaken for one, so check the column names in the JSON tab; the reader’s options let you say whether there is a header. A file with a header and no rows gives the SQL writer nothing to insert, and the result says so rather than writing an empty table.

If the rows arrive as JSON from an API instead, start from JSON: the SQL card in the Convert section shows the same writer with a nested value in one column. And for YAML, the chain works exactly as it does here, with a JSON step of its own.

Where to go from here

• SQL: the three dialects, the statements and how nested values are stored.
• Convert between two formats that are not JSON: the JSON step, and what you can do with it.
• CSV and TSV: how the delimiter and the header are worked out.

Start on CSV to SQL, or Open the editor if the data needs cleaning first.