Dealing with importing disparate csv files

[Steveoc]

I am creating a database that will be used to handle orders by importing csv files from various Internet sources of orders. Unfortunately, the files are very different in the way data is arrayed and presented. For example, product information is stored 2 columns in one and in three in the other. One has the full name in one column, while the other separates first and last name. One has perhaps 15 fields, whereas the other has about 30.

I have been playing with setting up a separate table for importing each data source so that I have fields that are easily matched with the source file. I was thinking about setting up calculation fields within the import tables to cleanup the data so that it is at least more similar. Does this approach make sense?

Also, I need a way to combine the records from the two tables so that I can process the orders from one layout that gives me the information that I need to process these orders. Can I combine the cleaned up data on another table, or would I be better off writing find scripts that look at the records from both import tables?

I have come a long way in working my way through FM7 and this DB in the week or so that I have been working through this (having been away from FM since version 3), but this has me stumped. Any help will be greatly appreciated!

Steve

[comment]

See if this thread might help some.

[Steveoc]

While this thread does not directly address my main issues, it certainly has opened my eyes to many possiblities in approaching the issues I am facing. I definitely want to include some of the functionality that you offered in the file you uploaded in the thread. Quite elegant--I have enjoyed looking at the underpinnings!

My issues differ in that, for the time being, there are two very disparate fixed formats that I will be importing on a daily basis, so I think dedicated import fields with an intermediate field is the way to go.

Thanks!

Steve