How to export the query results from a Database Manager to a CVS file in SQLite?

Answered
Avatar
Permanently deleted user
Created

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 Dabase Manager. 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.

Votes

0

Share

3 comments
Avatar
Yuriy Vinogradov
Created

Here is good article to learn how to export data in DataGrip, please read it.

Also you can just write any query like

select * from table where 1=1

right-click on it and select 'Execute to File' action, where you'll also be able to set up desired output format and target file.

0
Avatar
O Liveadams1999
Created
  1. Turn on the header of the result set using the . header on command.
  2. Set the output mode to CSV to instruct the sqlite3 tool to issue the result in the CSV mode mypascoconnect official
  3. Send the output to a CSV file.
  4. Issue the query to select data from the table to which you want to export
0
Avatar
Thewebmonster66
Created

SQLite project provides you with a command-line program called sqlite3 or sqlite3.exe on Windows. By using the sqlite3 tool, you can use the SQL statements and dot-commands to interact with the SQLite database.

To export data from the SQLite database to a CSV file, you use these steps:

  1. Turn on the header of the result set using the .header on command.
  2. Set the output mode to CSV to instruct the sqlite3 tool to issue the result in the CSV mode.
  3. Send the output to a CSV file.
  4. Issue the query to select data from the table to which you want to export.

The following commands select data from the customers table and export it to the data.csv file.

>sqlite3 c:/sqlite/chinook.db
sqlite> .headers on
sqlite> .mode csv
sqlite> .output data.csv
sqlite> SELECT customerid,
   ...>        firstname,
   ...>        lastname,
   ...>        company
   ...>   FROM customers;
sqlite> .quit

If you check the data.csv file, you will see the following output.

Besides using the dot-commands, you can use the options of the sqlite3 tool to export Tellthebell data from the SQLite database to a CSV file.

For example, the following command exports the data from the tracks table to a CSV file named tracks.csv.

>sqlite3 -header -csv c:/sqlite/chinook.db "select * from tracks;" > tracks.csv
Code language: SQL (Structured Query Language) (sql)

If you have a file named query.sql that contains the script to query data, you can execute the statements in the file and export data to a CSV file.

>sqlite3 -header -csv c:/sqlite/chinook.db < query.sql > data.csv
0

Please sign in to leave a comment.