Updated: August 2019
Applicable to: Office 2019/2016/365
Here’s a question from Mike:
“I know how to create Access forms and insert user input into a database table, but is there a way to automatically import data from an Excel workbook into an Access database table instead”?
Good question, Thanks. Yes, you can automatically import data from an Excel worksheet into new and existing Access tables so that later you can run queries and data analyses against that data set. Read on for the step by step data acquisition instructions from Excel.
Importing Excel data into a new Access table
- Open Access 2019, click on the external data in the ribbon and select Excel in the import & link command.
- In the file name, browse to the excel file which has to be imported into the table.
- There are three ways in to specify how and where we need to store the data in the current database.
- If we want to create a table and insert the imported Excel data into it, check on import the source data into a new table in the current database and click OK.
- If your Excel spreadsheet has column headings mark the First Row Contains.. check box; then hit Next.
- Then select each column to modify the field name and data type if needed.
- Hit Next.
- Now select if we need primary key option in your table.
- Check on Let Access add primary key to make access to add a column, use it as primary key or select choose my own primary key and add the primary key column.
- If we do not need primary key, check on No primary key.
- Enter the name for the table to which the data has to be imported, if we needed to analyse the table check on I would like to analyze my table after importing the data and click Finish.
Adding your data into an existing table
- If we need to insert the excel into the database which was created by us before, check on Append a copy of the records into the table.
- Hit Next.
- Remember, the column names of the table has to be same as the column names in the Excel sheet that is going to be imported.
Enjoy your data analysis