Import/Export Data from Excel 2010 into Access 2010

Importing data from Excel into an Access database is a simple and helpful task.

I ran an 8k a week or two ago, and was sent an e-mail with all the contestents name, city, age, sex, shirt size, etc. Later on results were posted. I will be using this data to do some Access/Sql processes.

1) Open a new or saved database in Microsoft Access.
2) Click on the External Data tab
3) Click on the Excel button within the Import & Link Group (see image). Get External Data window opens.

4) Choose the location of the Excel data file to import

5) Two options appear: Import Source data or Link to Data source. Choose option to Import source data. (See Image). Click Ok

6)  The Import Spreadsheet Wizard should open and will show all/any worksheets in the Excel spreadsheet. Select the worksheet you want to open by selecting it, and clicking Next. (See image) If the first row of data contains data headings, click First Row Contains Column Headings, then Next.

7) The next screen allows you to format columns of data that will be added into an Access table. This is a good time to index column data, which will turn into Access fields -- if necessary. An index is like a map for Access. If you choose to index a column of data, Access has the ability to jump right to the specific data for a query, report, etc. This is a great way to speed up reports which are generated from a query or qry def. Usually I do nothing at this screen though, and just push Next.

8) At the next screen in the wizard, you have an option to add a primary key. This simply numbers each row of data. I usually selection the option No Primary Key, and  use just the data that is being imported.

9) The last screen of the Import Spreadsheet Wizard allows you to name table. After entering text or if you are ok with the name Access has given your table, push Finish.

10) A screen will pop-up asking if you want to Save Import Steps. If this is an Excel worksheet you will import frequently go ahead and save, otherwise click Close.

11) Look in the Navigation Pane and you will see the newly imported table. Double click on the table name and your data appears!