SQLite: How do I save the result of a query as a CSV file?

Question

Is there a way I can export the results of a query into a CSV file?

Export from shell, one-liner: https://stackoverflow.com/a/21741408/911945 — Anton Tarasenko, Oct 25 '17 at 16:21

score 336 · Accepted Answer · edited Jun 27 '14 at 20:04

336

From here and d5e5's comment:

You'll have to switch the output to csv-mode and switch to file output.

sqlite> .mode csv
sqlite> .output test.csv
sqlite> select * from tbl1;
sqlite> .output stdout

edited Jun 27 '14 at 20:04

lippertsjan

309
1
18

answered May 20 '11 at 20:01

gdw2

7,558
4
46
49

3

Use sqlite> .output C:/Users/jdoe/Documents/output.csv if you want to use a specific path. – Dustin Mar 29 '16 at 14:01
Hi! I did this. Although my query worked perfectly, the file output is empty. Does someone knows why? – Valeria Lobos Ossandón Oct 22 '18 at 13:44
1

@ValeriaLobosOssandón this just happened to me, so i thought I'd respond. Either you don't have rights to edit the output file (unlikely), OR if you are viewing the CSVs in Excel, and have another Excel file open, even with your test.csv file closed, Excel will still lock it. In that case you would have to close all Excel windows first. – burmer Feb 06 '20 at 17:25
3

To get back to the default mode after `.mode csv` type `.mode list` (or exit and restart) – biscuit314 Jun 09 '20 at 14:25
how to achieve this with headers? – onesiumus Jan 06 '21 at 01:49

score 183 · Answer 2 · edited Mar 19 '14 at 06:57

183

To include column names to your csv file you can do the following:

sqlite> .headers on
sqlite> .mode csv
sqlite> .output test.csv
sqlite> select * from tbl1;
sqlite> .output stdout

To verify the changes that you have made you can run this command:

sqlite> .show

Output:

echo: off   
explain: off   
headers: on   
mode: csv   
nullvalue: ""  
output: stdout  
separator: "|"   
stats: off   
width: 22 18

edited Mar 19 '14 at 06:57

robguinness

16,266
14
55
65

answered Jul 30 '13 at 05:21

Sai Bharath

1,831
1
11
2

5

Thanks for showing how to get the column names! This answer needs to be higher. – Stan James Aug 02 '13 at 17:20
2

Note that sqlite3 appends output to the end of the file without previous content erasing. – kupgov Nov 20 '16 at 17:42
3

But each `.output filename.csv` execution creates or erases the file. – kupgov Nov 21 '16 at 06:10

score 43 · Answer 3 · answered Jan 13 '17 at 17:51

In addition to the above answers you can also use .once in a similar way to .output. This outputs only the next query to the specified file, so that you don't have to follow with .output stdout.

So in the above example

.mode csv
.headers on
.once test.csv
select * from tbl1;

score 27 · Answer 4 · answered May 27 '13 at 22:16

27

Good answers from gdw2 and d5e5. To make it a little simpler here are the recommendations pulled together in a single series of commands:

sqlite> .mode csv
sqlite> .output test.csv
sqlite> select * from tbl1;
sqlite> .output stdout

answered May 27 '13 at 22:16

crsierra

1,045
10
6

score 19 · Answer 5 · answered Apr 12 '20 at 02:13

Alternatively you can do it in one line (tested in win10)

sqlite3 -help
sqlite3 -header -csv db.sqlite 'select * from tbl1;' > test.csv

Bonus: Using powershell with cmdlet and pipe (|).

get-content query.sql | sqlite3 -header -csv db.sqlite > test.csv

where query.sql is a file containing your SQL query

score 7 · Answer 6 · answered Sep 18 '20 at 20:34

All the existing answers only work from the sqlite command line, which isn't ideal if you'd like to build a reusable script. Python makes it easy to build a script that can be executed programatically.

import pandas as pd
import sqlite3

conn = sqlite3.connect('your_cool_database.sqlite')

df = pd.read_sql('SELECT * from orders', conn)
df.to_csv('orders.csv', index = False)

You can customize the query to only export part of the sqlite table to the CSV file.

You can also run a single command to export all sqlite tables to CSV files:

for table in c.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall():
    t = table[0]
    df = pd.read_sql('SELECT * from ' + t, conn)
    df.to_csv(t + '_one_command.csv', index = False)

See here for more info.

score 0 · Answer 7 · answered Feb 08 '23 at 00:54

While .mode csv and .output (or .once) commands are fine, they only work in SQLite command-line interface (aka sqlite.exe on Windows).

If you are not using the CLI, you can still write to an output file with the help of sqlean-fileio extension, using fileio_append(path, string) function.

For example:

create table people(id integer primary key, name text);

insert into people(id, name) values
(11, 'Alice'), (12, 'Bob'), (13, 'Cindy');

select sum(
    fileio_append('people.csv', format('%d,%s', id, name) || char('10'))
  )
from people;

After that, people.csv will have the following content:

11,Alice
12,Bob
13,Cindy

SQLite: How do I save the result of a query as a CSV file?

7 Answers7

Linked