In a previous post, I talked about getting your Excel data into an Access database, and gave you some tips for smoothing out that process. If you missed that article, you can see it here.
For that post, I helped you get your Excel data into Access in the form of a new table. This is a great way to get started in Access, but sooner or later, you’re probably going to want to add the Excel data to an existing Access table. Today we’ll take a look at how to do that.
First off, let’s talk about using the Import Wizard to append records to an existing table in Access. If you’ve used the Import Wizard, you may have noticed this option:
This is a great feature, and if it works in your case, use it. In my experience, there are a lot of things that can cause this to fail, most notably, unexpected data in the Excel file you’re trying to import. For this reason, I often end up creating a three-step process:
Import the Excel data
Clean up the imported data
Append the data to an Access table
Step 2 is optional, but in my experience, there is almost always some cleanup to be done on Excel data. Don’t get me wrong, I’m not dissing Excel. It’s a great tool. But because it’s not a database tool, it allows you to get sloppy with your data. We’ll look at a couple examples of this.
The cleaning step could also be completed in Excel, but there are some tools in Access, like the Find Duplicates Query, that might make the task easier. I’m not going to cover those tools here. For purposes of this discussion, let’s assume that you’re going to import your data into Access before cleaning it.
The Set Up
Let’s take a look at the data I’m working with. Here’s the original Excel file that I started with:
I imported that into a new table in my Access database, and renamed that new table to “tblVendors”. It looks like this in Datasheet view:
In my scenario, new vendors are continually added to an Excel sheet. You might be wondering why they don’t get entered directly into Access, and that’s a good question. There are many reasons why that might not be happening. For purposes of this exercise, let’s say that vendor data is sent to us from a third party – maybe a marketing firm that is collecting contact data for us, or an outside company that is managing our vendors. For whatever reason, we receive a new Excel sheet every month with new vendors. Here’s the file we received for October:
If you’re paying attention, you’ll notice that the format of this data is not quite right. The state, which is supposed to be in the first column, is missing.
In a perfect world, we’d push back on the source of this data and ask that it be corrected. In the real world, though, this type of thing happens frequently, and you often can’t wait for the correction before moving forward. So, we’re going to have to clean up this data.
Let’s look at a couple more examples. Here are the new vendors for November:
I’ve got two problems with this data. First, notice that the states are abbreviated. In our Access table, the states are spelled out. Although this data will import with this mistake, it’s likely to cause problems in the future with my queries and reports, so I’d prefer to correct it before adding it to my Vendor table.
More importantly, notice that there are address columns. We don’t have a spot for them in our Access table. We’re going to add an address column to our table, but we’ll only add one of those, so these two columns will have to be consolidated.
Here’s one more example of bad data, this one showing our new vendors for December:
Oh boy, they’ve really made a mess of things here, haven’t they?
There are extra headings on rows two and six. Row five is blank. And take a look at row 4 - it’s got a state, but not a name, contact or other details.
All of these are going to cause problems when we import the data into our Access table.
As I mentioned earlier, for all of these reasons, my preference is to import the data into a new table, clean the data there, and then append the cleansed data to my Vendor table. Let’s take a look at how to do that.
Step 1: Import the Excel data into a new table
Use the import wizard to import the excel data, as you normally would. When I import my October vendors, my imported table looks like this:
Step 2: Clean up the imported data
For this set of data, all I need to do is add the State column. I’ll open this table in Design View, and add a column for the state. I’ll do my homework to discover which state each of these new vendors is in, and I’ll set the values in Datasheet View:
Note: I’m adding State as the last column, whereas it’s in the first column in my Vendor table. That’s OK. You’ll see in a minute that the sequence of columns doesn’t matter.
Step 3: Append the data
I’ll now create an Append query to add this data to the Vendor table.
An Append query is very similar to the Select queries that you’ve probably already created in Access, but there are a couple of additional steps to identify the table you’ll be appending the data to.
I’ll start by creating a Select query on my October data, which will look like this:
Notice that I called out each individual column, rather than using the “*”, which selects all columns. This is necessary in an append query, because we are about to create a mapping between this table (in my case, “New Vendors October”) and the original Access table (in my case, tblVendors), where we’ll identify the associated columns between the two.
To do so, I’ll change this from a Select query to an Append query by clicking the “Append” button on the “Query Design” tab:
When I do so, I’ll be prompted to select the destination for the data – the table that I’ll be appending data to. In my case, that’s tblVendors, which I’ll select from the drop-down list:
When I click OK, I have a new line in the grid, labeled “Append To:”. You’ll see that several of the values are already filled in:
This is where you identify where every column in the imported table will reside in the Access table. In my case, values in the “Contact” column of my imported table will end up in the “Contact” column of my Vendor table. Same for Email, Phone and State.
Notice, though, that under the “Name” column, there’s no value in the Append To row. That’s because the column name in the imported table (“Name”) doesn’t match the column name in the Vendor table (“Vendor Name”).
I’ll click on that cell, and use the drop-down arrow to select the Vendor Name column:
Now the Append query is ready to execute. When we do so, each record in the imported table will be appended to the Vendors table. Once again, note that it didn’t matter that the sequence of columns was different between the two tables, because I mapped each of the columns by column name.
I use the “Run” button in my Query Design toolbar to execute this query. I get a prompt when I do so, asking me to confirm that it’s OK to add the data to my Vendor table:
Obviously, I’ll click “Yes” to append the rows.
IMPORTANT: Only execute the query once! Each time its executed, it will re-append the data to the Vendor table. You’ll end up with duplicate records if you run your append query more than once.
ALSO IMPORTANT: As the message above indicates, there’s no “undo” option once you’ve appended the data. You may want to back up your table or your entire database before you execute this query.
After I run my append query, my Vendor table looks like this, with the two new rows at the bottom:
Now I’ll repeat the process for the November. In this case, remember that the November file contains two extra columns: Address and Address 2. I’m going to add a column to my Vendor table for Address, but I’m only going to add one of them. There won’t be an Address 2 column. There are many reasons that I make this choice, which I won’t go into here. For this exercise, let’s just assume that this is the correct design decision.
Since I only have one Address column in my Vendor table, I’ll manually combine the Address and Address 2 columns in the Import table, by opening the table in Datasheet View, and typing the correction into the Address column. I’ll also correct the State values while I’m here:
BEFORE:
AFTER:
I’m going to ignore that Address 2 column. When I create my append query, I’ll leave it out:
After I append this data, my Vendor table looks like this:
Finally, let’s look at how we’ll correct December. It’s the same basic process. Import the data, open the imported table in Datasheet View, and make the corrections:
BEFORE:
AFTER:
As you can see, I removed all of the extraneous rows.
The append query for this data looks just like the others:
And here’s the final result in my Vendors table, after I’ve added the December records:
As I mentioned earlier, this is only one of many possible ways to get your Excel data into your Access table. It happens to be the one that I use most often, because I’m usually working with messy Excel data. I hope you find this useful, and as always, reach out via email if you have questions.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2022. All rights reserved.