Create a SQLite3 Database from a Spreadsheet

Recently, I needed to create a pre-populated SQLite database that will be distributed with an application I’m developing. I tried to use a visual interface initially, but I quickly hit a wall when doing the import due to the size of the database.

Since local databases are frequently used in mobile applications and offline web applications, I thought it would be good to cover how to create a SQLite database from the command line and share some helpful hints I stumbled upon along the way.

Clean the Data

In an ideal world, you’d be able to start creating the database and schema right away. Most of the time, there needs to be some degree of data formatting before doing that. A spreadsheet is a much less rigid format than SQL, so the data that is being imported into the SQLite database needs to constrain to what SQLite allows. While writing this article, I was working with a TSV (tab delimited) file that was exported from a report in a web application. Don’t open the file in Excel. Use a plain text editor such as notepad instead. Here are the steps I followed:

1. Remove illegal characters and replace spaces with underscores in headers (i.e. “email address” needs to become “email_address”.
2. Cut column headers out of the data file and put them in a separate text file. Replace the separators so they are comma separated (you’ll use these later).
3. All blank lines at the top and bottom of the file need to be removed.

Install SQLite

Normally, I develop everything on a Windows computer, but for this task, I prefer the Linux command line. On my development machine, I have VMWare Player running the current release of Ubuntu (11.10 at the time of this writing). It takes some time to download and perform the set up initially, but it is irreplaceable when you really need it. The best part is everything you need is free.

On Ubuntu, you need to first install SQLite3. To do this, run the following command from the terminal:

sudo apt-get install sqlite3

Create the Database

Once sqlite3 is installed, type the following from the command line:

sqlite3 mydatabase

This command will start sqlite and create a database called “mydatabase” if it doesn’t exist already. Then create the table by running:

create table app_data(paste in column headers in CSV format from step #2 of “Clean the Data”);

This creates a table called “app_data”. You can confirm this by running .tables from the terminal. There are a couple of things to note. First, even if you are working with a tab or pipe delimited file, the column headers have to be entered in CSV format in the create table command. Second, anything that is a true SQL command needs to get terminated by a semicolon (;), while specific SQLite commands do not, just as you saw with the .tables command. The SQLite-specific commands begin with a period.

We are almost ready to import the contents of our file. First, set the separator. The default is pipe (|) delimited. I mentioned before that my file is tab delimited, so I need to run .separator \t to set it to the tab character. You can confirm the separator by running .show.

Now we’re ready to do the import and can run:

.import filename.csv app_data

This imports the contents of “filename.csv” into the app_data table in the database.

Conclusion

SQLite is being distributed with many mobile applications and and is also used in desktop browsers for offline web applications. You can certainly create and populate a SQLite database using your favorite programming language. This is simply a quick alternative that is language agnostic.