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, 200818 yr by Guest

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

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, 200818 yr by Guest

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.

Thanks guys,

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

This solution should cover it.

Matt

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.