Split Import to Seperate Tables

[FMD]

I have an Excel file in which a single worksheet has data that needs to go into separate tables. For example...

Table: Company

Field: pkCompanyID

Field: CompanyName

Table: Contact

Field: pkContactID

Field: fkCompanyID

Field: ContactName

So if I have an Excel Worksheet that is like this

ColumnA ColumnB

CompanyA Bob

CompanyA Dave

CompanyB Sara

CompanyB James

CompanyA Sally

How to I automate an import of that into FileMaker into the separate Tables while assigning the key field values and keeping the integrity?

[Matthew F]

Why not create a script with a separate "Import Records" step for each table?

[FMD]

mferno -

How will the relationship between the Company Table and the Contact Table be maintained?

[Matthew F]

How will the relationship between the Company Table and the Contact Table be maintained?

Sorry, but I'm not getting your question. The relationships are an integral part of your FM database, but are not part of an Excel speadsheet. Since its not built in to your Excel data I'm not sure what you need to maintain during the import.

If you have relationships setup in your FM database, (using your example, Company::pkCompanyID = Contact::fkCompanyID) these will be maintained as long as the match fields satisfy the relationship.

Is the issue is that your FM database has relationships based on identifying data (e.g. pkCompanyID) that are not in your Excel spreadsheet? If so, the obvious solution is to make the IDs part of your spreadsheet. If that isn't practical then you'll need to figure out a way to match names and IDs within your FM solution. That can be problematic, e.g. if two people have the same name or if the same person uses two different names.

I'd suggest breaking it down to a very simple example, and if it still doesn't work for you then attach it as .zip file to your post.

:

[FMD]

mferno - that gets to the heart of my question... if I import the spreadsheet in two phases, one to the Company table, and one to the Contact, there is apparently no mechanism inherent to Filemaker that will maintain the PK to FK relationship without assigning the PK and FK values to the spreadsheet prior to import..

What about creating a table in Filemaker that matches the spreadsheet structure, plus fields for the PK and FK, importing to there, and then moving the data, row by row, to the appropriate tables? I am envisioning going row by row, checking to see if the Company exists, if yes then adding the value to a field in the import table, if no adding the Company to the Company table and then recording the new value in the field. Finally add all the contacts to the Contact table with the appropriate FK_ID to the Company table...

This processes is going to be repeated which is why I am trying to find a way to automate it, otherwise I would just massage the spreadsheet... the upshot is that the new spreadsheets should never contain anyone from an old spreadsheet.

[Matthew F]

The inability to 'divine' the unique identifier's for your imported data, when it is not provided in the import data set, is an inherent limitation to any relational database, not just Filemaker. Excel also has no idea that 'Jack Smith' on Sheet1 is the same as 'Jack Smith' on Sheet2. That identity is simply a concept in the users mind (and it may actually be wrong if two people have the same name).

Perhaps you're considering the names in the Excel file to be unique identifiers and are wondering why FM doesn't get it. Naturally, you could create a quick and dirty database using the names as the matchfields for your relationships. However, this would be considered poor form because of the potential ambiguities associated with peoples names (e.g. people with same names, misspelled names, duplicates, etc.).

Creating a specific table to hold import data is good because it will also serve as an 'import log'. This will allow you to trace back what was imported (and when) and may help troubleshooting.

There are many different ways that you could match names and IDs, e.g. your looping script idea. Even though you think your future excel sheets will only have unique names, it is inevitable that errors will occur as your database grows larger. The more error checking you can build in the better, along with the ability for the user to fix mistakes.