How to Import Data From a CSV File in SQLite

The SQLite database is a database management system or DBMS written in the C programming language. It is a module that creates a single-file-based database in a specified directory and stores all the databases and tables inside that single file.

A database schema refers to the logical representation of a database. Simply put, it refers to the overall structure of a database.

A database table schema refers to the structure of a table; it includes details about the attributes, their types, their default values, constraints, etc.

When some data is added to a table, that data should go along with the constraints set on the table and column types. There are various ways to load or add data to an SQLite table, such as manually using the INSERT command, an ORM or some SQL interface in any programming language.

This article will teach us how to add data to an SQLite database using CSV files.

Import CSV Data to SQLite Table

We can import data from a CSV file to an SQLite table using the following SQL commands.

.mode csv -- Setting the mode to CSV
.import data.csv my_table -- Importing the data to the specified table
.exit -- Exit from the CSV mode

The .mode csv command sets the output mode to CSV. The .import data.csv my_table command imports all the data in the data.csv file to a new table, my_table.

Column names present in the first row of the CSV file will be used as column names for the created table. Lastly, the .exit command exits from the CSV mode.

In new versions of SQLite, all the above commands can be executed in one go.

.import test.csv my_table --csv

If the CSV file doesn’t have column names in the first row, we must manually create an SQLite table and specify the schema that matches the CSV file structure.

Further, we can import the data to that table as usual. The workflow for this will be the same as follows.

create table my_table( ... ); -- Creating a new table
.mode csv -- Setting the mode to CSV
.import data.csv my_table -- Importing the data to the specified table
.exit -- Exit from the CSV mode