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

Question

I'm new to SQLite. 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 310 · Accepted Answer · edited Jun 27 '14 at 20:04

310

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

319
1
18

answered May 20 '11 at 20:01

gdw2

6,980
4
43
47

2

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
@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
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 166 · Answer 2 · edited Mar 19 '14 at 06:57

166

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

15,588
13
53
65

answered Jul 30 '13 at 05:21

Sai Bharath

1,661
1
10
2

3

Thanks for showing how to get the column names! This answer needs to be higher. – Stan James Aug 02 '13 at 17:20
Note that sqlite3 appends output to the end of the file without previous content erasing. – kupgov Nov 20 '16 at 17:42
2

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

score 31 · 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 12 · 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 6 · 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.

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

6 Answers6

Linked