CODEWITHSUNDEEP
sqlitecsv
RayLoveless
RayLoveless

Reputation: 21058

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

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

Upvotes: 267

Views: 185524

Answers (7)

Anton Zhiyanov
Anton Zhiyanov

Reputation: 382

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

Upvotes: 1

Powers
Powers

Reputation: 19308

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.

Upvotes: 10

Alexis
Alexis

Reputation: 1593

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

Upvotes: 29

Mat
Mat

Reputation: 661

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;

Upvotes: 56

gdw2
gdw2

Reputation: 8016

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

Upvotes: 366

Sai Bharath
Sai Bharath

Reputation: 2001

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

Upvotes: 200

crsierra
crsierra

Reputation: 1045

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

Upvotes: 27

Related Questions