I have been working on a project for quite some time that requires the export of data from FM Pro to MySQL. This is not an easy enterprise. Unless you know what to do and where to look that is. But, after some extensive work and several mistakes, I have managed to get my data exported from FM Pro and imported into MySQL with no problems. While I don’t expect a huge outcry from folks trying to do this, I suspect it will help some intrepid person who is also stuck working on a similar export/import.

So, the steps are as follows:

  1. Remove all commas (,) from the FileMaker Pro fields.
  2. Replace with semi-colon.
  3. Remove all double-quotes from FileMaker Pro fields.
  4. Replace all single-quotes with ‘ in FileMaker Pro fields.
  5. Replace all commas(,) to csv file.
  6. Export as comma separated values (csv).
    1. Confirm data fields are identical to existing MySQL databases.
    2. Change to ASCII (DOS) file format.
    3. Make sure “Running Count” field is before first field,”Section Type.”For some unclear reason, MySQL does not read the first data field. This will allow MySQL to ignore the first field and then read the next one, which is the one we want read first.
  7. Create new MySQL db on DB server.
  8. Copy sql file with commands to create new database tables over to DB server.
  9. Login to Dreamhost server via SSH.
    1. ssh me@host.server.com
  10. Navigate to folder with sql file.
  11. Connect to MySQL db.
  12. Execute the sql script file on the MySQL command line.
    1. LOAD DATA LOCAL INFILE ‘exported_file.csv’ REPLACE INTO TABLE `test_table` FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES STARTING BY ” TERMINATED BY ‘\r’;
    2. Confirm that data has been uploaded into the correct table and field.