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

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
sqlite3 database.db
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} ...);
create table myTable(
id integer,
name text,
imagePath text,
description text);
.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}
.import /Users/taro/Documents/database.csv myTable
SELECT * from {any table name};
.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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store