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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|