SQLite | SQLite Commands | Import CSV Data from a File (.import)

Tags:

• SQLite
• Database

SQLite can read a CSV file and import its data into a table. This article explains how to use the .import command.

Using the .import Command

Use .import to load a data file into a table. The syntax is as follows.

.import FILE TABLE

The first argument is the file name and the second is the destination table. Specify the file with either an absolute path or a path relative to the directory where the SQLite CLI was started.

Import behavior differs depending on whether the destination table already exists.

Importing into an Existing Table

First, create the destination table and add several existing rows.

create table user (id integer, name text, address text);

insert into user values (1, 'devkuma', 'Seoul');
insert into user values (2, 'kimkc', 'Busan');

sqlite> create table user (id integer, name text, address text);
sqlite> 
sqlite> insert into user values (1, 'devkuma', 'Seoul');
sqlite> insert into user values (2, 'kimkc', 'Busan');
sqlite> 

Create the CSV data shown below and save it as userdata.csv in the current directory.

3,araikuma,Seoul
4,happykuma,Seoul
5,mykuma,Daejeon

Before importing the file into user, switch to CSV mode with .mode, then run .import.

.mode csv
.import ./userdata.csv user

sqlite> .mode csv
sqlite> .import ./userdata.csv user
sqlite> 

After importing, query the user table.

select * from user;

sqlite> select * from user;
1,devkuma,Seoul
2,kimkc,Busan
3,araikuma,Seoul
4,happykuma,Seoul
5,mykuma,Daejeon
sqlite> 

The imported rows were appended to the existing data.

Importing into a New Table

When the destination table does not exist, SQLite creates it and uses the first imported row as the column names.

Create the following CSV file and save it as userdata2.csv in the current directory.

id,name,address
1,devkuma,Seoul
2,kimkc,Busan
3,araikuma,Seoul

Switch to CSV mode, then import the file into the new user2 table.

.mode csv
.import ./userdata2.csv user2

sqlite> .mode csv
sqlite> .import ./userdata2.csv user2
sqlite> 

SQLite creates user2 and stores the imported data in it.

Use .schema to inspect the generated table.

.schema user2

sqlite> .schema user2
CREATE TABLE user2(
  "id" TEXT,
  "name" TEXT,
  "address" TEXT
);
sqlite> 

The first CSV row became the column names, and every column has the TEXT type.

Next, query the user2 table.

select * from user2;

sqlite> select * from user2;
1,devkuma,Seoul
2,kimkc,Busan
3,araikuma,Seoul
sqlite> 

The CSV rows after the header were stored as table data.