How to migrate the database from Filemaker to Mysql?

Question

I am rebuilding an ERP system based on Symfony1.4 and MySQL 5.1. The challenge is that previous system was built on Filemaker Pro and I have to migrate all previous data to current system. For that, first I need to move all the data to a MySQL DB having the previous schema structure intact and then I can map the data to the current system schema by writing a script as needed.

How should I proceed with that first step? Is there any existing tools or processes to do that?

Any help will be appreciated! Thanks in advance!

Solution 1

The simplest way is to export data from FileMaker into some common format. To do this you need to open the file in FileMaker and for each table you need go to the layout associated with the table and use the menu to show all records and export.

Make sure to only export data fields (Text, Number, Date, Time, and Timestamp), because it's typical for FileMaker to have lots of calculated fields (Calculation and Summary). (To do this first go to File - Define Database, then to some table, sort files by type, and note the last data field.

This won't export container fields, but most apps don't store such data. It's still possible to export them too but it would require a custom script.

The next option is to use ODBC. It's 'next' because it's less convenient and usually slower.

If you don't have a copy of FileMaker, you can download a 30-day trial from their site; it's fully functional.

Solution 2

I'm doing this in Java at the moment using JDBC. You need to import the JDBC driver and MySQL into your library. I'm currently looking in my other posts for the best practices to processing this large amount of data.

You might have to make the tables yourself in MySQL, and I might go as far as to recommend it, because FMPro typically has a strange setup, that you might not want to copy exactly (I noticed on the most recent one text fields have to be set to a length too or things just go haywire..). An easy afternoons work in MySQL Developer (or whatever they call it now) drawing some nice diagrams?

Here's some cheats:

public static String dbaseURL = "jdbc:filemaker://machineIP:2399/database";

public static void FMProConnect() {

// Load the JDBC to ODBC driver
try {

    Class.forName("com.filemaker.jdbc.Driver");

} catch(java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
}
}
public static void copyTable(){
FMProConnection.FMProConnect();
Connection dbConnection = null;
Statement query = null;
PreparedStatement stmt = null;
    try {
      // Create a database connection
  dbConnection =
    DriverManager.getConnection(dbaseURL, "fmprouser", "fmpropassword");
  // Create a statement and execute the SQL query
  query = dbConnection.createStatement();
}
catch (SQLException e) {
  System.out.println("Error connecting to dbase.");
  e.printStackTrace();
  //System.exit(1);
}

ResultSet results = null;
try {

  results =
    query.executeQuery("SELECT * from table");

  // Iterate through the results and print them to standard output
     Connection con = DriverManager.getConnection("jdbc:mysql://mysqlserver.com/database","username","password");

  while (results.next()) {
    String fname = results.getString("field");
    String lname = results.getString("field1");
 // System.out.println("Found user \"" + fname + " " + lname + "\"");
  stmt = con.prepareStatement("INSERT ignore INTO table (field, field1 values (?, ?)");
  stmt.setString(1, fname);
  stmt.setString(2, lname);
  stmt.executeUpdate();

  }
  System.out.println("Completed Customers");
}
catch (SQLException e) {
  System.out.println("Error retrieving data from database.");
   e.printStackTrace();
  //System.exit(1);
}


}

Solution 3

Converting without Software: Here is a link to a well written article showing how to transfer data from FileMaker Pro using no software at all.

[http://drilix.com/en/tutorial/sql-migrate-filemaker-mysql-without-any-software][1]

File Access Permissions: It is possible to have FileMaker database files with no visible export capability. Within FileMaker, custom menus can be implemented to disable the export features. To resolve this type of issue, you need to log into the database using the Admin account password, having [Full Access] privileges. Then you can select the menu: Tools->Custom Menus->[FileMaker Standard FileMaker Menus] Once you select this menu, all of the regular menus will be available.

FileMaker Binary File Format: It is important to realize that the FileMaker database uses a proprietary binary file format to store its data. None of these files can be read on any Linux or UNIX operating system directly, since the file format has not been made public. I know of only one person outside of FileMaker Inc. who has successfully reverse engineered the modern version of the file format (.fp7, .fmp12 versions).

This means that to extract data from a FileMaker database, you must always have the FileMaker software running on MacOSX or Windows in order to extract the data. This is completely different from reading Access .mdb/.accdb files, for which open source alternatives are available.

ODBC vs File Exports: There are some important limitations to exporting data from FileMaker to any non-native file format. There can be loss of UTF8 formatted data, truncation of data with some formats, and issues with repeating fields data. This is why I recommend making a direct connection to FileMaker via ODBC, and transferring the data directly to MySQL (or any other database you choose).

What are repeating fields? A repeating field in FileMaker is similar to storing an array of data within a single field of a single record. I generally recommend separating out this data into related records, related by the the primary key of the parent record. The perl scripts linked above, accomplish this task. But you must prepare the data within FileMaker in advance. Since FileMaker no longer supports repeating fields via their ODBC driver, you need to create a script in FileMaker to move all of the repeat values into the first repeat value. So if you have a field of repeating values like this:

Field1[1]="abc"
Field1[2]="def"
Field1[3]="ghi"

You move the data into:

Field1[1]="abc"<TAB>"def"<TAB>"ghi"

Then you can iterate thru the TAB delimited values within Field1[1] to write the data into the related table.

Comments

[Jesse Ivy]

My first thought was XML, and the grammars, they look a bit complex. Any suggestions about apps that help with the process?