Need help importing from Excel document

[djeans]

I have data in an excel document that was exported from a different database that I would like to import into FM 8.5. The problem is, that the data in excel is not contained in one row or column, but rather, each record is spread out over several rows and columns. I'm having a hard time getting the data to import properly.

I can't figure out how to attach an example of the Excel file I'm talking about, but you can download it here http://files.me.com/djeans/9x426t

That is only a small example, the whole document is 28 pages long.

Any suggestions?

Thanks

D.

Edited April 27, 2009 by Guest

[comment]

You could import this into a temp file, find and delete rows that do not carry actual data, then script creating one record (in another table) for each pair or imported rows.

[djeans]

Not sure I completely understand what you mean. Can you dumb it down a little for me? (I'm somewhat of a Newbie.)

Thanks.

D

[comment]

Which specific part is not clear?

[djeans]

the import into a temp file and deleting rows is a little confusing.

d

[comment]

Just open the Excel file in Filemaker - this will create a new file. Then find records that are empty or serve as headers only and delete them. I think this will become clear once you see the imported data.

NOTE:

I am assuming this is a one-time conversion, not something you need to do regularly. Otherwise you'd want to script the find-and-delete part too. But I wouldn't presume to advise on that on the basis of sample data.

[djeans]

Ok,

I'll give it a try. It is something that I will need to do fairly regularly, so I'll probably try to figure out how to script it eventually.

Thanks

[djeans]

Ok,

I opened the file in Filemaker, and see what you mean about deleting empty rows. What's not clear is how to automate combining data. For instance, if I delete all the garbage rows until the first row with data, the barcode number and description are in row one, but all of the other information that i need is in row (record)2. When I import this into my main database, I'll want all of that data from those two records/rows importing into one record in the main database.

D

[comment]

Once you have only valid data rows, you can run a script that does roughly:

Go to Record [First ]

Loop

Set Variable [ $barcode ; ]

Set Variable [ $description ; ]

Go to Record [Next ]

Set Variable [ $whatever ; ]

...

Go to Layout [ another table ]

New Record

Set Field [ Barcode ; $barcode ]

Set Field [ Description ; $description ]

...

Go to Layout [ original layout ]

Go to Record [Next, Exit after last ]

End Loop

[djeans]

Thanks!!

I'm going to give that a try right now.

d

[djeans]

Worked like a charm!!

Thanks.

D