Thread: Method to get data from Excel into normalized tables

Hi

I have an Access database that, for my own practice, I have tried to normalize to the best of my ability. I am trying to eliminate duplicate data in tables. As such, I have a table for dates, a table for percentages, and a table for "Product Names". I have each of these tables linked to another table which will hold my pricing data. The pricing data table holds the dates, percentages, and product names via the ID number field in each of those tables.

Now I want to get data from Excel into the pricing data table daily. If I do a simple Import Data, it brings in the values from Excel rather than looking them up and returning the ID number from the associated table (for Date, percent, and product). What method should I use to get the data from excel into my table in the most efficient manner?

Thanks

Dave,
Let me suggest how I would go about doing this. I would export the tables having the ancilary data, percentages, etc to excel. I would then create a vlookup/lookup/index-match table for each of the cells in your primary table. In this manner, you have now replicated the fields in your new data base. Make sure to back up your original excel table before you begin this process. Once you have populated the new table, copy all the data and paste special back over itself, so that you have data and not formulae. Again I would create a backup just before this last step. Now you are ready to import your data into your data table in Access. If this is not clear or you need more information, post back.

Once you have this perfected, you will probably want to write some VBA to do same. You will then need to bring in your data to a temporary table and do an append to your primary table. This assures that you do not mess up your primary data before you append it by double checking it first.

An alternative to this is to forego the lookup scenario and bring the data into a temp table and massage it in a query and then append your primary table.
Alan

Those both seem like good options. I think I'll give both a try, and see which one runs faster. Thanks for your suggestion, I'll post my results when finished.