EDUCBA

Home Data Science Data Science Tutorials SQL Tutorial SQLite export to csv

SQLite export to csv

Updated March 10, 2023

SQLite export to csv

Introduction to SQLite export to csv

SQLite provides a different kind of functionality to the user. SQLite export to csv is also one of the functionalities in which that user can convert the database into the cvs file by using different commands. Basically, there are different ways to dump the database, or we can say convert it into the csv file as well we have different commands to export the database. SQLite project gives you a command-line program that we call sqlite3 or sqlite3.exe on Windows. By utilizing the sqlite3 extension, we can use SQL statements and different dot commands to convert the SQLite database to a csv file.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax

.header on
.mode csv
.once/path/specified name with csv extension
select * from specified table name.

Explanation

In the above syntax, we use different parameters as follows.

.header: First, we need to enable the headers by using the dot command. That means it will consider the column name as a header in the csv file. This parameter is optional of this syntax. We can disable this option.

.mode csv: After that, we need to enable the csv mode by using the above-mentioned command.

.once: It is used to write content into the csv by using this command, and it also contains the path of the csv file that means where we need to store the csv file.

The last query actually writes the content by using the select clause.

How to export to csv in SQLite?

Now let’s see how we export the table into the csv file in SQLite as follows.

To export data into the csv file, we need to follow some steps as follows.

  1. Enable the header by using the dot command that means the .header command.
  2. After that, we need to set the output mode of the file that is csv mode.
  3. In the third step, we need to send the output to a specified CSV file.
  4. Issue the query to select information from the table to which you need to export.

Rules for dot command (.tables)

Normal SQL statements are freestyle, and can be spread across various lines, and can have whitespace and remarks anyplace. However, speck orders are more prohibitive:

  • A dot command should start with the “.” at the left edge with no previous whitespace.
  • The dot command should be completely contained on a solitary info line.
  • A dot command can’t happen in a standard SQL statement. As such, a dot command can’t happen at a continuation brief.
  • Dot command doesn’t perceive remarks.

The dot command is deciphered by the sqlite3.exe order line program, not by SQLite itself.

Examples

Now let’s see the different examples of export to csv for better understanding as follows.

First, we need to create the new table by using the following statement as follows.

create table comp_worker(worker_id integer primary key, worker_name text not null, worker_age text, worker_address text, worker_salary text);

Explanation

In the above example, we use the create table statement to create a new table name as comp_worker with different attributes such as worder_id, worker_name, worker_age, worker_address, and worker_salary with different data types as shown in the above example. The end output of the above statement we illustrate by using the following screenshot as follows.

.table

SQLite export to csv output 1

Now insert some record for function implementation by using the following insert into the statement as follows.

insert into comp_worker(worker_id, worker_name, worker_age, worker_address, worker_salary) values(1, "Jenny", "23", "Mumbai", "21000.0"), (2, "Sameer", "31", "Pune", "25000.0"), (3, "John", "19", "Mumbai", "30000.0"), (4, "Pooja", "26", "Ranchi", "50000.0"), (5, "Mark", "29", "Delhi", "45000.0");
select * from comp_worker;

Explanation

In the above statement, we use to insert it into the statement. The end output of the above statement we illustrate by using the following screenshot as follows.

SQLite export to csv output 2

Now we can perform the export operation as follows.

First, we need to enable the header by using the following dot command as follows.

.header on

After that, we set the mode of the export mode CSV file by using the following dot command as follows.

.mode csv

Now assign some names to the csv file by using the following command as follows.

.output sample.csv

Now use SQL statement to write the data into the csv file by using the following SQL statement as follows.

select worker_id, worker_name, worker_age, worker_address, worker_salary from comp_worker:

Explanation

In the above statement, we use a select clause to write the data into the csv file from the comp_worker table and finally execute the dot quite command (.quit).

Now check the sampl.csv file in the SQLite folder and print something like the below screenshot.

output 3

In the below screenshot it shows where the csv file is created as follows.

output 4

Now let’s see how we can use join in export as follows.

First, create another table by using the following statement as follows.

create table worker_details(dept_id integer primary key, worker_pincode text not null, worker_id text);

Now insert some records for implementation as follows.

insert into worker_details(dept_id, worker_pincode, worker_id) values(11,"100240", "1"), (22,"100250", "2"), (33,"200240", "3"), (55,"500240", "4");

Now we have records in both tables now, let’s see how we can export both tables into the csv file by using the join as follows.

First, perform the same command as the above example.

.header on
.mode csv
.output demo.csv
select worker_name, worker_address, worker_pincode, worker_salary from comp_worker as cw inner join worker_details as wd on cw.worker_id = wd.worker_id order by worker_name;

Explanation

In the above example, we use a join constraint to generate the csv file from two different tables that are the comp_worker and worker_details, that we already created. In this example, we try to combine the data from these two tables.

Now check the demo.csv file in the SQLite folder and print something like the below screenshot.

output 5

Conclusion

We hope from this article you have understood about the SQLite Export to CSV. From the above article, we have learned the basic syntax of Export to CSV statements, and we also see different examples of Export to CSV. We also learned the rules of Export to CSV. From this article, we learned how and when we use SQLite Export to CSV.

Recommended Articles

We hope that this EDUCBA information on “SQLite export to csv” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQLite
  2. SQLite Commands
  3. SQLite COUNT
  4. SQL COALESCE