Is there a way I can export the results of a query into a CSV file?
-
2Export from shell, one-liner: stackoverflow.com/a/21741408/911945Anton Tarasenko– Anton Tarasenko2017-10-25 16:21:27 +00:00Commented Oct 25, 2017 at 16:21
Add a comment
|
7 Answers
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
6 Comments
Dustin
Use sqlite> .output C:/Users/jdoe/Documents/output.csv if you want to use a specific path.
Valeria Lobos Ossandón
Hi! I did this. Although my query worked perfectly, the file output is empty. Does someone knows why?
burmer
@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.
biscuit314
To get back to the default mode after
.mode csv type .mode list (or exit and restart)
onesiumus
how to achieve this with headers?
|
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
3 Comments
Stan James
Thanks for showing how to get the column names! This answer needs to be higher.
kupgov
Note that sqlite3 appends output to the end of the file without previous content erasing.
kupgov
But each
.output filename.csv execution creates or erases the file.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;
Comments
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
Comments
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
Comments
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.
1 Comment
s5s
This assumes you can hold the data in memory. And that you have a python interpreter and can install sqlite3 python package which in a corporate environment might be unavailable.
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
