Help with rearranging imported data

[picnichouse]

This is kind of a tough one to explain...

I want to export sales data from my company website and then use filemaker to arrange the data for import into my accounting software. The accounting software has fields that look like this

Customer Name

Order Number

Purchase Date

Product Name

Product Price

Product Quantity

And if the customer orders more than one product, multiple records are assigned the same customer name, order number and purchase date.

The exported file from the website is kind of like two databases in one... like this

Name Order Number Purchase Date

John 67832 05/23/04

Product Price Quantity

Apples $1 3 dozen

Orange $5 2 dozen

Pear $3 3 dozen

So the data is stacked on top of itself, repeating header rows each time you reach a new customer or new products. I've attached a file to make this more clear.

I want to add redundant customer information to each product record so it will be compatible with my accounting software. How can I do this?

Please check out the excel file for more clarity.

Thanks in advance!

Andrew

example.xls.zip

[dkemme]

Take a look at what FMP does when you choose open file and select this spread sheet. You get some of your data correct, but the rest is not. You need to create a few extra data fields for storage. Then create a script that will loop through each record, moving the overlapping data to the new fields. Then clean up the data with the same looping, but pasting the correct name, order, date into the repeating records. Not too hard after a few trial runs.