CSV to SQL, without losing the zeros

When you need it, and why a script is better

The CSV comes out of the management software, an online form or a Google sheet, and it has to end up in a database. Import wizards decide the types by themselves and when they get it wrong they do not say so: you notice months later, when a postcode has lost its zero.

Here instead you get an SQL script: a CREATE TABLE with the types already chosen and the INSERT statements with every row. It is text you can read before running it, paste into phpMyAdmin, DBeaver or psql, and run again the same way on another computer. And the table never leaves your browser: no upload, no account.

What becomes a number and what stays text

It is the lesson of CSV to Excel, and in a database it weighs more, because there a wrong type is not fixed with one click: a postcode like «00185» in an INTEGER column becomes 185, and the zero never comes back. That is why a column stays text if it has leading zeros, if they are whole numbers of 15 digits or more (a card, an IMEI: they are codes), if there is a + in front of a row of digits (an international phone number), if they look like IBANs or Italian tax codes, if there is a value already wrecked into scientific notation such as «1.2E+15», and if the column name says postcode, code, phone or staff number and there are only digits inside.

The content decides the rest: INTEGER if they are all whole numbers, DECIMAL with the right digits if there are decimals, BOOLEAN if there are only yes and no, true and false. The decimal separator is counted in the file: «1.234,56» is continental, «1,234.56» is English. If no number says it and «1.250» reads two ways, the column separator decides (with semicolons the decimals have a comma, with commas a dot), and the page writes it down. The numbers reach the SQL with the digits of the file, without going through floating point.

If you pick a type that a value cannot hold, for instance whole number on a column that contains «n/a», the page says which value and in which row, and it does not write the SQL.

Dates, only when the order is certain

«03/04/2024» is the 3rd of April for a European and the 4th of March for an American, and the file does not say which. That is why a column of dd/mm/yyyy dates becomes DATE, rewritten year-month-day as every database reads it, only if the whole column is unambiguous: at least one date must have the day above 12, and none may contradict the order. Otherwise it stays text and the page says so; if you know the order, you pick it from the menu. Dates already written year-month-day always pass, two-digit years never do, because «24» does not say the century.

SQLite has no real date type: it keeps the date as text, and its functions only understand the year-month-day form, which is what comes out of here. SQL Server gets DATE and not DATETIME, which reads the same text according to the session language.

Four databases, four ways of writing the same row

Names go in double quotes in SQLite and PostgreSQL, in backticks in MySQL, in square brackets in SQL Server, and the apostrophe of «O'Brien» is doubled everywhere. In MySQL and MariaDB the backslash is a special character too: written as it is, «C:\temp» becomes «C:», a tab and «emp». Here the backslash is doubled, the script starts with SET NAMES utf8mb4 because the old «utf8» refuses emoji, and texts become at least VARCHAR(100): under 64 characters InnoDB keeps the column inside the row, and with forty such columns the row goes past the 8,126 bytes it can hold.

SQL Server wants the N in front of strings, otherwise the letters outside its code page turn into question marks, uses BIT for yes and no (1 and 0, as in SQLite) and accepts at most a thousand rows in a single INSERT. That is why the rows go in groups, five hundred per statement unless you choose otherwise, and every INSERT stays under one megabyte, except a row that weighs more on its own. The INSERT statements sit in a transaction, and in SQL Server with SET XACT_ABORT ON, without which an error cancels only its own statement: if the run stops, no half-filled table is left behind. The DROP TABLE IF EXISTS only goes together with the CREATE TABLE, and in SQL Server it exists since version 2016.

Column names become real names

«Date of birth», «Città» and «Price (€)» become date_of_birth, citta and price: letters without accents, digits and underscores, which can be written in a query without quotes. A word the database keeps for itself, such as order, group, user or key, gets an underscore at the end, otherwise the first SELECT written by hand would throw an incomprehensible error. Two columns with the same name become name and name_2, a name starting with a digit gets «c_» in front, and past 63 characters, the PostgreSQL limit, it is shortened. The mapping is in view, and every name can be rewritten by hand.

Empty cells: NULL or empty text

For a database an empty cell can be two things: NULL, the absence of a value, or '', a text of length zero. COUNT(phone) does not count the NULLs, and WHERE phone = '' does not find them. For text columns you choose; in columns of numbers, dates and yes or no an empty cell is always NULL, because '' is neither a number nor a date. The same goes for a cell holding NULL, or \N as in MySQL exports: there it cannot mean anything else. In a text column it might be the word, and it stays so, between apostrophes, unless you tick the box for it.

What it does not do, said plainly

It does not connect to any database and it runs nothing: it writes the script. It creates no primary keys and no indexes, which depend on how you will use the table. A number with the euro sign or the percent sign stays text, and so do dates with a time; in SQLite also decimals past 15 digits, which its floating point does not keep. It does not read Excel files: for those the first step is Excel (XLSX) to CSV. If the CSV is messy, it is worth fixing it first with Clean up a CSV. And if you need it for a program and not for a database, the right road is CSV to JSON.

And in all honesty: the test for this tool really runs the script on SQLite, on PostgreSQL and on MariaDB, which behaves like MySQL here, and reads the rows back from the table. SQL Server is not available here: its script is checked by a parser written by other people, but no real SQL Server has run it. And if NO_BACKSLASH_ESCAPES is on in your MySQL, the doubled backslashes stay doubled.