Applicable to: Office 2019/2016/365
Here’s a question from Mike:
“I know how to create Access forms and capture user input into a database table, but as i have already gathered 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.
Create Access table from Excel
Important Note: Before attempting to import the Excel file content ensure that the file you are about to import contains data in tabular form with proper and descriptive headings. Also, might be a good idea to ensure that your spreadsheet doesn’t contain any duplicated rows.
- Open Microsoft Access.
- When prompted, either select an existing database or create a blank database.
- In Access 2016, go ahead and hit the External Data tab in the ribbon.
- Next, from the import & link Group, hit the Excel button.
- Alternatively, if you are using Access 2019, hit New Data Source>>From File and select Excel
- Next, go ahead and hit Browse to select the Excel file that will be imported into the table.
- Browse to your computer or shared location to find your Excel spreadsheet.
- Once done, hit Open.
- Next, you should specify how you would like to store the Excel data in your Access database.
- In this case, we want to go ahead and create a table and then insert the imported Excel data into it. Therefore, we’ll select import the source data into a new table in the current database and click OK.
- If your Excel spreadsheet has column headings, make sure to 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.
- Your last step will be to define a meaningful name for the newly created table,.
- As an optional step, you might want to run a quick analysis to optimize your table. The analysis tool allows you to identify duplicates, compact the table for better performance etc’. If so, then go ahead and check I would like to analyze my table after importing the data and click Finish.
Note: So far, we learnt how to bring on data from Excel to Access. You might be as well interested in exporting your data from Access to Excel.
Import Excel to Access existing tables
Sometime your might need to append data that is stored in an Excel (either as a XLS or CSV file format) into an existing Access database table.
- If we need to insert the Excel spreadsheet data 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.
- Hit Finish.