AnsweredAssumed Answered

How to Export FileMaker Records to CSV that Matches Destination Schema in PostgreSQL?

Question asked by mjstrwy on Jul 18, 2017
Latest reply on Jul 20, 2017 by fmpdude

I am an archaeologist building a FileMaker database for mobile data collection (via FileMaker Go) and am having some trouble exporting data in the correct format for our backend database, which is in PostgreSQL. The backend database has equivalents for some, but not all of the FileMaker fields, as well as additional fields which are only in the PostgreSQL database. I need to export data from FileMaker into a CSV file which can then be imported into PostgreSQL.

 

My question is: how do I best format my CSV output so that it is clear which destination fields in PostgreSQL each FileMaker field should import into? Should I provide a diagram (or schema crosswalk) showing how they map and then simply provide a CSV with only the fields which have a destination field in PostgreSQL, along with blank fields so that the CSV file matches the field order in the destination table?

 

Related question: How do I include blank fields in the CSV where no source fields exist? FileMaker's "Export records" script step only lets me adjust the ordering of the source fields, not add empty placeholder fields to match the ordering and fields present in PostgreSQL destination tables. I've also tried building layouts which only display the equivalent fields to help automate the export, but have run into issues there with not being able to add blank fields without affecting the structure of the source table.

 

Example Mapping:

FileMaker Source Table (Survey Project)Maps ToPostgreSQL Destination Table (project)
UUID  project_uuid
Project Numberproject_num
Project Nameproject_name
Field Personnel project_mgr
Fieldwork Startproject_start
Fieldwork End project_end
Transect WidthNO EQUIVALENT FIELD
Ground VisibilityNO EQUIVALENT FIELD
EnvironmentNO EQUIVALENT FIELD
VegetationNO EQUIVALENT FIELD
Notesproject_desc
NO EQUIVALENT FIELDproject_type_other
NO EQUIVALENT FIELDis_classified
NO EQUIVALENT FIELDrec_no
NO EQUIVALENT FIELDproject_status_code

Outcomes