DBF to SQLite

If you're one of those who's delayed the "database day of reckoning" when conversion to SQL can be avoided no more, take heart. The easy and obvious choice is the lightweight, serverless, open-source SQLite. Other than suffering through the inevitable code changes, you'll lose nothing in the way of security or performance by making the switch.

Creating the database and tables

Exporting DBF to CSV

To keep things simple, create the SQLite tables with structures identical to those of the DBF files. The DBF file is saved as a CSV with NO COLUMN HEADINGS and pipe characters ( | ) as field separators.

Here's a sample Visual Objects method to loop through a DBF file and output it to CSV. (Visual Objects is related to C/C++ so users of those languages can follow along.)

To import your DBF files, you'll first need to download and install SQLite, in addition to the clever SQLite browser.

For simplicity's sake, place the CSV files in the same folder where you intend to create your SQLite database. Then save this script with the extension .sql .

(We're assuming that if you're reading this, you know how to adjust your path settings on your Windows system, so we'll omit that step.)

We've included an example SQLite script to import the CSV files into identical SQLite tables. In the example you'll see that once everything is appended, empty numeric fields are filled with zeros. (Can't do the same with character fields, where empty fields are carried as NULLs, which can be a headache to someone accustomed to working in the DBF world.)

Choose or make a folder for your SQLite database (it's a *.db file). Go to the command prompt and type the following:

sqlite3 joe.db starts SQLite and creates or opens joe.db

. read vo_sql.sql this runs the script you created

. exit quit SQLite