Import CSV to SQLite: A Guide to Importing Data

If you’re working with data stored in CSV files, you may want to import that data into a SQLite database for more efficient storage, querying and analysis. SQLite is a lightweight, file-based relational database that’s well-suited for many applications. This guide will walk you through the process of importing CSV data into a SQLite database using practical examples.

To import a CSV file into a SQLite database, first create the destination table if needed. Then in the SQLite prompt, set the import mode to CSV using .mode csv, and run the .import command specifying the CSV file path and target table. SQLite will create the table automatically if it doesn’t exist and the CSV file contains a header row with column names. Use additional options to skip lines, handle errors, and specify the delimiter and quote characters as needed.

Create a Sample SQLite Database

Let’s start by creating a simple SQLite database and table to work with. Open up a terminal or command prompt and run the following commands:

sqlite3 mydb.db

SQLite version 3.39.4 2022-09-29 15:55:41
Enter ".help" for usage hints.

sqlite> CREATE TABLE users (
   ...>   id INTEGER PRIMARY KEY,
   ...>   name TEXT,
   ...>   email TEXT
   ...> );

This creates a new SQLite database file named mydb.db in the current directory and a table named users with three columns: id, name, and email.

Preparing CSV Data

Now let’s create some sample CSV data to import. Create a new file named users.csv with the following contents:

1,John Doe,[email protected]
2,Jane Smith,[email protected] 
3,Bob Johnson,[email protected]

Each line represents a row of data, with values separated by commas. The first line contains the data for user with ID 1, name “John Doe”, and email “[email protected]“.

Importing CSV Data

With our database and CSV file ready, we can now import the data. Go back to the SQLite prompt and run:

sqlite> .mode csv
sqlite> .import users.csv users

The first command sets the import mode to CSV. The second command imports the data from users.csv into the users table.

And that’s it! The data from the CSV file is now in the SQLite database. You can verify this by running a SELECT query:

sqlite> SELECT * FROM users;
1,"John Doe","[email protected]"
2,"Jane Smith","[email protected]"
3,"Bob Johnson","[email protected]" 

Handling Column Names

In the previous example, our CSV file didn’t include a header row with column names. SQLite assumed the order of columns based on how we defined the table. But what if the CSV file includes column names?

Let’s create a new CSV file users_with_header.csv:

id,name,email
1,John Doe,[email protected]
2,Jane Smith,[email protected]
3,Bob Johnson,[email protected]

To import this file, you don’t need to create the table first. SQLite can create the table automatically based on the header row:

sqlite> .mode csv
sqlite> .import users_with_header.csv users_with_header

This creates a new table users_with_header with columns id, name, and email, and imports the data.

Specifying Column Data Types

When SQLite automatically creates a table during import, it assumes the TEXT data type for all columns. To specify different data types, create the table first with the desired schema:

sqlite> CREATE TABLE users_typed (
   ...>   id INTEGER PRIMARY KEY,
   ...>   name TEXT,
   ...>   age INTEGER
   ...> );

Then import the data to the pre-created table:

sqlite> .mode csv 
sqlite> .import users_typed.csv users_typed

Where users_typed.csv contains:

1,John Doe,30
2,Jane Smith,25
3,Bob Johnson,45

Handling CSV Delimiter and Quote Characters

By default, SQLite assumes comma (,) as the column delimiter and double-quote (") as the quote character for CSV files. If your file uses different conventions, you can change them using the .separator command.

For example, if the file uses semicolon (;) as the delimiter:

sqlite> .separator ";"

To use a different quote character, e.g., single-quote ('):

sqlite> .import -q "'" users.csv users

Skipping Lines During Import

Sometimes you may want to skip a certain number of lines at the beginning of the CSV file, e.g., if it contains a multi-line header. Use the -skip option:

sqlite> .import -skip 2 users.csv users 

This skips the first two lines of the file during import.

Handling Errors

By default, SQLite aborts the import if it encounters any errors, such as constraint violations or data type mismatches. To ignore errors and continue importing, use the -ignore option:

sqlite> .import -ignore users.csv users

Examples of Real-World Use Cases

Here are a few real-world scenarios where importing CSV data into SQLite can be useful:

1. Analyzing Sales Data: Say you run an e-commerce store and have exported sales data to a CSV file. You can import this file into SQLite to run queries and generate reports.

Sample sales.csv:

order_id,product,quantity,price
1,Widget,5,10.0
2,Gadget,2,25.0
3,Widget,10,10.0

Import:

sqlite> CREATE TABLE sales (
   ...>   order_id INTEGER PRIMARY KEY,
   ...>   product TEXT,
   ...>   quantity INTEGER,
   ...>   price REAL
   ...> );
sqlite> .mode csv
sqlite> .import sales.csv sales

Analysis:

sqlite> SELECT product, SUM(quantity) AS total_qty, SUM(quantity * price) AS revenue 
   ...> FROM sales
   ...> GROUP BY product;
Widget,15,150.0
Gadget,2,50.0

2. Managing User Records: Many applications need to store user information. You can keep this data in CSV files and import it into SQLite for management.

Sample users.csv:

username,full_name,email
jdoe,John Doe,[email protected]
jsmith,Jane Smith,[email protected]
bjohnson,Bob Johnson,[email protected]

Import:

sqlite> .mode csv
sqlite> .import users.csv users

Query:

sqlite> SELECT * FROM users WHERE username = 'jdoe';
jdoe,"John Doe",[email protected]

Comparison of CSV Import Methods

Conclusion

Importing CSV data into SQLite is a straightforward process using the .import command. By following the steps outlined in this guide and leveraging the various options and techniques, you can easily transfer data from CSV files into a SQLite database for efficient storage, querying, and analysis. SQLite’s simplicity and wide support make it a great choice for many data management tasks.

Whether you’re working with sales records, user information, sensor readings, or any other type of structured data, the workflow remains the same: prepare your CSV files, create the appropriate tables in SQLite, and import the data using the .import command with any necessary options.