Development:ZionWorx - Song Data Format

From OpenLP
Jump to: navigation, search

Recently there was developed a windows tool ZionWorx Export to export songs from ZionWorx into xml based data format OpenLyrics. Songs in OpenLyrics format can be then imported directly by OpenLP.

This page is outdated and does not apply to the current development version!

Importing songs from ZionWorx 2.6

Make a Backup


Getting data out of ZionWorx

ZionWorx 2.6 uses a propetiary TurboDB database, so you need to download a tool to convert the ZionWorx database into a CSV File. The easiest way seems to be using Dataweb TurboDB Data Exchange. You can download it from TurboDB download page (Windows users, who don't feel comfortable using cmd, may want to use the TurboDB Studio, and make an CSV export from there.)

After downloading the software, move the executable into your ZionWorx Data folder, which has the file "MainTable.dat", after that run the program on command line with the arguments: MainTable.dat songstable.csv -fsdf -s, -q\". (On Linux it would go like: ./tdbdatax MainTable.dat songstable.csv -fsdf -s, -q\") Now you have csv file which includes all the songs from your ZionWorx database.

(Note: You may experience some problems with the character encoding, eg. have a # between all the words at this point, so it's wise to check the file with some text editor eg. Writer, and remove these errors.)

Getting data into SQLite database

The Next thing to do is to append the data into the existing openLP database. OpenLP uses SQLite 2.8 so we need a program that can import CSV files into a SQLite Database. We'll use Sqlitebrowser v 1.1 to do the trick (the newer ones don't support SQLite 2.8 anymore). You can download the program from the projects sourceforge page. Remember to download the 1.1 release! (Note: Linux users who don't like to compiling can run it with wine as I did. Worked nicely.)

After downloading, run the program. The next thing to do is to open the openLP database from the openLP Data folder (on Windows XP it's something like C:\Documents and Settings\All Users\Application Data\\Data\songs.olp). After that import the csv file: File > Import > Import Table from CVS file. Select the songstable.csv and name the table as "import". The other options should be fine by itself.

Getting data into OpenLP tables

Now we have the Zionworx data in the openLP SQLite database, but on the wrong table, so we need to move the data. Select the "Execute SQL tab, and execute the following commands, on at a time:

(Note: with these queries, You'll lose the theme data.)

INSERT INTO authors (authorname) SELECT DISTINCT Field5 FROM import;

INSERT INTO songs (songtitle, lyrics, copyrightinfo, settingsid) SELECT Field2, Field4, Field6, authors.authorid FROM import, authors WHERE import.Field5 = authors.authorname;

INSERT INTO songauthors (authorid, songid) SELECT settingsid, songid FROM songs;

UPDATE songs SET settingsid = 1;

(Note: If You have used The Databaweb TurboDB Studio instead of Data Exchange, the Field1, Field 2, etc. may be different.)

After checking that the data got into the songs, authors and songauthors tables you can delete the import table from the database with the "DELETE TABLE" button.

Have A party!

If everything went right, you now have a fully functional OpenLP database with all the songs which you already had on ZionWorx!