To import spreadsheet or excel data into a db file in SQLite format

I recently prepared a SQLite DB for my Flutter application, but I wanted to use the data I had entered in the spreadsheet beforehand.

Prerequisite information

• Importing directly from a spreadsheet or Excel is quite cumbersome, so let’s make it a CSV file!
• It is not possible to append CSV data to an existing db file. You need to create a new table.
• I’m using a Mac PC, so we are showing you how to do this.

1. Create a db file and connect to SQL

In the beginning, you can start a command line (Terminal on Mac), create a db file, and connect to SQL at the same time.

sqlite3 {file name}.db

Example)

sqlite3 database.db

（If successful, you will see “sqlite >” on the command line.）

sqlite >

2. Create a new table

Next, you can create a new table.

create table {table name}(
{column name} {column type},
{column name} {column type},
{column name} {column type} ...);

Example)

create table myTable(
id integer,
name text,
imagePath text,
description text);

If successful, you can see the table created with the command `.schema {table name}`.

.schema {table name}

3. Import a CSV file

At last, you can use the command `.import` to specify the csv file you want to import and the name of the table you just created to import.

.import {path to csv file} {table name}

Example)

.import /Users/taro/Documents/database.csv myTable

If successful, you can list the imported data by inputting `SELECT * from {any table name};`.

SELECT * from {any table name};

When you are done, you can disconnect to SQL by the command “.quit” or “ctrl + C”.

.quit

If you meet any errors…

There is a desktop app GUI called DB Browser for SQLite that allows you to view db files on the app.
It also seems to have a csv import function. If you don’t have a lot of columns, this will be easier for you.

BTW, in the case of the Flutter app, if the db file contains extra data such as header information, or if the data types do not match perfectly, it will not display anything without throwing an error.