How to use sqlite3 SQL script to export results of a query to an external text or csv file.
(1) By anonymous on 2025-02-11 03:38:36 [source]
Below is an example of a query I am using that joins three sqlite tables and displays three variables. I have seen examples of how to export using Terminal, but it would be better for me to export to a file using the SQL script. How can I do that, given that I am working with this SQL script statement? SELECT TABLE1.UI, TABLE2.AccountNo, TABLE1.Group FROM (TABLE1 INNER JOIN TABLE2 ON TABLE1.UI=TABLE2.UI) INNER JOIN TABLE3 ON TABLE2.UI=TABLE3.UI WHERE TABLE3.Status = 'A';
(2) By anonymous on 2025-02-11 04:12:01 in reply to 1 [link] [source]
.headers on .mode csv .output output_file.csv
(3) By anonymous on 2025-02-11 12:06:00 in reply to 2 [link] [source]
Thank you. Those are for the sqlite3 shell. I'm hoping to find something that will work in the SQL script. Apparently other forms of SQL have an EXPORT statement, but I don't see that on the list of sqlite statements, and it didn't work when I tried it.
(4) By Stephan Beal (stephan) on 2025-02-11 15:08:23 in reply to 3 [link] [source]
Apparently other forms of SQL have an EXPORT statement, but I don't see that on the list of sqlite statements, and it didn't work when I tried it.
SQLite's library does not have any built-in capability to export results to a file. That's a feature of its shell.
(5) By TripeHound on 2025-02-11 15:38:32 in reply to 3 [link] [source]
It would probably help if you can say what the "SQL script" is being used by.
(6.1) By Kees Nuyt (knu) on 2025-02-11 18:55:01 edited from 6.0 in reply to 3 [link] [source]
Shell redirection may work for you. Here is an example in the bash shell (unix, linux) :
sqlite3 yourdb.sqlite <yourscript.sql >yourresult.csv
and yourscript.sql would look like:
.headers on
.mode csv
SELECT TABLE1.UI, TABLE2.AccountNo, TABLE1.Group
FROM (TABLE1
INNER JOIN TABLE2 ON TABLE1.UI=TABLE2.UI)
INNER JOIN TABLE3 ON TABLE2.UI=TABLE3.UI
WHERE TABLE3.Status = 'A';
(7) By Simon Slavin (slavin) on 2025-02-12 14:13:06 in reply to 3 [link] [source]
SQLite does not include scripts. It includes no scripting language, and no stored procedures. SQLite does not understand CSV format.
However the SQLIte CLI (the shell application) does what you asked for, as documented in