Import Excel table into Access - how it works
You can easily import Excel tables into the MS database program Access. This gives you the opportunity to edit your data outside of the actual database or to integrate data from external sources into the database.
Integrate Excel tables in Access
Prepare your Excel spreadsheet before starting the import process. To do this, remove empty rows, separate connected cells and give each column a heading. Also keep in mind that only one Excel worksheet can be imported at a time. If the Excel table has been edited in this way, a program assistant will help you to import the data from Excel into an Access database.
- Go to the "External data" tab and open the import wizard by double-clicking on "Excel". The path to the source file is entered under "File name" - in this case that of the Excel table. "Browse" takes you to the location of the table on your computer to select it.
- The wizard offers you three options for importing the table. You can import the table into the database as a stand-alone table, attach the records to an existing table, or create a linked table. If you are working with an empty Access database, you will only be offered options 1 and 3.
- A linked table is useful if you want to import regularly updated data into your Access database using an external Excel table. For example, you can use data from external employees without opening the database. However, the data can then no longer be edited in Access
- If you have selected a possibility, confirm with "Ok". The import wizard then guides you step by step through the import process. So you can select the worksheet from your Excel file to be imported. Alternatively, make corrections if field names in the column heading are incompatible.
- In the last step, you define the name of the new Access table and complete the import process by clicking on "Finish".
- It is possible to save these import steps as a routine. This is useful if you want to import several tables with the same structure from the same source.
This practical tip is based on Access / Excel 2013. The menu bar of Access is often perceived by program beginners as too confusing. If you feel the same way, we will show you another practical tip on how to tidy up the Access user interface.