Office 2010 - Access

Lesson 71: Importing Data

71/76 Lessons 

From an Excel file

In “Access”, we have the ability to import “Excel” files. To do this, select the “External Data” tab in the “Ribbon” and under the “Import &Link” group, click the “Excel” button.

les71_image001_en (1)

In the dialog that appears, click the “Browse” button and select the ‘Excel’ file that you want to import This opens a second “Open File” dialog.
Navigate to the file, select it and click the “Open” button.

The dialog box options are self explanatory, so click the OK button.

This opens the “Worksheet Wizard”. When the spreadsheet consists of several worksheets, or there are named “Ranges” in the spreadsheet, we see the following window. Select the worksheet or named “Range” that you want to import and click the “Next” button.

In the second window of the wizard, check the box for the “First row contains column headers” text if your worksheet contains column headings. “Access” will then use these as field names. The column headings should be in the first row of the worksheet.

Click the “Next” button.

In the third window of the wizard, we can type the datatype for each field. We have the option to determine whether the field we are importing should have an index or not.

To make changes to a field, click the field title in the lower part of the wizard window. If you have finished making changes to the fields, click the “Next” button.

In the next window of the wizard, you decide whether you want to define a primary key field or not. You can let “Access” add a primary key, or select a field that you want to set as the”Primary Key”. Because this worksheet does not have a unique field, I let Access add a primary key to a field . “Access” will automatically give a field with the field name as “Id” with the “AutoNumber” as data type.

Click the “Next” button.

In the last window of the wizard, you give the table a name, and click the “Finish” button.
In the window that appears, you can save the import steps if you wish. Make your choice and click the “Close” button.

Your worksheet is now included in the “Navigation pane” of the “Access” database.


You've completed Lesson 71