Exporting a SQLite Query Result to CSV From the Shell

Getting data out of SQLite as CSV is a single flag. What is less obvious is what the file looks like: which values are quoted, how a NULL is written, what ends a line, and whether the file will survive a trip into another database. SQLite 3.54 reworked its output modes and the CSV rules are now explicit settings, so it is worth looking at them once instead of trusting a memory of an older release. This article exports a small table with the awkward values in it and shows the result byte by byte, then a round trip through Python and back into SQLite. Going the other way is in the import guide.

The shortest export

The table has four rows: a name with a space, a NULL email and balance, an empty name and email, and a name with a comma.

sqlite3 -csv -header export.db "SELECT * FROM c"

id,name,email,balance
1,"Ana Silva",[email protected],1.5
2,Bo,,
3,"","",0.0
4,"Smith, Jane",[email protected],2.0

-csv sets the output mode and -header prints the column names. The query goes in as a command line argument and the redirect captures it. Inside a session the same thing is .headers on, .mode csv and .once out.csv before the query, where .once sends only the next query's output to a file:

sqlite3 shop.db ".headers on" ".mode csv" ".once /tmp/out.csv" "SELECT id, name, balance FROM customers" && cat /tmp/out.csv

id,name,balance
1,"Ana Silva",120.5
2,"Smith, Jane",0.0
3,"Bo Chen",""
4,"Cy Dubois",15.75

Use .output file instead to send everything that follows to a file until you run .output with no argument. .once is safer, since a forgotten .output quietly swallows the rest of your session.

The rules the export follows

Read the first output carefully. Row 1 is 1,"Ana Silva",[email protected],1.5: the name is quoted and the email is not. Row 2 is 2,Bo,,: no quotes on Bo, and the NULL email and balance are empty fields. Row 3 is 3,"","",0.0: the empty strings are written as "". Row 4 is 4,"Smith, Jane",[email protected],2.0.

So the rule is not "quote everything" and not "quote only what the RFC requires". In SQLite 3.54, text is quoted when it contains a space or a character that CSV treats specially, and when it is empty. The settings behind it are visible with .mode -v:

sqlite3 export.db ".mode csv" ".mode -v" | tr ' ' '\n' | grep -E -A1 '^--(null|quote|rowsep|colsep)$' | paste -sd' ' | sed 's/ -- /\n/g'

--colsep ","
--null "" --quote csv
--rowsep "\r\n"

The CSV mode uses --colsep ",", --null "", --quote csv and --rowsep "\r\n". Each of those is an option of .mode in 3.54. I was able to get the quoting rule only by running experiments, since the documentation for the new --quote option names its values and not the exact rule, so treat my description as what the shell does today and check it against a test file before relying on it. The shell documentation covers output modes and is the place to look first when a release changes them.

NULL against an empty string

The distinction between a NULL and an empty string is the one worth keeping. The export keeps it: NULL is an empty field, and an empty string is "". The --null option changes how NULL is written, so a downstream tool that expects a marker can have one:

sqlite3 export.db ".headers on" ".mode csv --null NULL" "SELECT * FROM c WHERE id IN (2, 3)"

id,name,email,balance
2,Bo,NULL,NULL
3,"","",0.0

With .mode csv --null NULL, the NULL email and balance in row 2 are written as the word NULL, and the empty strings in row 3 stay "". Some tools read NULL as a string and some as a missing value, so check the receiver. The text NULL in a numeric column makes most loaders fail, as PostgreSQL's NULL option does unless you tell it that NULL is the marker.

An aside on aliases: the word nothing is a keyword in SQLite's upsert syntax, so SELECT NULL AS nothing is a syntax error, which I found out when I used it as a column name for this test. Quote it or pick another:

sqlite3 -csv -header export.db "SELECT '' AS empty_string, NULL AS missing"

empty_string,missing
"",

The "" for the empty string and the empty field for NULL are visible in the second line.

Quoting options

--quote controls how text is written. The three values I tried, on the rows with a space and a comma:

for q in csv relaxed on; do echo "--quote $q"; sqlite3 export.db ".headers on" ".mode csv --quote $q" "SELECT * FROM c WHERE id IN (1, 4)"; done

--quote csv
id,name,email,balance
1,"Ana Silva",[email protected],1.5
4,"Smith, Jane",[email protected],2.0
--quote relaxed
id,name,email,balance
1,Ana Silva,[email protected],1.5
4,Smith, Jane,[email protected],2.0
--quote on
id,name,email,balance
1,'Ana Silva','[email protected]',1.5
4,'Smith, Jane','[email protected]',2.0

csv is the default and gave "Ana Silva" and "Smith, Jane". relaxed wrote Ana Silva and Smith, Jane without quotes, which makes the fourth row 4,Smith, Jane,[email protected],2.0 with an extra comma, and a CSV reader sees five fields. on, an alias for sql, wrote single quoted SQL literals, 'Ana Silva', which is not CSV at all. Leave the default alone for a file that will be read by anything else.

Line endings

The shell ends every row with \r\n, as the setting says. A hex dump shows it:

sqlite3 -csv -header shop.db "SELECT id, name FROM customers LIMIT 2" | xxd | head -4

00000000: 6964 2c6e 616d 650d 0a31 2c22 416e 6120  id,name..1,"Ana 
00000010: 5369 6c76 6122 0d0a 322c 2253 6d69 7468  Silva"..2,"Smith
00000020: 2c20 4a61 6e65 220d 0a                   , Jane"..

After name comes 0d 0a, and after each record. That matches the line ending in RFC 4180, and it means a CSV written on Linux by SQLite has Windows line endings. Most readers cope. A shell loop or a Unix tool that splits on \n leaves a carriage return glued to the last field, as the line endings article explains. If you need LF, set --rowsep "\n" on the mode, or convert with a CSV aware tool afterwards, not sed, which would also change a CRLF inside a quoted field.

A byte order mark for Excel

Excel on Windows reads a UTF-8 file as a legacy code page unless it starts with the three bytes of a BOM, as the article on opening UTF-8 CSV in Excel explains. The shell can write one: .once --bom adds it to the file it writes.

sqlite3 shop.db ".headers on" ".mode csv" ".once --bom /tmp/bom.csv" "SELECT id, name FROM customers LIMIT 2" 2>&1; head -c 8 /tmp/bom.csv | xxd

00000000: efbb bf69 642c 6e61                      ...id,na

The first bytes are ef bb bf followed by id,na. There is also an .excel command that writes a temporary file and opens it in the spreadsheet program, with the same --bom option. Use the BOM only for files meant for Excel; other tools see it as part of the first column name.

A round trip, and what it loses

The final check is the one that matters: does the file read back as what was written? Python reads it exactly as the quoting rules say:

sqlite3 -csv -header export.db "SELECT * FROM c" > /tmp/exp.csv
python - <<'PY'
import csv
for row in csv.reader(open("/tmp/exp.csv", newline="")):
    print(row)
PY

['id', 'name', 'email', 'balance']
['1', 'Ana Silva', '[email protected]', '1.5']
['2', 'Bo', '', '']
['3', '', '', '0.0']
['4', 'Smith, Jane', '[email protected]', '2.0']

Row 2 has '' for the email and balance, and row 3 has '' for the name and email: Python returns an empty string for both an empty field and "", so the distinction does not survive Python's reader. The other direction, importing the file into SQLite again:

rm -f again.db
sqlite3 again.db ".import --csv /tmp/exp.csv c" "SELECT id, name, email IS NULL AS email_null, email = '' AS email_empty FROM c ORDER BY id"

1|Ana Silva|0|0
2|Bo|0|1
3||0|1
4|Smith, Jane|0|0

email_null is 0 for every row and email_empty is 1 for rows 2 and 3. The NULL email of Bo became an empty string, because .import loads an empty field as an empty string. A NULL exported by SQLite does not come back as a NULL when SQLite imports it. If you need the round trip to be exact, use a marker with --null and convert it back in SQL with NULLIF, which is the same advice as for every other engine in this section.

How to tell it worked

Compare the number of lines of the file, minus the header, with SELECT count(*), taking care that quoted newlines make wc -l larger. Open the file with a CSV reader, not an editor, and compare a few rows. Look at the first bytes with xxd when the destination is Excel. And re-import into a scratch database and check the types of columns, since the file has none.

What the export cannot preserve

CSV has no types, so an integer, a text value that looks like one and a float with a trailing .0 are all text on the way out, and 2 became 2.0 for a REAL column in this export. Blobs are written as text in a form that depends on --blob-quote, and you should avoid CSV for them. Dates are whatever strings the table holds, since SQLite has no date type. And a text value that contains a line break is written correctly inside quotes and read correctly by a real CSV reader and wrongly by wc -l, head and sort. If the consumer needs types, nesting or blobs, export JSON instead, which the SQLite shell also supports with .mode json.