Exchanging Data Between MySQL and FileMaker Pro

10.41.1 Problem

You want to exchange information between MySQL and FileMaker Pro.

10.41.2 Solution

Under Windows, you can make an ODBC connection from FileMaker Pro to the MySQL server. Alternatively, you can export tables into files from MySQL and import them into FileMaker Pro, or vice versa. But watch out for conversion issues like incompatible date column types.

10.41.3 Discussion

If you can connect from FileMaker Pro to your MySQL server over an ODBC connection, you can access MySQL tables that way. The procedure is similar to that for connecting to MySQL from Access. (See Recipe 10.39.)

Another option is to export data from one program into files and then import them into the other program. The transfer directory of the recipes distribution contains a mysql_to_filemaker.pl utility that exports a MySQL table to a file that you can import into FileMaker Pro. This script is designed to handle the following FileMaker Pro-specific issues:

• FileMaker Pro's default date format is MM-DD-CCYY. The script rewrites dates the contents of any columns in the MySQL table that contain dates so that they match FileMaker Pro's date format.
• FileMaker Pro has date and time column types, but not a combined date-and-time type. mysql_to_filemaker.pl exports DATETIME and TIMESTAMP columns as separate DATE and TIME values. (For example, a table column named c is exported as two columns named c_date and c_time.)
• Any internal carriage returns or linefeeds in column values are mapped to Ctrl-K, which FileMaker Pro uses to represent line separators within data values.

To process date values, mysql_to_filemaker.pl uses a technique similar to that shown earlier in Recipe 10.34 for constructing a SELECT statement that exports table data with the dates rewritten. That is, it reads the table metadata to detect date-based columns and exports them using calls to DATE_FORMAT( ) that rewrite the column values into FileMaker Pro format.

mysql_to_filemaker.pl writes output in what FileMaker Pro calls merge format, which is essentially CSV format with an initial row of column labels. Merge files are useful with FileMaker Pro for a couple of reasons:

• When creating a new table from a datafile in merge format, FileMaker Pro automatically uses the labels for the column names. This makes it easy to carry along the MySQL table column names into the FileMaker Pro database.
• When importing a merge file into an existing table, having a row of column labels makes it easier to match up datafile columns with table columns.

mysql_to_filemaker.pl requires database name and table name arguments on the command line. For example, to export the contents of the mail table to a merge file mail.mer, you'd invoke the script like this:

% mysql_to_filemaker.pl cookbook mail > mail.mer

The mail table has a column t that contains DATETIME values. If you examine mail.mer, you'll see that mysql_to_filemaker.pl exports t as two separate columns, t_date and t_time, with the order of the date parts rearranged from ISO to MM-DD-CCYY format:

t_date,t_time,srcuser,srchost,dstuser,dsthost,size
05-11-2001,10:15:08,barb,saturn,tricia,mars,58274
05-12-2001,12:48:13,tricia,mars,gene,venus,194925
05-12-2001,15:02:49,phil,mars,phil,saturn,1048
05-13-2001,13:59:18,barb,saturn,tricia,venus,271
05-14-2001,09:31:37,gene,venus,barb,mars,2291
...

mysql_to_filemaker.pl also understands the usual options for specifying connection parameters (such as --user or --host). Any options must precede the database name argument.

After you create the merge file, you can tell FileMaker Pro to open it directly (which will create a new table) or to import it into an existing database.

To go the other direction and import a FileMaker Pro database into MySQL, use the following procedure:

1. Export the database in some text format. If you want the file to include a row of column labels, export the database in merge format. You may then want to run the file through cvt_file.pl to produce tab-delimited linefeed-terminated lines. This will be useful if you need to transform the file with other utilities which assume that format.
2. If the database contains dates, it may be necessary to convert them for MySQL, depending on what format FileMaker Pro uses for exporting them. FileMaker Pro will use its default MM-DD-CCYY format if you select "don't format output" during the export procedure. If you select "display using current layout," dates will be exported using the format in which FileMaker Pro displays them. You may be able to use cvt_date.pl to rewrite the dates into ISO format.
3. If the MySQL table into which you want to import the FileMaker Pro data does not exist, create it. The guess_table.pl utility might be helpful at this point for generating a CREATE TABLE statement.
4. Import the datafile into MySQL with LOAD DATA or mysqlimport.