Populating A SQLite Database Using CSV

Up until now for item data I’ve been typing items directly into the SQLite database using DB Browser for SQLite. The process was a bit cumbersome because I didn’t enter the items in alphabetical order so to make sure I wasn’t adding duplicates it was necessary to do a SELECT * FROM Item ORDER BY Name before adding an individual item. Also, the DB Browser UI was a bit cumbersome and required a lot of flipping between screens to check and add.

But in order to make sure I have a concise but comprehensive list of items on a typical grocery list that I continuously can add to I needed a way to update the database contents in one fell swoop.

First thing I did was export my existing database items into a text file (DB Browser puts each record on a single line, separated by commas). I used Text Wrangler to remove the primary key column from each item and alphabetize the list.

Next I spent about 45 minutes walking the aisles of the local grocery store writing down items. After this I compared my new list to my original list and eliminated duplicates. Then I typed the new items at the bottom of the list, re-alphabetized, and I was ready to go.

One thing I thought about was how to enter similar items like Black Olives and Green Olives. At first I though I’d do it as Olives, Black and Olives, Green but decided to go with the comma-less versions. I want entering new items to be easy so eliminating the comma avoids the user having to flip back and forth between two keyboards (at least on the iPhone). I also thought adding the comma made things look too geeky. This approach does mean that all Olives won’t be clustered under the “O” section but I think I can live with that (we’ll see what my wife thinks when she tests the app).

FYI, I’m also thinking of maybe adding some sort of incremental search on the Add screen.

So I now needed to import my list of 120 comma-seperated values into my Items database. DB Browser didn’t have that function so after some googling I discovered that there’s a command line shell for SQLite that comes pre-installed on the Mac. Type “sqlite3” at the command prompt to start the command line utility.

sqlite3-1

Then type the following lines, hitting Enter after each one, to open up the database and import the CSV file contents into the existing Item table (prior to this I deleted all the previous data with a DELETE FROM Item SQL statement in DB Browser).

sqlite3-2

That attempt to do a direct insert fails with a datatype mismatch error because the Item table contains five columns, the first being an auto incremented primary key, and the comma separated value file contains only four items–Name, Buy, Quantity, and Label.

The solution turns out to be to import the comma separated value file into a temporary table and then use a SQL statement to copy the items from the temporary table into the actual Items table. Here are the SQLite commands to import the items into the temporary table, which is created automatically.

sqlite3-3

Then a single SQL command in DB Browser populates the Item table, generating the primary keys automatically.

INSERT INTO Item (Name, Buy, Quantity, Label) SELECT Name, Buy, Quantity, Label FROM TempItem

Here’s the original temporary table with the four columns of data imported from the CSV file.

sqlite3-4

And here’s the Item table with the automatically generated primary keys plus the four columns of data from the temporary table.

sqlite3-5

Now I can add new items to my CSV list, alphabetize the list, and easily update the SQLite database without a lot of manual overhead.

Helpful links:

This entry was posted in Side Projects, Software Development and tagged , , . Bookmark the permalink.