How to Import CSV and Pipe-Delimited Files to SQLite Tables

If you want to import CSV and pipe-delimited files into SQLite, this blog post is for you.

How to import CSV and pipe-delimited files into SQLite

1) Import CSV file into SQLite3

In our example, we will import a CSV file with US state names and abbreviations into a SQLite table states contained in a SQLite file states.db.

Download US 50 states CSV file

You can download our 50 states CSV file. It has two columns. 1. state name 1. state abbreviation

This is a snippet of the first 5 rows and last 5 rows.

Alabama,AL
Alaska,AK
Arizona,AZ
Arkansas,AR
California,CA
...
Virginia,VA
Washington,WA
West Virginia,WV
Wisconsin,WI
Wyoming,WY

Create SQLite file with states table

You should already have SQLite installed. If you do not, install it now.

Next, we will create a table with these fields:

1. name varchar(255) NOT NULL
2. abbreviation varchar(2) UNIQUE NOT NULL

Run this on the terminal to start the SQLite shell.

sqlite3 states.db

Output:

 $ sqlite3 states.db
SQLite version 3.43.2 2023-10-10 13:08:14
Enter ".help" for usage hints.
sqlite>

On the SQLite shell, run this command to create the states table:

CREATE TABLE IF NOT EXISTS states (
  name varchar(255) PRIMARY KEY NOT NULL,
  abbreviation varchar(2) UNIQUE NOT NULL
);

Run the import command

Assuming that both the csv file and the states.db file are in the same directory, run this on the SQLite shell:

.mode csv
.import states.csv states

If the states.csv file is in another directory, also include its path to the csv file:

.import /PATH-TO/states.csv states

The CSV rows would have been successfully imported into the SQLite states table.

2) Import pipe-delimited file into SQLite3

In the second part, we will import a CSV file with US state names and abbreviations into the same table states contained.

Download US 50 states pipe-delimited file

Download our 50 states pipe-delimited file. It has two columns, separated by a | pipe character. 1. state name 1. state abbreviation

This is a snippet of the first 5 rows and last 5 rows.

Alabama|AL
Alaska|AK
Arizona|AZ
Arkansas|AR
California|CA
...
Virginia|VA
Washington|WA
West Virginia|WV
Wisconsin|WI
Wyoming|WY

Truncate states table

If you have already created the states table, you can truncate it at the SQLite shell with:

delete from states;

If you have not created the states table yet, you can create it with:

CREATE TABLE IF NOT EXISTS states (
  name varchar(255) PRIMARY KEY NOT NULL,
  abbreviation varchar(2) UNIQUE NOT NULL
);

Import the pipe-delimited file

Run this command on the SQLite shell:

.separator '|'
.import states.txt states

After you do run those commands, the two columns in the pipe-delimited file will be imported as rows into the SQLite table states.

Conclusion

I have followed this process to import CSV and pipe-delimited text files into SQLite databases. If you are unable to get successful imports, let me know by adding a comment here, or you can contact me. Thanks for reading.

Related Posts

If you have any questions, please contact me at arulbOsutkNiqlzziyties@gNqmaizl.bkcom. You can also post questions in our Facebook group. Thank you.