SQL: migrate Filemaker to MySQL without any software
-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.
- Open the Filemaker file you want to transfer
- Export you database as "comma separated text" (file > export records)
- 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.