• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1199
  • Last Modified:

Import excel file in to multiple filemaker pro tables

I need to import excel files in to a Filemaker 10 database.

How do I go about importing the excel file so the data relates to the proper tables?

the database has numerous tables for managing an art collection.
A table for the artwork, another for artist info, another for history, another for images and so on...

I am okay doing multiple imports linking the fields in the excel file imported to the appropriate tables, but what is the best way to do this without extensive scripting and to ensure that the data from the excel file will relate properly to the existing data?

Would it be best to create a temporary database and then merge the two together?
0
madcat2020
Asked:
madcat2020
  • 2
  • 2
2 Solutions
 
Will LovingPresidentCommented:
As long as the first row of your spreadsheet contains the field names exactly matching the fields you wish to import them into in FileMaker, you can use the Arrange By: "Matching Name" import option. When importing FileMaker to FileMaker the "Matching Names" option is always available, but when importing from Excel or other sources, you must first tick the checkbox that says: "Don't Import first record (contains field names)"

Import matching on first row
You can script some or all of the multiple imports most easily by being consistent about naming conventions, both the fields but also the spreadsheets. If you consistently name the spreadsheets the same name and put them in the same location - for example a folder name "File to Import", then you can setup a script with multiple import into different tables.

You can actually go further if you want and not just import but also "Update" existing records with new data as long as you have at least one field to match on between the FM Database and the spreadsheet. If you use the "Update" option, keep in mind that the "Matching Names" option no longer is available because you have to specify one specific field to match on. As long as you don't delete fields in FM or Columns in your spreadsheet you'll be fine with the "Last Order" Arrange option.
0
 
madcat2020Author Commented:
Thank your for the quick response.
This makes sense so far.
But it is only solving part of my issue.

When importing, I can send data from my Excel file in to different tables.
The problem is nothing is related.

So in my example, I import data in to the artwork table, creating/updating records and then import the data about each artist in to the artist table creating/updating records, repeated for each table for the financial info and so forth. But nothing seems to be connected or related. So the database cannot tell what artwork belongs to which artists or what financial info goes to which artwork and so on.


In the existing database structure there are auto generated key fields and serial#'s that relate everything and all the records seems to be relating to each other fine. I can modify or add new records through filemaker no problems, except when importing from external sources.
These key fields in general do not exist in the Excel data because it was created outside the system.

I guess my more specific question is how do I get the imported data to relate to the existing tables? Do I need to generate or add key fields in to the Excel document or do i need to add, modify existing key fields in the database?

Thank you for your help.
0
 
Will LovingPresidentCommented:
You last statement is exactly correct. In order to Update data in your existing records via Import, your Excel documents must have a key field - usually a unique serial number - for each record in FM that you are trying to update. So for the artworks, one of the columns must be the actual serial number of the artwork record to be updated and similarly for artists, it must include the serial number or unique key field for each artist.

When you perform the import, you select "Update Matching records in found set" and then set at least one key matching field in the field matching dialog by clicking on the operator between them so that you have a double-headed arrow pointing between the two fields, the serial number or key field in FM and the matching column in Excel.

How you get the key fields into your Excel document depends on how the changes in Excel are being created and edited. Sometimes people will do an Excel export from FM of specific records and include the unique identifier. They then edit the spreadsheet and re-import.

By the way, if you doing University level image management, I've actually worked on and helped revise a system called Snapdragon for doing just that.
0
 
madcat2020Author Commented:
This solved most of my issues I was having I still need to tweak some of my tables to work correctly but the advice gave me enough to started. Thanks!<br /><br />I wish Filemaker itself had an easier process for importing into multiple tables from an external source. To me this seems like it would be a common thing people do. Its way to complicated and too many steps no matter how i do it now.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now