Import Excel file and normalize it every week (1 Viewer)

Freshmeadow

New member
Local time
Yesterday, 19:14
Joined
Aug 7, 2009
Messages
5
Greetings from Guelph. First, my apologies if this has been covered already. I have searched through the forum and the Using Microsoft Access 2002 and 2003 books by QUE publishing but have not found an answer.
Each week I import an Excel file into Access 2002. Each week the file will have approx. 5,000 rows. The same fields will always be in the same order with consistent data formats, the same field names, field lengths etc.
Each week I want to normalize the imported table in Access to reduce space and make the database more efficient.
My question is, can I append the latest week's normalized table to that of the previous week's so that I end up with one master normaized table to run queries and reports against, rather than having a separate table for each week?
Any suggestions would be appreciated.
I have worked with and studied Access in books and online courses since 2002 but this is my first crack at setting up a database from scratch.
Thanks! :)
 
Yes, that is possible. I do it with several files on a daily basis. Generally I will import the file into a temp table. Since the spreadsheets will be constant as far as structure, you might want to use an Import Specification. Once the data is in the temp table, you can modify fields, change data, whatever. Once that is done, you can use an Append query to transfer the data to the master table. Use a delete query to clean out the temp table and you are ready for the next week's file :)
 
Scooterbug, what a great idea! I knew normalizing a new table and adding it to an existing one was something many people had to do regularly but I could not find an answer for it!
Thanks from rainy Guelph! :)
 

Users who are viewing this thread

Back
Top Bottom