Since I’ve been working most of my time with iPhone Dev for the last couple of months, I thought it will be worthwile to post how to import files to SQLite3. In a nutshell, use MesaSQLite’s IMPORT function. It will save you a lot of time. Now it is up to you how to export into a CSV or TAB delimited file. For Oracle, I used SQLDeveloper. For other databases, such as MySQL or Postgres, I’ve used Navicat.

  1. Using SQLDeveloper -> Right Click on the Table from the left pane -> Export

oracle-sqldeveloper-left.gif

oracle-sqldeveloper.gif

  1. Choose TEXT since we are going to use that when importing using MesaSQLite. Note, SQLDeveloper does not export CLOB columns. You need to change your column from CLOB to VARCHAR. A workaround is to add a column and then update that column aferwards.
ALTER TABLE poi ADD en_desc VARCHAR(2000);
UPDATE poi SET en_desc = en_short_desc;
  1. In MesaSQLite, FILE -> IMPORT -> CSV/TAB

mesa-1.gif

  1. Choose the table which you want to import.

  2. Choose your exported TAB delimited file from SQLDeveloper.

  3. Afterwards, map the corresponding fields to your database. Note, that I added an extra column “dummy” in the file because it seems I can only match n-1 columns, thus the extra column.

mesa-2.gif