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

已回答

Permanently deleted user

创建于 2021年03月15日 11:15

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.

3 条评论

Yuriy Vinogradov

创建于 2021年03月16日 14:06

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.

O Liveadams1999

创建于 2021年05月14日 09:30

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

Thewebmonster66

创建于 2021年09月09日 04:03

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:

Turn on the header of the result set using the .header on command.
Set the output mode to CSV to instruct the sqlite3 tool to issue the result in the CSV mode.
Send the output to a CSV file.
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

请先登录再写评论。