-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).
- Open the Filemaker file you want to transfer
- Export your database as "comma separated text" (file > export records)
- Create the database structure in MySQL manually
- 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!