Solved

Import excel file in to multiple filemaker pro tables

Posted on 2012-03-27
4
1,057 Views
Last Modified: 2012-03-31
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
Comment
Question by:madcat2020
  • 2
  • 2
4 Comments
 
LVL 25

Accepted Solution

by:
Will Loving earned 500 total points
ID: 37773360
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
 

Author Comment

by:madcat2020
ID: 37773840
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
 
LVL 25

Assisted Solution

by:Will Loving
Will Loving earned 500 total points
ID: 37774220
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
 

Author Closing Comment

by:madcat2020
ID: 37792470
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question