Migrating a database from FileMaker Pro to SQLite

FileMaker Pro is almost certainly one of the least interoperable cases of proprietary database management software. As such, it is the worst choice you could make to manage your data as far as digital preservation goes. Period.

There is data to be salvaged out there. If you find data that you care about, you’ll want to migrate content from FileMaker Pro to another database.

Things are not necessarily easy. If you work primarily on GNU/Linux (Debian in my case) it may be even more difficult. It can be done, but not without proprietary software.

Installing FileMaker Pro

You can get a 30-days free trial of FileMaker Pro (FMP): you may need to register on their website with your e-mail address. It is a regular .exe installer.

Please note that, while other shareware programs exist to extract data from a FileMaker Pro database, there is absolutely no way to do it using free and open source software, and as far as I know nobody has ever done any reverse engineering on the format. Do not waste your time trying to open the file with another program: the FileMaker Pro trial is your best choice. Also, do not waste your time and money buying another proprietary software to “convert” or “export” your data: FileMaker Pro can be used to extract all of your data, including any images that were stored in the database file, and the trial comes at no cost if you already have one of the supported operating systems. After all, it is proprietary so it is appropriate to use the native proprietary program to open it.

Extracting data

Alphanumeric data are rather simple to extract, and result in CSV files that can easily be manipulated and imported by any program. Be aware however that you have no way to export your database schema, that is the relationships between the various tables. If you only have one table, you should not have used a database in the first place, but that’s another story.

Make sure FMP is installed correctly. Open the database file.

1. Go to the menu and choose File → Export records …
2. Give a name for the exported file and make sure you have selected Comma-separated values (CSV) as export format
3. A dialog will appear asking you to select which fields you want to export.
   • Make sure that “UTF-8” is selected at the bottom
   • On the left you see the available fields, on the right the ones you have chosen
   • Click Move all ‒ you should now see the fields listed at the bottom right. If you get an error complaining about Container fields, do not worry, we are going to rescue your images later (see below)
   • Export and your file is saved.
4. Take a look at the CSV file you just saved. It should open in Notepad or any other basic text editor. A spreadsheet will work as well and may help checking for errors in the file, especially encoding errors (accented letters, special characters, newlines inside text fields, etc.)
5. Repeat the above steps for each table. You can choose the table to export from using the drop-down list in the upper left of the export dialog.

Extracting images from a Container Field in FileMaker Pro

If, for some unfortunate reason, image files have been stored in the same database file using a Container Field, the normal export procedure will not work and you will need to follow a slightly more complex procedure:

• Exporting the Field Contents of a Container Field – How can I export multiple images from my container field?

 Go to Record/Request/Page [First]
 Loop
      * Set Variable [$filePath; Value: Get ( DesktopPath ) MyPics::Description & ".jpg"]
    ** Export Field Contents [MyPics::Picture; “$filePath”]  
        Go to Record/Request/Page [Next; Exit after last]
End Loop

*Set Variable Options: Name: $filePath
 Value: Use one of the following formulas

Mac: Get ( DesktopPath ) & MyPics::Description & ".jpg"

 Windows: "filewin:"& Get ( DesktopPath ) & MyPics::Description & ".jpg"

 Mac and Windows: Choose ( Abs ( Get ( SystemPlatform ) ) -1 ;

     /*MAC OS X*/
      Get ( DesktopPath ) & MyPics::Description & ".jpg"
 ;
      /*WINDOWS*/
      "filewin:"& Get ( DesktopPath ) & MyPics::Description & ".jpg"
 )

 Repetition: 1

 **Export Field Contents Options:
 Specify target field: Picture
 Specify output File: $filePath

Migrating to SQLite

SQLite is a lightweight, file-based real database (i.e. based on actual SQL). You can import CSV data in SQLite very easily, if your starting data are “clean”. If not, you may want to look for alternatives.

Appendix: if you are on GNU/Linux

If you are on GNU/Linux, there is no way to perform the above procedure, and you will need a working copy of Microsoft Windows. The best solution is to use VirtualBox. In my case, I obtained a copy of Microsoft Windows XP and a legal serial number from my university IT department. The advantage of using VirtualBox is that you can erase FileMaker Pro and Windows once you’re done with the migration, and stay clear of proprietary software.

Let’s see how to obtain a working virtual environment:

1. Install VirtualBox. On Debian it’s a matter of sudo apt-get install virtualbox virtualbox-guest-additions-iso
2. Start VirtualBox and create a new machine. You will probably need to do sudo modprobe vboxdrv (in a terminal) if you get an error message at this stage
3. Install Windows in your VirtualBox. This is the standard Windows XP install and it will take some time. Go grab some tea.
4. … some (virtual!) reboots later …
5. Once Windows is installed, make sure you install the VirtualBox Guest Additions from the Devices menu of the main window. Guest Additions are needed to transfer data between your regular directories and the virtual install.
6. Install the FMP trial and reboot again as needed. Then you can open the database file you need to convert and follow the steps described above