A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Importing data from excel spreadsheet



 
 
Thread Tools Display Modes
  #1  
Old January 11th, 2010, 09:43 PM posted to microsoft.public.access.tablesdbdesign
Emelda
external usenet poster
 
Posts: 2
Default Importing data from excel spreadsheet

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.
  #2  
Old January 11th, 2010, 10: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]
  #3  
Old January 11th, 2010, 10:25 PM posted to microsoft.public.access.tablesdbdesign
Golfinray
external usenet poster
 
Posts: 1,597
Default Importing data from excel spreadsheet

I do, and I think it is normal to, bring the Excel data in with the
docmd.transferspreadsheet command putting that data in a temp table, then do
an update query to add the data you want to your existing tables. See this
website:http://www.accessmvp.com/KDSnell/EXCEL_Import.htm
--
Milton Purdy
ACCESS
State of Arkansas


"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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:35 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.