How to Create a Database From an Excel Spreadsheet

Learning Outcomes

• Create Access data table from Excel data

There are scenarios where data has been created or stored in Excel and now needs to be added into an Access database. As an example, say that the original company accountant only knew how to create Excel spreadsheets. Now, however all company accounting information needs to be in an Access database. How would you do this?

We'll walk through importing data from Excel into Access so follow these steps.

1. Open the Excel workbook and look through the data to:
   1. Clean up the data if there are errors or gaps in the data fields.
   2. Use the Data Tools group, Remove Duplicates button to eliminate any potential make duplicate information that may being exported.
   3. Check to see that columns have headings and are consistent data types (e.g. all currency, percentage, etc.) and correct if necessary.
      
2. Save and close the Excel worksheet and open Access.
3. In the External Data tab, click the New Data Source button, From File, Excel option.
   
4. The Get External Data – Excel Spreadsheet window opens. Find the excel file you wish to import into Access using the Browse button.
5. Next is deciding where to store the imported data. The three options are:
   1. A new table in current database,
   2. Appending a copy of the records in an existing table, or
   3. Linking to the data source by creating a linked table.
   4. For this example choose to create a new table.
      
6. The Import Spreadsheet Wizard is now opened, and the Excel table is displayed. Make sure the checkbox for First Row Contains Column Headings if that is the case. Click the Next button.
   
7. The next wizard window allows you to be specific about the field information being imported by modifying the field information in the Field Options area. For our purposes the defaults work.
   
8. The next wizard window allows you to define a primary key for the new table. Primary keys uniquely identify every record in a table to be able to return data quickly. For this example, we'll let Access add the primary key.
   
9. Access is now ready to import the Excel file. There is a checkbox to select if you would like the table to be analyzed after the data is imported. It will remain unchecked for this example. Now click the Finish button.
   
10. Access now asks if you would like so save these import steps just walked through. If so, click the Save import steps checkbox. We'll leave it unchecked for now.
    
11. The Excel information is now imported into Access and can now be use in Access or even added to as more Excel information is discovered.

Practice Questions

Now that you've seen how to import Excel data into a new Access table, watch this video that walks through the steps and demonstrates how to add Excel data to an existing Access table. While the video shows an older version of Word (Microsoft Word 2016), the same steps work in other versions of Word, including 2019 and 365.

You can also view a transcript for the video "How to Import Excel Data into Access" here (opens in new window).

Contribute!

Did you have an idea for improving this content? We'd love your input.

Improve this pageLearn More

How to Create a Database From an Excel Spreadsheet

Source: https://courses.lumenlearning.com/wm-computerapplicationsmgrs-2/chapter/access-data-from-excel-table/

Share this post