Best approach compairing a lot of different pricefiles coming from different file types

[FoolzRailer]

Hello



I'm looking at 10-12 different price catalogues/files, the pricefiles come in different layouts (columns/fieldnames) and different filetypes: csv, txt and xml. I need to compare the files with each other based on a unique modelname found in each of them, and thereby locating the cheapest among other things.
My problem is this, the files are looking to be updated, perhaps every week or so, but will always remain in the same location (so new files overwrite old ones with the same name). In order to start this process this the best, I'm looking for how I should approach this.

Will it make sense to link the data instead of adding it as a table? Will that work if a new file is overwriting the old one? I'm just thinking of doing some VBA along with Queries in order to find the data I want, but if there is something better suggested I'm all ears.

The files vary in size around 10-50 mb each, so there are a lot of rows of data in them.

Any help would be much appreciated.

[CJ_London]

Will it make sense to link the data instead of adding it as a table?

it depends on the requirement - if the latest file 'is it' i.e. you are not interested in dropped items, then linking should be fine

Will that work if a new file is overwriting the old one?

providing field names don't change then should be OK - you may need to refresh the link (can be done in code)

However they won't have indexing so if the files are large, performance may be slow - so perhaps better to import the data. You'll have to try it and see.

If importing and data is regularly discarded then you may suffer from bloat, so perhaps better to store the data in a temporary db that can discarded/archived as required and replaced with a new one (keep a template of the tables to create the new one)

otherwise link to the files and run queries to import new items, update old ones and delete (or mark deleted) those that no longer exist.

[FoolzRailer]

Appreciate the fast response

it depends on the requirement - if the latest file 'is it' i.e. you are not interested in dropped items, then linking should be fine
providing field names don't change then should be OK - you may need to refresh the link (can be done in code)

Dropped items doesn't matter, if they aren't in them, they don't need to shown in anyway. Field names shouldn't change.

I think Bloat might be a real issue, so I'll have a go with linking the data and see how it goes.