Best way to migrate data from OpenOffice DB to SQLite?

Hi all, I am in the process of porting and old Oobase app to Xojo. I am building a new Xojo app and will use the SQLite built-in DB. The old Oobase data can be exported to a number of formats, including comma delimited. I then figure that I will need to write a small Xojo app to parse the exported data and Insert it into the new SQLite tables.
Has anyone done something similar? Is this the best approach? Any advice will be gratefully received!

Well, the only way I know, is going to the BASE menu Tools->SQL

then execute (to export the base sql to a proper temporary place): script ‘c:\myproper-place-on-windows\sql-exported.sql’

  • or for a unix like script ‘/myproper-place-on-the-os/sql-exported.sql’
  • make proper adjustments

The exported definitions and data are for HSQL ( )

Now you will need to edit the SQL to make it SQLite compatible and run it using a good SQLite DB editor of your taste.

There is another possible way, not tested by me, you could try:

Hi Rick,
Many thanks for the suggestions!

This is not my options.

You said you can export your "source base’ to comma delimited in a text file. I prefer tab delimited, but it is nearly the same.

Export your current data base contents to comma delimited text file.
In your current project (or another who will do not have code: better to debug), add an import method that

loads (TextInputStream.ReadLine) one line at a time in a loop (… Not TIS.EOF …),
parse the line and fill a Record (comma delimited is useful here to get atom and fill Column)
then add the Record.

When done, you are … done !

Feel free to ask if this is not enough clear.

Well, the need described is conversion. Porting a DB for a new version in SQLite. That does not necessarily involve Xojo programing.
Both pointed process by me lets you convert not only the data, with correct data types (with some adjustments in some cases), but all associated structures like index, keys, relations, etc all at once. As a matter of taste, I still prefer the shortest path less error prone method. :slight_smile:

Again, as last poster, I could not edit to correct my mistypings, just after posting, even refreshing the page, and I don’t know why. :stuck_out_tongue:

All useful ideas - many thanks!

Try this, it’s 100% free, watch the video at the bottom of the blog post