SQL to CSV Converter

SQL to CSV without restoring the database

Data often travels as SQL: a backup made with mysqldump, a seed file in a repository, a snippet attached to a support ticket. To look at those rows in a spreadsheet you would normally have to restore the dump into a database server and export from there. This sql to csv converter skips that step. It reads the INSERT INTO … VALUES statements directly and writes their rows as CSV, using the column names from the statement or from the matching CREATE TABLE.

No query is executed. The SQL is parsed as text, which is why only table definitions and inserted values can be converted; statements such as SELECT or UPDATE carry no rows to extract.

Example

The tool's sample defines a table and inserts three rows in one multi-row statement:

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(255),
  country CHAR(2),
  credit DECIMAL(10,2)
);

INSERT INTO customers (id, name, email, country, credit) VALUES
  (1, 'Ava Martinez', 'ava@example.com', 'US', 250.00),
  (2, 'Noah Kim', 'noah@example.com', 'KR', 0),
  (3, 'Mia O''Rourke', 'mia@example.com', 'IE', NULL);

The CSV output:

id,name,email,country,credit
1,Ava Martinez,ava@example.com,US,250
2,Noah Kim,noah@example.com,KR,0
3,Mia O'Rourke,mia@example.com,IE,

The doubled quote in 'Mia O''Rourke' is unescaped to a single apostrophe, NULL becomes an empty field, and 250.00 is written as 250 because SQL numbers are read as numbers and trailing zeros are not kept.

What the parser understands

• INSERT and REPLACE statements, including INSERT IGNORE, INSERT OR REPLACE and trailing ON DUPLICATE KEY or ON CONFLICT clauses, with any number of value tuples.
• String literals in single or double quotes, with doubled quotes and backslash escapes such as \' and \n, plus N'…' Unicode strings from SQL Server.
• Identifiers in backticks, double quotes or square brackets. Schema or database prefixes are dropped, so `shop`.`orders` is read as orders.
• Comments (--, # and /* … */) and unrelated statements like DROP TABLE, LOCK TABLES or SET are skipped.
• Column names come from the INSERT's column list. If it has none, the columns of a matching CREATE TABLE in the same input are used; otherwise the columns are named column1, column2 and so on.

Constraints and index lines such as PRIMARY KEY (id) or KEY idx_name (…) inside CREATE TABLE are not mistaken for columns.

Options

• Delimiter – comma, semicolon, pipe or tab. Values that contain the delimiter, quotes or line breaks are quoted automatically.
• Include header row – untick it to get data lines only, for appending to an existing file.
• Quote all fields – quotes every value, which some import tools require.
• Flatten nested objects – applies when several tables are combined, see below.

Dumps with more than one table

A full dump usually contains many tables, but a CSV file holds one. When the input has inserts for several tables, they are combined into a single wide row with columns like orders.0.id, which is not what a spreadsheet user wants. Paste the CREATE TABLE and INSERT statements for one table at a time, or use SQL to JSON, which keeps each table in its own array.

Tips for common dump formats

mysqldump writes extended inserts with many tuples per statement by default, which the converter handles as they are. PostgreSQL's pg_dump writes data as COPY … FROM stdin blocks unless you pass --inserts or --column-inserts; COPY blocks are not read here. Function calls such as NOW() are copied as text, since no database evaluates them. To go the other way, from a spreadsheet export back to SQL, use CSV to SQL; to tidy the statements before converting, the SQL formatter helps.

Privacy

The SQL is parsed in your browser and is not uploaded, which matters when dumps contain customer data. Only Load URL (fetched through our server) and Save & Share (stored for the link) use the server.

Convert SQL to CSV in Python

In Python, SQL to CSV usually means running a query and writing the cursor to a file with the csv module. The example loads a small dump into an in-memory SQLite database with the built-in sqlite3 module. The detail people miss is the header: the cursor returns data rows only, so take the column names from cursor.description. NULL arrives as None and is written as an empty field, and csv.writer quotes values that contain commas.

import csv
import sqlite3
import sys

sql = """
CREATE TABLE users (id INTEGER, name TEXT, city TEXT);
INSERT INTO users VALUES (1, 'Ann', 'Paris'), (2, 'Bob, Jr.', NULL);
"""
con = sqlite3.connect(":memory:")
con.executescript(sql)
cur = con.execute("SELECT * FROM users")

writer = csv.writer(sys.stdout, lineterminator="\n")
writer.writerow([column[0] for column in cur.description])   # header row
writer.writerows(cur)

If all you have is a dump file and no database to run it on, the converter above reads the INSERT statements directly.