How to import data from FileMaker Pro to PostgreSQL

Question

FileMaker Pro is similar to Microsoft Access. I want to take this FileMaker data and import it to a PostgreSQL database.

Any suggestions? Should I write a program to do this? Or should I put the FileMaker data into Access and import the data from there?

Thank you for the input!!

Answer 1

Yes, you can do this. You can use Access as a middle layer in this process, but there's no reason to do so other than personal preference.

First, I'd advise you to spend some time analyzing your FileMaker database. Are all data tables designed with standard relational principles? (FileMaker supports, but does not enforce relational design). Are the fields types logically defined, and primary/secondary keys consistent and well-structured? If not, I'd strongly advise you to address these issues (either in FileMaker or Access if you prefer) before proceeding with your migration.

Second, I'd look to see if there are any idiosyncratic FileMaker data structures that won't transfer cleanly. Here are main ones to watch out for:

• Calculated fields, which play a heavy role (especially in earlier FileMaker solutions) won't translate directly to SQL and should probably simply be dropped and recreated with application logic later on.
• Repeating fields, a legacy left over from FileMaker's pre-relational days, will need to be translated to separate tabular structures;
• Container fields, which are similar to SQL BLOBs. The challenge here will be finding a clean way to get container data into SQL, since CSV and similar formats won't accept blobs unless you convert them to Base64 (FileMaker can do this for you if needed);
• Data constraints; these will just get lost in a transfer, so it's probably best to just note any you care about and recreate these manually in our Postgres DDL.

Once you've done this prep, you can easily export data from FileMaker into a standard format like CSV, which can in turn be imported directly into new Postgres tables. Key things to watch for here:

• For each FileMaker table you'll export, be sure to Select All Records before exporting, to ensure that all records are included (otherwise, FileMaker will only export the records currently found for a current table).
• You'll need to export data individually for each table you want to move over to Postgres.

Last, of course, define the new tables in Postgres and import your CSV data.

Answer 2

I wouldn't go through Access unless there's some compelling, unstated, reason for doing so.

If this is just a one-time routine, exporting to some common format, such as CSV, would probably be easiest.

If it's something you're going to have to repeat and keep live, I'd look into an ODBC connection into FileMaker and write the routine in your application logic for the PostgreSQL system.

Answer 3

As everybody stated, transferring the data to Access will only result on extra work which is completely unnecessary,

I suggest looking at FileMaker ODBC to extract data, or using Postgres ODBC driver and insert data from FileMaker with "Execute SQL" script step into PostgreSQL tables.

Answer 4

One problem which can occur with transferring the data into Access is that Access is limited to a maximum of 255 fields per table. FileMaker databases which customers send to me for conversion often have hundreds of fields.

When I perform these conversions with FmPro Migrator, I always make a direct connection between the databases - in order to preserve UTF8 encoding within the FileMaker data.

Another consideration, is whether you also want to convert the GUI and business logic (scripting) from FileMaker for use with your PostgreSQL database. Objects within FileMaker can be extracted as XML, making it possible to transform these objects into something equivalent and useful within another application. One popular choice for a GUI today is a PHP web application.

So for instance, elements from the FileMaker layouts (fields, images, text labels, vector graphics objects) can be converted into HTML, CSS and JavaScript within a PHP web application.

FileMaker value lists (used for drop down menus, radio button and checkbox groups) can be defined as an array in PHP. This can be either a static array of values or a dynamic array retrieved at runtime - to emulate the original functionality.

For performance reasons, it can be very helpful to convert FileMaker Unstored Calculation and Summary fields into SQL Views. This way the calculations are performed in the database server instead of via PHP in the web application.