10 Replies Latest reply on Jul 20, 2017 7:30 AM by beverly

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


      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
      NO EQUIVALENT FIELDproject_type_other
      NO EQUIVALENT FIELDis_classified
      NO EQUIVALENT FIELDproject_status_code