Import a CSV File Into an SQLite Table
Last Updated :
25 Jan, 2024
SQLite is a lightweight, embedded relational database management system (RDBMS)which famous for its simplicity and fewer setup requirements. It is a self-contained, serverless, and zero-configuration database engine, SQLite is widely used in various applications, including mobile devices, desktop software, and embedded systems.
SQLite supports standard SQL syntax provides ACID (Atomicity, Consistency, Isolation, Durability), and ensures reliable and transactional data operations.
What is CSV?
Comma-separated values (CSV) is a widely used file format for plain-text and exchanging tabular data in a plain-text format. In CSV files, each line represents a row of data, with fields separated by commas. This simplicity and human-readable structure make CSV files easy to create, manipulate, and understand.
CSV is commonly employed for data interchange between different applications, databases, and spreadsheet software, providing a straightforward and universal means of sharing structured information while remaining lightweight and versatile.
Using SQLite CLI
Step 1: Create a Database
Before we start, make sure we have SQLite3 installed on our system. To create a new SQLite database, use the following command:
Query:
./sqlite3.exe Database.db
The above command will create a Database in the current working directory.
Step 2: Prepare CSV File
Consider the below Data in the CSV file which will be imported. The first row includes the attribute names and then the data.
Let the Absolute Location of the below import file be C:\Sqlite-Proj\Import-CSV\importFile.csv
CSV FileStep 3: Import Operation
1. Create a Table and Import Data
Change the mode to CSV using the .mode
command:
.mode csv
Copy the absolute path of the CSV File. Use the .import command to import the file
If the table does not exist then the table will be created with all the attributes of the TEXT datatype.
.import absolute_path table_name
Example:
Let the absolute path be C:\Sqlite-Proj\Import-CSV\importFile.csv ,and table name be students.
.import C:\Sqlite-Proj\Import-CSV\importFile.csv students
By executing the above commands we will get imported by the data of the csv file into the database.

2. Import Data into an Existing Table
We will create the students table using the below command.
CREATE TABLE students
(
roll_number INTEGER PRIMARY KEY,
name TEXT,
class TEXT,
percentage REAL
);
Change the mode to CSV:
.mode csv
Now as we are already having a Table, we cannot just directly import the CSV file. Executing the normal import command will raise a datatype mismatch error for the first row. To skip the first line (column names), use the following command:
.import C:\Sqlite-Proj\Import-CSV\importFile.csv students
Output:
Import the csv file (with Warning)Just to ignore the datatype mismatch error we can just skip the first line of csv file (column names) using the below command.
.import -skip 1 C:\Sqlite-Proj\Import-CSV\importFile.csv students
Output:
Import the data from csv file ignoring the first row of column namesExplanation: Importing the data into a table which is already having data will just append the data.
Using SQLStudio
Step 1: Connect to the Database
Open SQLiteStudio and navigate to the Import options from the Tools Menu bar.
Import tool of SQLiteStudioStep 2: Select Database and Table
Choose the target database and table for data import.
Select the Database and Table to importStep 3: Configure and Import
Some of the configurations are:
- Ignore Errors
- First Line represents CSV column names
- Column Seperator
- NULL Values
- Interpret " as a value quotation mark.
Browse the import file and set some configuration optionsNow Click on Finish and the data will be imported in the table.
Conclusion
SQLite's import from CSV feature provides a efficient way to populate a database with data from Comma-Separated Values (CSV) files. This feature simplify the process of transferring large datasets into SQLite databases by allowing users to easily map CSV file columns to corresponding database table columns. This capability enhances the versatility of SQLite, making it a practical choice for applications that require the unlimited integration of structured data from CSV files.
Similar Reads
How to Import a CSV File into R ?
A CSV file is used to store contents in a tabular-like format, which is organized in the form of rows and columns. The column values in each row are separated by a delimiter string. The CSV files can be loaded into the working space and worked using both in-built methods and external package imports
3 min read
How to Import a CSV file into a SQLite database Table using Python?
In this article, we are going to discuss how to import a CSV file content into an SQLite database table using Python. Approach:At first, we import csv module (to work with csv file) and sqlite3 module (to populate the database table).Then we connect to our geeks database using the sqlite3.connect()
3 min read
How to import an Excel File into R ?
In this article, we will discuss how to import an excel file in the R Programming Language. There two different types of approaches to import the excel file into the R programming language and those are discussed properly below. File in use: Method 1: Using read_excel() In this approach to import th
3 min read
How to list the Tables in a SQLite Database File ?
SQLite is a database engine which is written in C programming language. SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is
4 min read
Python SQLite - Deleting Data in Table
In this article, we will discuss how we can delete data in the table in the SQLite database from the Python program using the sqlite3 module. In SQLite database we use the following syntax to delete data from a table: DELETE FROM table_name [WHERE Clause] To create the database, we will execute the
2 min read
How to Import Data From a CSV File in MySQL?
Importing data from a CSV (Comma-Separated Values) file into a MySQL database is a common task for data migration and loading purposes. CSV files are widely used for storing and exchanging tabular data. However, we cannot run SQL queries on such CSV data so we must convert it to structured tables. I
10 min read
How to import CSV file in SQLite database using Python ?
In this article, we'll learn how to import data from a CSV file and store it in a table in the SQLite database using Python. You can download the CSV file from here which contains sample data on the name and age of a few students. Approach: Importing necessary modulesRead data from CSV file DictRead
2 min read
Export SQLite Database To a CSV File
SQLite is an embedded database that doesn't use a database like Oracle in the background to operate. It is written in C language and is used by developers who embed a lightweight database over the existing application, browser, or embedded systems. The main features of SQLite are that it is a tiny,
5 min read
Create Table From CSV in MySQL
CSV (Comma Separated Value) files are a type of file containing data frames that are separated by a comma (generally). These files are textual in format and aren't confined to a specific program or standard, due to which they are widely used. It is quite common for data frames to be stored in form o
3 min read
Python MySQL - Insert into Table
MySQL is a Relational Database Management System (RDBMS) whereas the structured Query Language (SQL) is the language used for handling the RDBMS using commands i.e Creating, Inserting, Updating and Deleting the data from the databases. SQL commands are case insensitive i.e CREATE and create signify
3 min read