Exporting a CSV With Headers From SQLite on the Command Line

SQLite's command-line shell turns a query into a CSV file with one option, which makes it the shortest path from a database to a file of any engine in this series. It also has two habits that surprise people who use it from scripts: it ends rows with Windows line endings, and it quotes more than it has to. Neither is a bug, and both can break a downstream step that assumed otherwise. This article covers the options you will use, the two habits, the way SQLite spells NULL and numbers, and a 2,000,000 row export.

The one-liner

sqlite3 -csv -header work/export/ex.sqlite "SELECT id, name, note FROM ex_people WHERE id IN (4, 5, 6) ORDER BY id" | cat -A

id,name,note^M$
4,Cy,"line one$
line two"^M$
5,Di,""^M$
6,Ed,^M$

-csv selects CSV output and -header turns the column names on. The query is a normal SQL argument and the output goes to standard output, so redirect it to a file. This output was piped through cat -A, which shows invisible characters, because the interesting parts are invisible. Row 4's note spans two lines and is wrapped in quotes, as CSV requires. Row 5, an empty string, is written as "", and row 6, a NULL, as nothing. And every row ends in ^M$, a carriage return and a line feed.

The CRLF surprise

The row ending in CSV mode is CRLF. RFC 4180 specifies CRLF as the line break, and SQLite follows it, so this is correct. It is not what a Unix script expects: wc -l still counts rows, but diff against an LF file reports every line different, a cut or awk field at the end of a row carries a trailing \r, and a value compared in a shell with == will not match. The newline inside the quoted note stays a bare LF, because that is data and not a row ending, so the file mixes the two.

To get LF, set the row separator after selecting CSV mode. There are three ways, and only some of them work:

sqlite3 -csv -header work/export/ex.sqlite "SELECT id, name FROM ex_people WHERE id <= 2" | od -c | sed -n 1,3p
echo '--- -newline with a real newline character, after -csv'
sqlite3 -csv -header -newline "$(printf '\n')" work/export/ex.sqlite "SELECT id, name FROM ex_people WHERE id <= 2" | od -c | sed -n 1,3p
echo '--- .separator with double-quoted escapes in a session'
sqlite3 work/export/ex.sqlite ".headers on" ".mode csv" '.separator "," "\n"' "SELECT id, name FROM ex_people WHERE id <= 2" | od -c | sed -n 1,3p
echo '--- or strip the carriage returns afterwards'
sqlite3 -csv -header work/export/ex.sqlite "SELECT id, name FROM ex_people WHERE id <= 2" | tr -d '\r' | od -c | sed -n 1,3p

0000000   i   d   ,   n   a   m   e  \r  \n   1   ,   "   A   n   a    
0000020   S   i   l   v   a   "  \r  \n   2   ,   "   O   '   N   e   i
0000040   l   ,       P   a   t   "  \r  \n
--- -newline with a real newline character, after -csv
0000000   i   d   ,   n   a   m   e   1   ,   "   A   n   a       S   i
0000020   l   v   a   "   2   ,   "   O   '   N   e   i   l   ,       P
0000040   a   t   "
--- .separator with double-quoted escapes in a session
0000000   i   d   ,   n   a   m   e  \n   1   ,   "   A   n   a       S
0000020   i   l   v   a   "  \n   2   ,   "   O   '   N   e   i   l   ,
0000040       P   a   t   "  \n
--- or strip the carriage returns afterwards
0000000   i   d   ,   n   a   m   e  \n   1   ,   "   A   n   a       S
0000020   i   l   v   a   "  \n   2   ,   "   O   '   N   e   i   l   ,
0000040       P   a   t   "  \n

The first block is the default: \r \n after the header and after each row. The second tried -newline "$(printf '\n')" and got no row separator at all, with the header and rows run together. Command substitution strips trailing newlines, so the argument was an empty string. The third, .separator "," "\n" with the \n in double quotes inside the dot-command, wrote plain LF. Single quotes there, '\n', would give a literal backslash and n; I tried that first, and it produced the two characters in the file. The fourth block pipes the default output through tr -d '\r', which also works and is the least fragile. It would remove a stray \r from inside a value too, so use it when you know the data has none.

If the consumer is Windows software, leave CRLF alone. If it is a Unix pipeline, convert and say so in the contract.

A session instead of flags

For several exports in one run, the dot-commands are clearer:

sqlite3 work/export/ex.sqlite <<'SQL'
.headers on
.mode csv
.once work/export/sq_once.csv
SELECT id, name, score FROM ex_people WHERE id <= 3 ORDER BY id;
.output work/export/sq_out.csv
SELECT id, note FROM ex_people WHERE id IN (5, 6) ORDER BY id;
.output stdout
SELECT 'back on the screen' AS msg;
SQL
echo "--- sq_once.csv"; cat work/export/sq_once.csv
echo "--- sq_out.csv"; cat work/export/sq_out.csv

msg
"back on the screen"
--- sq_once.csv
id,name,score
1,"Ana Silva",12.5
2,"O'Neil, Pat",0.0
3,"Bo ""Bee"" Chen",7.0
--- sq_out.csv
id,note
5,""
6,

.headers on and .mode csv set the format. .once file sends only the next query's output to the file; .output file sends everything from then on until .output stdout switches back. The last query printed msg and "back on the screen" to the terminal, in CSV, because the mode persisted. Note what the first file shows: "Ana Silva" is quoted although it contains no comma or quote, and plain and Cy are not. In this output, SQLite quoted every field that contained a space, a colon, a comma or a quote, which is more than RFC 4180 demands. Legal, and a little unexpected if you compare files byte for byte.

The shell documentation lists the dot-commands, including .mode, .headers, .once and .separator.

Options: separators, NULL, no header

sqlite3 -header -csv -separator ';' work/export/ex.sqlite "SELECT id, score, note FROM ex_people WHERE id IN (1, 6) ORDER BY id"
echo ---
sqlite3 -header -csv -nullvalue NULL work/export/ex.sqlite "SELECT id, score, note FROM ex_people WHERE id IN (5, 6) ORDER BY id"
echo ---
sqlite3 -noheader -csv work/export/ex.sqlite "SELECT id, active, score FROM ex_people WHERE id IN (1, 6) ORDER BY id"
echo ---
sqlite3 -tabs -header work/export/ex.sqlite "SELECT id, name FROM ex_people WHERE id IN (2, 3) ORDER BY id" | cat -A

id;score;note
1;12.5;plain
6;100.0;
---
id,score,note
5,3.14,""
6,100.0,NULL
---
1,1,12.5
6,,100.0
---
id^Iname$
2^I"O'Neil, Pat"$
3^I"Bo ""Bee"" Chen"$

-separator ';' changed the delimiter. -nullvalue NULL made a missing note appear as the word NULL, where the default is empty; choose a marker the data cannot contain. -noheader dropped the header row. -tabs produced tab-separated output, with the cell quoting kept, shown as ^I in cat -A.

The NULL and empty string distinction deserves a second look. In the default output, an empty string is "" and a NULL is empty, which is the same convention PostgreSQL's COPY uses (see the psql article), and a CSV reader that understands it can keep the difference. Many do not.

Types: SQLite stores what it was given

A SQLite column has an affinity, not a strict type, and the CSV shows what is stored:

sqlite3 -csv -header work/export/ex.sqlite "SELECT typeof(score) AS t, score, 1.0 * 3 AS three, 0.1 + 0.2 AS sum, 10 / 4 AS int_div, '007' AS text_num, x'4869' AS blob_col, 1e20 AS big FROM ex_people WHERE id = 1"

t,score,three,sum,int_div,text_num,blob_col,big
real,12.5,3.0,0.30000000000000004,2,007,Hi,1.0e+20

typeof(score) is real and the value is 12.5 and not 12.50, so a number you think of as money loses its trailing zero. 1.0 * 3 came out as 3.0, 0.1 + 0.2 as 0.30000000000000004, 10 / 4 as 2 (integer division) and a very large number as 1.0e+20. The text '007' stayed 007, since text is text. And the BLOB x'4869' was written as the raw bytes, which here happen to print as Hi; a binary blob will put arbitrary bytes into a text file. If the consumer needs 12.50, format it in the query with printf('%.2f', score), and hex(blob) for a blob.

Dates are the same story. SQLite has no date type; the joined column holds text, and it exports as the text you stored. Store ISO 8601 (2025-03-01 09:30:00 or with a T and a Z) and it exports readably. The dates and booleans article compares what each engine does.

Every table in the database

Backing up a SQLite database as CSV is a loop:

rm -rf work/export/sqlite_all && mkdir -p work/export/sqlite_all
sqlite3 work/export/ex.sqlite "CREATE TABLE IF NOT EXISTS tags (person_id integer, tag text); DELETE FROM tags; INSERT INTO tags VALUES (1,'new'),(1,'vip'),(3,'a,b');"
for t in $(sqlite3 work/export/ex.sqlite "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name"); do
  sqlite3 -csv -header work/export/ex.sqlite "SELECT * FROM \"$t\"" > work/export/sqlite_all/$t.csv
  printf '%-10s %s lines\n' $t $(wc -l < work/export/sqlite_all/$t.csv)
done

ex_people  10 lines
tags       4 lines

The loop lists the tables from sqlite_master, skips the internal ones, and writes table.csv for each, and prints the line count. ex_people shows 10 lines for 8 rows plus the header, because the embedded newline adds one: another reminder that wc -l is not a row count. Quote the table name in the query (\"$t\") in case a name contains a space.

When the export fails

rm -f work/export/sq_fail.csv
sqlite3 work/export/ex.sqlite -csv -header ".output work/export/sq_fail.csv" "SELECT * FROM no_such_table"; echo "exit status: $?"
ls -l work/export/sq_fail.csv | awk '{print $5 " bytes"}'

Parse error in 5th command line argument: no such table: no_such_table
exit status: 1
0 bytes

A query against a table that does not exist printed Parse error ... no such table and exited with status 1, and the .output file was left behind at 0 bytes. As with the other tools, the file's name exists even though the export did not happen. Write to a temporary name and rename on success, and check the exit status in the script. The nightly export article does both.

How fast it is

rm -f work/export/orders.sqlite
time sqlite3 work/export/orders.sqlite ".mode csv" ".import work/orders-2m.csv orders" 
sqlite3 work/export/orders.sqlite "SELECT count(*) FROM orders"
time sqlite3 -csv -header work/export/orders.sqlite "SELECT * FROM orders" > work/export/orders_from_sqlite.csv
wc -l work/export/orders_from_sqlite.csv; ls -l work/export/orders_from_sqlite.csv | awk '{print $5 " bytes"}'
head -n 3 work/export/orders_from_sqlite.csv | cat -A

real	0m3.238s
user	0m1.705s
sys	0m0.407s
2000000

real	0m3.465s
user	0m1.913s
sys	0m0.360s
2000001 work/export/orders_from_sqlite.csv
139120928 bytes
order_id,customer_id,zip,amount,created_at,status,country,note^M$
1,22313,99154,103.1,"2025-11-22 13:08:24",shipped,GB,"rush order"^M$
2,193489,34494,31.59,"2025-03-03 08:03:33",failed,BR,"leave at door"^M$

Importing the 2,000,000 row CSV into a new database took 3.2 s, and exporting it back took 3.47 s: 2,000,001 lines and 139,120,928 bytes. The first lines show the CRLF endings and the quoting of the timestamp and notes ("2025-11-22 13:08:24", "rush order"), while shipped and GB are bare. This is the whole table in one pass with a single thread. For tables that fit on one disk, SQLite is not the slow part of any pipeline.

How to tell it worked

Count the records with a CSV parser, not wc -l. Compare the count with SELECT count(*). Check the line endings with cat -A or file, and decide whether you want them. Read the file back and compare the awkward rows. For numbers that must have a fixed number of decimals, look at the strings in the file, not at what a spreadsheet displays.

When the shell's CSV is not enough

The shell writes what SQLite stores, in the shell's conventions. If the consumer needs a specific number format, a particular quoting rule, a byte order mark for Excel, or a different encoding, SQLite will not do it: do the formatting in the query, or read the table from Python and write the file with a CSV library.