SQL ↔ CSV

2. How to Convert CSV to SQL INSERT Statements

Here is how to convert csv to sql insert statements with the tool above. The panel starts with a small sample so you can see the output first.

1. Add the CSV. Paste it or drop a file onto the panel. The first row must be the header.
2. Pick the dialect. MySQL / MariaDB, PostgreSQL, SQLite or SQL Server.
3. Set the table and batch size. Choose a table name from my_table, data, records or import, choose 1, 100 or 1000 rows per INSERT, and leave Include CREATE TABLE ticked if the table does not exist yet.
4. Convert, then copy or download. Press Convert; Download saves the script as inserts.sql.

If your real table has another name, find-and-replace it afterwards. The delimiter is detected for you, so a semicolon delimited csv to sql job from a European-locale Excel is picked up automatically, and so does tab separated values to sql insert straight from a spreadsheet copy; comma and pipe are detected too. Quoted fields and line breaks inside cells follow RFC 4180.

Wondering how to generate insert statements from Excel? Save the sheet as CSV first. If you only have the .xlsx or .ods file, the spreadsheet converter turns it into CSV, which you can then paste here.

3. A Worked Example Across Four Dialects

The built-in sample includes a name with both an apostrophe and a comma:

id,name,score,active
1,Ayesha,91.5,true
2,"O'Neil, Bilal",78,false

As csv to mysql insert online output, the same script you would use for csv to mariadb insert, it becomes:

CREATE TABLE `my_table` (
  `id` INTEGER,
  `name` VARCHAR(32),
  `score` DOUBLE PRECISION,
  `active` BOOLEAN
);
INSERT INTO `my_table` (`id`, `name`, `score`, `active`) VALUES
  (1, 'Ayesha', 91.5, TRUE),
  (2, 'O''Neil, Bilal', 78, FALSE);

For csv to postgresql insert statements, and for SQLite, the only change is double-quoted names. SQL Server gives a csv to sql server insert script with bracketed names and T-SQL types and booleans:

CREATE TABLE [my_table] (
  [id] INTEGER,
  [name] VARCHAR(32),
  [score] FLOAT,
  [active] BIT
);
INSERT INTO [my_table] ([id], [name], [score], [active]) VALUES
  (1, 'Ayesha', 91.5, 1),
  (2, 'O''Neil, Bilal', 78, 0);

Because score mixes whole and decimal numbers, it gets a decimal type: the csv to create table sql generator logic in miniature.

4. NULLs, Leading Zeros, Apostrophes: The Import Rules

Most failed imports come down to a handful of awkward values. These are the exact rules the converter follows.

• Apostrophes are escaped. The classic sql insert apostrophe error single quote in string comes from a value like O’Neil closing the string early. Every text value is wrapped in single quotes and any apostrophe inside is doubled: 'O''Neil', which all four dialects accept.
• Empty cells become NULL. If your csv import empty values not null mysql problem is blanks arriving as empty strings or zeros, this fixes it: an empty cell, or the literal text null or NULL, is written as SQL NULL.
• Leading zeros are kept. The leading zeros lost csv import sql headache happens when 007 or a postal code is treated as a number. Here a value with a leading zero stays quoted text, as does a very long digit string, so nothing is rounded or trimmed.
• Booleans follow the dialect. true/TRUE and false/FALSE become TRUE and FALSE, or 1 and 0 on SQL Server, which has no boolean literal.
• CREATE TABLE types come from the data. A column of whole numbers is INTEGER; any decimals make it DOUBLE PRECISION (FLOAT on SQL Server); all true/false values make it BOOLEAN (BIT on SQL Server). Everything else is VARCHAR sized from the longest value, between 32 and 255, or TEXT when a value runs past 255 characters.

One more case worth knowing: a column that mixes numbers and text, say mostly numeric codes with the odd N/A, gets a VARCHAR in the CREATE TABLE, but its numeric-looking values are still written unquoted in the INSERTs. Most databases accept that, but if yours is strict about types, replace placeholder text such as N/A with an empty cell before converting, so it becomes NULL and the column keeps a numeric type.

5. Rows per INSERT, and When LOAD DATA Is Not Allowed

Rows per INSERT controls how many rows share one VALUES list. 1 gives one statement per row, slowest to run but easiest to debug. 100, the default, turns a large CSV into bulk insert statements with multiple rows each, the sensible choice for most imports. 1000 means fewer, larger statements.

The 1000 setting matters for SQL Server. If you have hit the sql server insert values more than 1000 rows error, that is a documented limit on the Transact-SQL table value constructor, which accepts at most 1000 rows per VALUES list. All three batch sizes stay within it.

This also answers how to import csv into mysql without load data infile: when a host says load data infile not allowed, plain INSERTs still work. For csv to sql for phpMyAdmin, use the Import tab, or paste smaller scripts into the SQL tab. For how to import csv into sqlite, choose the SQLite dialect and run the file with the sqlite3 command line. And for csv to sql seed data, the downloaded inserts.sql drops straight into a fixtures folder, and the SQL formatter will lay it out to your team’s style.

6. SQL to CSV: Getting Rows Out of a Dump

Set Conversion to SQL INSERT to CSV and the tool becomes a sql insert to csv converter. It reads every INSERT INTO table (columns) VALUES (...) statement, including multi-row VALUES lists, and merges repeated INSERTs for the same table. Name quoting is removed, escaped apostrophes are restored and NULL becomes an empty cell. The result downloads as rows.csv.

That covers how to extract data from sql dump to csv without restoring the backup, and answers how to open sql file in Excel: Excel cannot read INSERT statements, but it opens the CSV this produces. A mysqldump insert to csv job works because mysqldump writes exactly this kind of multi-row INSERT.

Know the limits before you start:

• First table only. A dump with several tables exports the first one found; paste another table’s INSERTs on their own to get it.
• INSERT statements only. PostgreSQL’s COPY ... FROM stdin blocks, CREATE TABLE definitions and query result printouts are not read.
• No column list, generic headers. Without a column list in the INSERT, headers are named col1, col2 and so on; rename them in your spreadsheet.
• Comma-separated output only. The CSV always uses commas, quoting a value only when it needs it. There is no semicolon or tab option; if your spreadsheet expects semicolons, use its import dialog and choose comma as the separator.
• Escapes. Doubled apostrophes ('') and backslash apostrophes (\') are turned back into a single apostrophe. Other backslash sequences, such as an escaped line break, are left as written, so check long text columns.

Need JSON instead? Take the CSV to the CSV to JSON converter, then the XML converter and YAML converter if needed. To compare two exports, use the diff checker.

7. Why Cortexis Is the Best Free CSV to SQL Converter

Plenty of pages offer a csv to sql converter online free. Few tell you what the script will contain. Here is what is different.

It is one of a wider set of free data and document tools on the same terms, from the JSON formatter to the line tools for tidying a column of values.

8. Frequently Asked Questions (FAQ)