drilix.com

Freedom, Community & Sustainability

SQL: migrate Filemaker to MySQL without any software

January 2, 2011 -- Willynux
Last modified on August 2014
Duration: +- 15 minutes

-Transfer Filemaker files into an SQL database for free-

At first this task can look quite daunting. If you hit a search engine for transforming your local Filemaker database into a dynamic site driven by, say MySQL, you get a bunch of paid programs or extensions. Among these options you find the ODBC translation (ODBC stands for Open DataBase Connectivity, a sort of common language among databases), FmPro Migrator (a program among others that do the migration) or using Filemaker instant web publishing. None of these options were good for me because I didn't want to spend money on something I would use just once and throw away.

We will need a little trick since there is no Filemaker to SQL direct connection. We will use a file format that both filemaker and MySQL can read: CSV (Comma Separated Value).

  1. Open the Filemaker file you want to transfer
  2. Export your database as "comma separated text" (file > export records)
  3. Create the database structure in MySQL manually
  4. Import the file.csv directly into MySQL using phpMyAdmin

Once you get the data into MySQL you can export it in an SQL file and use it in most databases. Good enough isn't it? Well, it might get a little trickier but really, for a one time migration, it's worth it.

The first time you do this you will have to think about the biggest inconvenient of this method: you have to type in by hand the structure of your Filemaker database entirely into MySQL before importing. This is because .csv files only contain the content and not the column names and properties. The structure will have to be exactly the same, of course, for you to have the same database (same number of fields, same order of fields too, same type of data etc.). Once you have done this, never go back to csv, prefer to save your database in SQL file format for better compatibility among databases. Also remember that once you typed the database manually you can import from your filemaker file as many times as you want as long as both database structures remain the same.

Another inconvenience of this method is that you won't be able to export images... But wait a second, who wants to put real images into filemaker? Files get extremely heavy, takes ages to open and sometimes won't even work. That's another reason for you to migrate from Filemaker (although it's a good program).

It also happened to me that MySQL wouldn't recognize line-breaks on the csv file. The symptom is a database with just one row containing thousands of entries squeezed into a few fields. The solution is simple: open the csv file in any text editor and use the function find and replace to transform all line brakes into something that phpMyAdmin can read, like for example "***line-break***". Be sure to chose a sequence of characters that you won't have in your database otherwise the entries of your database could cause undesired line breaks. Then, when importing the file on phpMyAdmin tell it that lines will be terminated by ***line-break***.

In a few words, the pros and cons:

  • It's free!!! You won't have to pay a dime to transfer data from FM to SQL
  • This process is easier than having to learn to use a software to do it
  • But... you won't be able to transfer images
  • It wouldn't be possible/easy/reliable to switch all the time from FM to SQL

I'm sure though you can find workarounds to all these obstacles. I can't provide general solutions for specific problems here (I wish I could). I hope this was useful for you as it would have been for me at the time I needed some advice. If you have questions about the details, drop a line in the comments!

Comments

As the developer of the FmPro Migrator software, I appreciate that you have written this useful and informative article. And as you mentioned in the article, this technique is a perfect solution for small and uncomplicated FileMaker database files.

Your readers may be interested to know about some of the other challenges which can affect a successful FileMaker conversion project.

Large Numbers of Fields - It seems that if you have a large table containing hundreds of fields, the CSV/TAB delimited file exporting process breaks down. Columns might get mixed up and data can get lost. I have not trusted this process as being a reliable process. This is why I always use an ODBC connection to retrieve the data from FileMaker, and I use a direct connection to MySQL to insert the records. Using an ODBC connection makes it possible to transfer gigabytes of data reliably between databases.

Image Fields - There are white-papers available on the internet explaining why database servers are not an efficient way to store and serve images for a website. In fact, there is a popular product used in the FileMaker marketplace called SuperContainer from 360 Works, which stores images outside the database too.

I make it possible for developers to export FileMaker images directly to data files on their local disk or to store them within MySQL BLOB columns.
However if you have a small database you are using for a hobby or very small business, it probably doesn't matter very much. It is probably easier in these cases to just store the images in BLOB columns within the database. Especially since I can generate all of the PHP code necessary to manage these images via a web browser.

Repeating Fields - Within FileMaker Pro, there are fields called repeating fields, which represent a non-relational way of storing data within a field. You can actually think of a repeating field as being similar to a PHP or JavaScript array from a programming perspective. Each repeating value is separated by a specific ASCII character within the database. MySQL won't know what to do with this info so it will get lumped together within the same field, and won't be very usable. Repeating fields data should be placed into a separate related table, referencing the Primary Key of the parent table, and this is what I do during the conversion process.

In fact, I would suggest extracting the repeating fields data into MySQL, re-importing that data back into FileMaker and building a relationship to the related table. Then replace the repeating fields with Portals on the FileMaker layouts. This way it is possible to automate the conversion of the portals into PHP/JavaScript based Grid objects to create a professional looking web application.

Getting Field-size Info - There is no easy way to figure out how much data is stored within the fields of a FileMaker database. This information is vital for the purpose of creating the SQL code to build the tables within MySQL. If you guess wrong, MySQL will silently truncate the data when performing the data transfer. In some cases, this is Ok, but if the information was vital to your application, it could be disastrous.
So what I do is read all of the records from the database table in order to count up the maximum number of bytes used for each field. Then I build the table creation SQL code based upon using this field-size metadata. I like for my table structure to be neat and tidy, so I build the field sizes in increments of 25. This means that if you have a maximum of 45 characters stored within a field, I increase that size to become a VARCHAR(50). It is likely that you might need that extra space anyhow someday, and it doesn't cost anything if it isn't used. Fields larger than 255 chars get created as LongText columns.

Relationships - When dealing with relationships and a web application, you have a couple of choices. If you are using a PHP framework, you may want to build and manage the relationships within the framework instead of the database itself. Otherwise, if you are manually writing some simple PHP scripts, you might allow the database to handle the referential integrity.

Generating PHP Code for Layouts - So now that you have your data transferred to MySQL - what are you going to do about providing a GUI for your data? I have figured out how to read the XML definition for each layout object, and use that metadata to generate the PHP code for a working PHP web application. There are a lot of great looking GUI objects available on a FileMaker layout which can be translated directly into web objects. These objects include: charts, tab controls, static and dynamic value lists (using database data), portals (converted to grids), vector graphic objects (ovals/circles, rectangles, rounded rectangles, lines), checkboxes, radio buttons, date picker, static image objects, buttons, tooltips and merge fields.
If you were writing the code manually, you might have a tendency to skip adding these features to your web application because they would be too time consuming to implement.

Submitted by Webmaster on

Hello David and thank you for this in-depth analysis of the migration process.

--webmaster--

We are in the process of tidying up a mess of a FM system before we can seriously think about migrating, but my web programming background would be more comfortable with it all migrated to a web system... This post I just browed to gave me some insights. Cheers, Leon

Add new comment