SQLite Export

SQLite facilitates you to export data from SQLite database to CSV file. You can export the whole table or less according to your query.

.once command is used to export data to a CSV file followed by the file path/name where you want to write the file.

SQLite Table to CSV file

Let's see an example which will export all the contents of the STUDENT table to a CSV file:

The STUDENT table has the following data:

snippet

.header on
.mode csv
.once /Users/rookienerd1/Desktop/sqlite/student.csv
SELECT * FROM STUDENT;

Code explanation:

.header on: It specifies that headers are enabled. This is optional. If you disable headers, the CSV file simply won't contain any data.

.mode csv: It specifies that CSV mode is enabled.

.once: It specifies that the output to be written to the CSV file and next is the exact location.

After execution of the above code, a CSV file is created on the specified location:

It is having the same data of SQLite STUDENT table.

.system command is used to automatically open the CSV file.

For example:

The following command open the CSV file automatically in Windows:

snippet

.system /Users/rookienerd1/Desktop/sqlite/student.csv

This code may be changed according to operating system:

On Windows, use .system followed by the file name.
On Mac, use .system open followed by the file name.
On Linux and Unix systems, use .system followed by the name of the program to open the file, followed by the file name. For example, .system libreoffice /file.csv