Importing Data from Spreadsheet - Efficient Design

  • Thread starter Thread starter Steve D
  • Start date Start date
S

Steve D

Good Morning,
I have a report (Excel) that I get weekly that I want to import into
Access but I know the format of the report is not efficient for a table in a
database (Duplicate Districts, Customers and Weeks) If this was a one time
issue I would import and then split the table but I will be updating this
every week. Is there a way to split and then import into the table when the
data is in the original format? Should I create an import table and then
figure a way to split the data into the existing table structure? Any help is
appreciated.

District Customer Week Items Sold
Atlanta ABC Inc. 05/11/2009 3
Atlanta DEF Co. 05/11/2009 1
Atlanta GHI Corp. 05/11/2009 2
Baltimore JKL LLC. 05/11/2009 1
Baltimore ABC Inc. 05/11/2009 1
 
I usually create a temp table and import my data to it. I then use an update
query to put just the data I want into my real table. Since you have
duplicates, such as several Atlantas, try putting an autonumber or some other
sort of key field in the update in order to identify each one.
 
Back
Top