Importing related table data from Excel

[Wickerman]

Hi -- I'm very inexperienced in importing data except in very simple straight field-to-field situations.

I have a database with a multi-level hierarchy of one-to-many related tables, kind of like russian dolls:

Collections < Titles < Copies < Volumes

Unfortunately, I'm in a situation where I need to allow a couple of people to create data in Excel and then import it to my FMP database. Seems crazy to me, but necessary. So I've created a monster spreadsheet with columns representing the various fields in all the tables -- actually I did this by exporting existing data from the Collection level and including all the lower-level fields as well.

I'm left wondering, though, how I achieve the import, including the creation of all those key-fields. Would there by any chance be a resource somewhere that provides a how-to, or a demo file for this kind of thing?

thanks for any pointers!

[The Missing Man]

From what I know, simply do the following.

Create your excel data base with columns for people to enter the data in. This needs to be in a consistent fashion. Fields always located in the same spot each time.

Place this excel document on your computer into the same folder each time, replacing the old excel document.

From Filemaker create a button linked to the action import data.

When you import your data cross reference the data to the corresponding field within your Filemaker data base and your done.

[tanbog]

I have a very similar inquiry as the first poster.

I do not completely follow what needs to be done from The Missing Man's reply.

When I try to import related data from a spreadsheet I get all kinds of crazyness. I cannot find a way to "cross reference" the incoming spreadsheet.

I can't seem to find a clear explanation or guide to the process either. Any advice or pointer in the right direction would be greatly appreciated.

[bcooney]

The fact is that there is no easy way to do this. The best way to do this is to not involve Excel, and if you do use Excel, you must try to preserve the correct FM key in each row.

@Wickerman - what data are they creating/editing in Excel? If they are editing existing data, perhaps you can import with a match on the key and update the record in FM. Maybe you should explore creating a runtime app for them or IWP.

[Wickerman]

Sorry to be away from the thread so long, in case anyone;s still listening!

It's a case where people would be adding records, not editing existing ones. I understand how to import an excel spreadsheet into a single Filemaker table with corresponding fields -- that's no problem.

THe challenge is in moving from a "Flat" (single-table) spreadsheet into a "Relational" multi-table set of tables. I'm figuring I have to ensure that there are fields in the spreadsheet that indicate, through identical values, that certain rows "belong" to the same Collection, Title, etc. -- and then I'm going to have to write a looping script that moves from the top down, creating a Collection record and then generating matching key field values for its child records, and the same for the child of the childs . . . .

I feel I have a conceptual sense of what needs to happen, but I'm feeling a little daunted by the task -- wondering if there might be a useful example of such a script out there, or a sample file?

[LaRetta]

I suggest that you use an intermediate Importer table. From there, you can error check and validate before moving the data on to its tables. You would import the main data into Customers (your main table for example) thus providing Importer with the new CustomerIDs through that relationship then you import child data into the child including the CustomerID.

The purpose of the Importer table is not only to take flat file and turn it relational but also to 1) prohibit importing same Excel file more than once, 2) validate that the data meets your requirements internally (field-level validations in Customers, for example). In this way, your Users are not interfacing directly with your normalized structure - far too risky.

As Barbara mentions, Excel is not the best choice because of its data-interpretations but I understand why it is needed (if you absolutely cannot provide FM to your Users). You could ask Users to export to csv - that's the best choice. But regardless, if they enter data in Excel, they can add blank rows, two-row headers and other things to mess it up. Only with Importer table do you stand a chance of proper interfacing.

If you wish for example, I can provide one but I would prefer to have something to work with so I can get 'close' to what you will need (some Excel data or fields/table names to work with)? Whether it is migration data or external source data, it should be highly controlled.

I actually LOVE these types of things.

ps - am I blind or is there no spell check any more? It used to check words automatically and no longer does? Or am I getting forums mixed up?

[Lee Smith]

ps - am I blind or is there no spell check any more? It used to check words automatically and no longer does?

I don't remember this being the case, I have always did my spell checking after-the-fact by using the key combination (Shift + command + ; ). However, Safari does allows you to turn on and off Check Spellings While Typing (Edit Menu « Spelling and Grammar « Check Spellings While Typing)

Or am I getting forums mixed up?

Don't know, I don't remember how the cafe they worked.

Lee

[Wickerman]

Thanks again LaRetta -- I may take you up on this offer when I'm forced to grapple with this for real . . .. the situation is currently on hold.