View Single Post
  #2  
Old January 11th, 2010, 09:10 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Importing data from excel spreadsheet

On Mon, 11 Jan 2010 12:43:02 -0800, Emelda
wrote:

These questions are regarding general situations for consolidating data into
an Access database. Some data is contained in a database that has been
created (or is in the process of being created) and other data was previously
recorded in an Excel spreadsheet. I am trying to consolidate by importing the
data from the Excel spreadsheet into the database.

First of all, I should be able to import it into the current Access
table(s), correct? Since Access data is normalized into different tables and
data in Excel are not, would I import appropriate columns into each
individual Access table? Would I have to make the Excel spreadsheet have the
same columns and be in the same order as the table(s) in Access? Or can I
just import it as is on Excel and Access can figure it out? Also, the Excel
spreadsheet does not contain all the information that was captured on the
later version of Access.

Secondly, are there extra steps/precautions to take when importing data from
an Excel spreadsheet into an Access database designed with autonumbers? The
Excel spreadsheet does not contain an autonumber. However, an autonumber will
be required when this information is imported into Access. Do I have Access
select the primary key in the table so that it can recognize this imported
data or would I have to run a query afterwards to assign an autonumber?

Any help would be greatly appreciated.


A direct import will not really work as you wish - it will overwrite whatever
is in your table.

Instead, use File... Get External Data... Link to link to the spreadsheet. You
can then create (multiple) Append and Update queries based on the spreadsheet
data to add or amend data stored in your normalized tables. You should
certainly back up the database before running these!!

You can run an Append query appending data into a table containing an
autonumber; just don't append anything to the autonumber field and it will
increment the autonumber automatically (there will probably be gaps in the
numbering sequence but that should be immaterial).
--

John W. Vinson [MVP]