Solved

Import excel file in to multiple filemaker pro tables

Posted on 2012-03-27
4
1,049 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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

773 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