Import from Excel

[blah]

Does anyone have a solution for importing from a formated excel file?

I have to use a form provided by my client. The Data is not in simple rows and columns, basically I just want to point to certain cells and let it pull the data out and import them into FM.

If I try to import data using FM, the data is disorganized and shows as separate records instead of 1 record with different fields.

I demoed Excel Extract data and text, a program from www.sobolsoft.com, but the program is not very refined and has NO support.

Thanks for the help

Matt

Edited January 7, 2008 by Guest

[comment]

Why don't you import to a temp table, then pick and choose the data from there.

[blah]

Each field shows as a separate record, and the actual data is incomplete when importing from FM. For example: 1 file shows as 55 records.

The form that I have to use is formatted like a word file would look, many cells are combined and there is also a lot of empty space.

I have to process a bunch of these files and the only other alternative is manual data entry.

Edited January 7, 2008 by Guest

[Colin Keefe]

Your best bet is still a temp table and some scripted data mining, as Comment says. If the excel doc isn't following a strict row/column format, it'll never import cleanly in one go.

Import to a temp table, determine rules for what field/records in the temp table should be harvested into a single record (or record set), and formalize those rules in scripts.

If the excel form isn't predictably strange, you're going to have some difficulties.

[blah]

Thanks guys,

I rechecked the data and you were right, it does follow a pattern.

This solution should cover it.

Matt

[comment]

It doesn't HAVE to be all scripted (although it can). If you can make the 55 imported records related to the single form record*, you can get the data by calculation - for example:

GetNthRecord ( TempTable::f3 ; 15 )

will fetch the data from what was the C15 cell in Excel.

---

(*) If you create a new form record first, set its ID into a global field or a variable, and define a FormID field in the temp table to auto-enter this ID, the imported records will be automatically related to the new form record. This part can (and should) be entirely scripted.