How to export the query results from a Database Manager to a CVS file in SQLite?
I am using DataGrip or SQLiteStudio (database managers) to run a series of queries in a database which guide me to find the information that I require. The queries works well and the results are shown in the console of the Database Manager. NomNomNow However, I need to export the results that appears in the database manager console into a CVS file.
I have seen everybody works directly in the shell, but I need (I have to) to use a DB manager to run the queries (so far the queries that I need to run in one step are about 600 lines).
In the sqlite3 shell I am able to run (and works) (.headers on) (.mode csv) (.output C:/filename.csv) (select * from "6000_1000_Results";) (.output stdout)
However, running this code in the sql editor of the DB manager, doesn´t work at all.
--(.....)
--(around 600 lines before)
--(.....)
"Material ID",
"Material Name",
SUM("Quantity of Material") Quantity
FROM
"6000_1000_Results_Temp"
GROUP BY
"DataCenterID", "Material ID";
------------------------------------------------------------
--(HERE IS WHERE I NEED TO EXPORT THE RESULTS IN A CVS FILE)
------------------------------------------------------------
.headers on
.mode csv
.output C:/NextCloudLuis/TemproDB.git/csvtest.csv
select * from "6000_1000_Results";
.output stdout
.show
DROP TABLE IF EXISTS "6000_1000_Results_Temp";
DROP TABLE IF EXISTS "6000_1000_Results";
Datagrip do not show any error, it runs the queries in a few seconds, but there´s no file anywhere, SQLiteStudio gives a syntax error.
Please sign in to leave a comment.
The SQLite "." commands are not part of the SQLite language but instead they are intercepted and interpreted by the sqlite3 command line program itself.
To export a query to CSV you have several options:
1. Open the query in console, right-click, select execute to file. From there you can specify the extractor to use and the location of the file.
2. Execute the query in the console then select export data from the console toolbar.
3. Create an external tool to execute the SQL file using SQLite3.exe. You can find external tools under the Tools in settings.
4. If you can create a view you can right-click on it in the select to export the data.
I thought I would offer some screenshots in the event you were going to go the external tool route.
I did find that the external tool isn't allowing me to pipe in a file so using a batch file as a workaround.
1. Store the sqlite dll and command line tools in a folder.
2. In that same folder create a batch file with this content:
sqlite3.exe <%1
3. Create an external tool that executes the batch file and passes $FilePath$
4. Execute the script from Tools|External Tools