EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQL Tutorial SQLite export to csv

SQLite export to csv

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.

Syntax

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

.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
SPSS Training Program
41+ Hours of HD Videos
14 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
MICROSOFT AZURE
97+ Hours of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
HADOOP Training Program
170+ Hours of HD Videos
32 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
INFORMATICA Training
69+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
SQLite Tutorial
 12+ Hours of HD Videos
4 Courses
Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more