How to automatically import Excel data into an Access database?


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.

2016-01-12 23_46_53-Cortana

  • In the file name, browse to the excel file which has to be imported into the table.

2016-01-12 23_47_29-Get External Data - Excel Spreadsheet

  • 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.

2016-01-12 23_48_10-Get External Data - Excel Spreadsheet

  • If your Excel spreadsheet has column headings mark the First Row Contains.. check box; then hit Next.

2016-01-12 23_48_57-Import Spreadsheet Wizard


  • Then select each column to modify the field name and data type if needed.
  • Hit Next.

2016-01-12 23_49_36-Import Spreadsheet Wizard

  • 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.

2016-01-12 23_50_05-Import Spreadsheet Wizard

  • 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.

2016-01-12 23_50_37-Import Spreadsheet Wizard

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.

2016-01-12 23_52_52-Get External Data - Excel Spreadsheet

  • 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.

2016-01-12 23_54_55-Import Spreadsheet Wizard

  • Hit Finish.

 

Enjoy your data analysis :-)

Leave a Comment: