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:

First_NameLast_NameAge
BobLee23
JamesFord57
NancySilva34
MariaGreen28
JackMay45

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

Table with data

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:

External Data

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

New Data Source

Choose the Excel option:

How to Import an Excel File into Access

Click on the Browse… button:

Get External Data

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

How to Import an Excel File into Access

Press OK:

Get External Data

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

First Row Contains Column Headings

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:

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:

How to Import an Excel File into Access

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:

Import an Excel File into Access

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:

Import to Table

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.

Save Import steps

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:

All Access Objects

You’ll then see the imported data:

How to Import an Excel File into Access