How to Import an Excel File into Access

In this guide, I’ll show you how to import an Excel file into Access.

Here are the steps you may follow:

Steps to Import an Excel File into Access

Step 1: Prepare the Excel File

To start, prepare the Excel file to be imported.

For example, I stored the following data in an Excel file:

This is how the data would look like in Excel (where the Excel file name is “People“):

Step 2: Import the Excel File into Access

To import the Excel file into Access:

First, open MS Access, and then go to the External Data tab:

Click on the New Data Source icon, and then select From File:

Choose the Excel option:

Click on the Browse… button:

Locate the Excel file to be imported, and then press Open:

Press OK:

Since our first row contains column headings, make sure that the First Row Contains Column Headings is checked. Then press Next:

You’ll now see the following display, where you can change the data type for each of your columns/fields. For example, the data type for the ‘First_Name‘ field is Short Text:

Let’s say that you want to change the data type of the ‘Age‘ field into integer. To do so, select the ‘Age‘ column, and then select Integer. Once you’re done, press Next:

Note that since the other fields contain short texts, we will not change the data type for those fields. By default, those fields are already assigned with the data type of Short Text.

At times, you may get errors when importing your data into Access. One way to avoid those errors, is by selecting the data type of Short Text for each field when importing your data. Once the data is imported, you can apply different formatting.

You may now choose to ‘Let Access add primary key.’ This primary key is a unique key that represents each of your records. It can be used to link with other tables. Finally, press Next:

Type a name for the new table to be created in Access. This table will contain the imported data from Excel.

For example, type ‘People‘ for the new table name, and then press Finish:

Press Close. Optionally, you may wish to check the ‘Save import steps’ box if you intend to repeat the same steps as above when importing your data from Excel.

Your Excel file should now be imported into Access. You can view the imported data at anytime by double-clicking on the ‘People‘ table under the ‘All Access Objects‘ section:

You’ll then see the imported data: