To import Excel data into Microsoft Access, use External Data › New Data Source › From File › Excel. A wizard turns your worksheet into an Access table, lets you set data types and skip columns you don’t need.
Video
Before you start
- Put the data in one block on one sheet, with one header row and no blank rows.
- Close the Excel file before importing.
- Make sure each column holds one kind of data: dates in a date column, numbers in a number column.
In the video, the file holds 90+ training participants: Delegate ID, Name, Age, Gender, Profession, Qualification, Organization, City, State and Email ID.
Step 1: Start the import
- Create or open a database (Blank database › Create).
- Go to External Data › New Data Source › From File › Excel.
- Click Browse, select the Excel file and click Open.
- Choose Import the source data into a new table in the current database and click OK.
Step 2: Headings and data types
- Tick First Row Contains Column Headings. The first row becomes the field names.
- Click Next. Click each column to check its name and data type. Access guesses: Delegate ID as Short Text, Age as Double. Change any type from the drop-down.
- To leave a column out, select it and tick Do not import field (Skip). In the video, the Organization column is skipped.
Step 3: Primary key and table name
- Choose a primary key option. Access offers to add an ID column; choose No primary key if you don’t need one, or Choose my own primary key if a column like Delegate ID is unique.
- Type a table name, for example Attendees, and click Finish, then Close.
Open the new table: every row from the worksheet is there, without the skipped column. Click a column and use Home › Ascending to sort, for example by Delegate ID.
Import, append or link?
| Option | What it does | Use when |
|---|---|---|
| Import into a new table | Copies the data into Access once | Moving data from Excel to Access for good |
| Append a copy to a table | Adds the rows to an existing table | Adding this month’s rows to last month’s table |
| Link to the data source | Reads the Excel file live, without copying | The data keeps changing in Excel and you only need to read it |
FAQ
Why are some values blank after importing?
The column had mixed data, such as text in a number column. Fix the data in Excel or import that column as Short Text.
Can I import several sheets at once?
No. Import one sheet or named range at a time, then append them into one table.
Can I save the import steps?
Yes. On the last screen, tick Save import steps and run it again from External Data › Saved Imports.