How to Import Excel Data Into Access

This is the first in a series of lessons where I demonstrate how to integrate Excel data with an Access database. In this lesson you will learn how to:

• 

  Work with External Data

• Import an Excel worksheet as a new Table in Access.
• Append an Excel worksheet to the Table that we created with the Imported data.
• Create a link between the Source Excel worksheet and an Access Table.
• Refresh the link between Excel and Access.

First, on the Excel side of this process, make sure that you have clearly identified the Field Headers in the Top Row of your data set. If you have and cells that contain Formulas, convert the formulas to Cell Values.

Begin the Import Process

You initiate the Importing from inside Access. Go to the External Data tab on the Ribbon (in Access 2007) and choose Excel. Follow the steps in the Import Wizard. I recommend that you allow Access to create a Primary Key for the new table. This will allow you to set up “table relationships”  inside Access.

Append Excel Data to an Existing Access Table

Once you have at least one Access Table set up, you have another option when you Import Excel data – you can “Append” Excel data to the Access Table. Be sure that your “field headers” match each other. Once again, follow the steps in the Import Excel data Wizard.

Notice that with each of these Import Wizards you are given the option to save your Import definition. If you will be importing data from the same Excel source on a regular basis, this “Saved Definition” will save you a lot of time.

Link to Source Data

You can decide to establish a “link” to the source data – in this case an Excel worksheet. If you do, you will see a special “icon” next to your Access table that indicates that the table is linked to an Excel source. In this case, you can not make any changes to the tabel from inside Access. You make all changes – adding, editing and deleting records – from inside Excel.

With a link to the source data, remember to “refresh” the link to see the updated information.

Watch this Video Lesson – Embedded from YouTube

As an experiment, I am embedding this video as a YouTube Video. Click on the video and you will be watching it in High Definition, Full Screen mode on my YouTube Channel – DannyRocksExcels.