SQL: migrate Filemaker to MySQL without any software

Submitted by Webmaster on January 2, 2011 - 22:32

-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 for a very specific reason: I didn't want to spend money on something I would use just once and throw away. Had I known it was so simple I wouldn't even have spent my time searching for the answer. The following method will help you save the money you would have spent on the most popular solutions available on the net, and the time I've spent to find out how to do it.

Since there is no Filemaker to SQL direct connection you will have to use a little trick. The solution is so obvious that it seems incredible that the information is so hard to find: you have to spot a file format that both filemaker and MySQL can read. The good news it that there is one!!! So why didn't they told you about it? I sincerely don't know. Now get your hands on your keyboard and start the process.

  1. Open the Filemaker file you want to transfer
  2. Export you database as "comma separated text" (file > export records)
  3. 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. Doesn't it look simple enough for anyone to spare a couple of bucks? I think we all agree. Ok, it might get a little more trickier than one two three but really, it's worth it.

The problems you might face at this point are important to point out. First and major problem of this technique: you won't be able to export images... But wait a second, who ever 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).

The second problem I had to face was that MySQL wouldn't recognize line-breaks on my csv file. The result was 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 can cause undesired line break. Then, when importing the file on phpMyAdmin tell it that lines will be terminated by ***line-break***.

The first time you do this you will also have to think about a third issue that is easy to solve but can be time consuming. You will have to type in by hand the structure of your Filemaker database entirely into MySQL before importing because .csv files will 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.

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
  • You won't be able to transfer images
  • It won't be possible/easy/reliable to switch all the time from FM to SQL

I'm sure though that you can find workarounds to all these obstacles. I can't provide general solutions for specific problems here (I wish I could). I really 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 of any step just drop a line in the comments and I'll try personally improving the explanations for everybody to enjoy it.


Comments

Submitted by David Simpson on October 9, 2011 - 18:51.
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 October 10, 2011 - 17:01.
Hello David and thank you for this in-depth analysis of the migration process.

Submitted by Leon Stafford on December 3, 2011 - 10:44.
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