How to transfer a database from Filemaker to Mysql?

Question

I am rebuilding an ERP system based on Symfony1.4 and MySQL 5.1. The problem is that the previous system was built on Filemaker Pro, and I need to transfer all the previous data to the current system. To do this, first I need to move all the data to a MySQL database that has the old schema structure, and then I can map the data to the current system schema by writing a script as needed.

How should I continue this first step? Are there any existing tools or processes for this?

Any help would be appreciated! Thanks in advance!

Answer 1

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

Be sure to export only data fields (text, number, date, time, and timestamp), since FileMaker typically has many calculated fields (calculation and summary). (To do this, first go to File - Define Database, then to some table, sort the files by type and pay attention to the last data field.

This will not export container fields, but most applications do not store such data. You can still export them, but this will require a custom script.

The next option is to use ODBC. This is the “next” because it is less convenient and usually slower.

If you do not have a copy of FileMaker, you can download a 30-day trial version from your site; It is fully functional.

Answer 2

I am doing this in Java at the moment using JDBC. You need to import the JDBC and MySQL driver into your library. I am currently reviewing in my other posts recommendations for processing this large amount of data.

You may have to create tables yourself in MySQL, and I can go so far as to recommend it, because FMPro usually has a strange setting that you might not want to copy exactly (I noticed in the very last text the fields should also be set to length, or things just go crazy ..). Are the easy days at MySQL Developer (or what they call it now) make up some good diagrams?

Here are 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); } } 

Answer 3

Convert without software: Here is a link to a well-written article that shows how to transfer data from FileMaker Pro without using software.

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

File Access Permissions: It is possible to have FileMaker database files without visible export capabilities. Inside FileMaker, custom menus can be implemented to disable export functions. To fix this type of problem, you need to log in to the database using the password of the administrator account that has privileges [Full Control]. Then you can select the menu: Tools-> Custom Menus → [FileMaker Standard FileMaker Menus] After selecting this menu, all normal menus will be available.

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

This means that to retrieve data from a FileMaker database, you must always have FileMaker software running on MacOSX or Windows to retrieve 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 restrictions for exporting data from FileMaker to any non-related file format. There may be loss of UTF8 formatted data, truncation of data in some formats, and problems with duplicate field data. That's why I recommend connecting directly to FileMaker via ODBC and transferring the data directly to MySQL (or any other database that you choose).

What are duplicate fields? A repeating field in FileMaker is similar to storing an array of data in one field of one record. I usually recommend splitting this data into related records associated with the primary key of the parent record. The perl scripts linked above perform this task. But you have to prepare the data in FileMaker in advance. Since FileMaker no longer supports repeating fields through their ODBC driver, you need to create a script in FileMaker to transfer all the repeat values ​​to the first repeat value. Therefore, if you have a field of duplicate values, for example:

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

You move data to:

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

You can then iterate through the delimited TAB values ​​in Field1 [1] to write data to the corresponding table.