Excel Import Cleanup
By Richard Rost
2 years ago
Import Excel to Access & Remove Header/Footer Rows In this Microsoft Access tutorial, I will show you how to automate the process of importing Excel data into Access while removing unwanted header and footer rows. We'll tackle a common issue of having extra rows at the top and unnecessary summary rows at the bottom to streamline your data import process. Brent from Midwest City, Oklahoma (a Gold Member) asks: I receive a daily quote report with multiple rows above the header and a totals row at the bottom that I don't need. Currently, I manually clean up the sheet before importing it into Access, but I'd like to automate this process. I've watched the import tutorials, but I'm not sure how to handle the unnecessary rows in an automated setup. Any guidance would be appreciated. MembersThere is no extended cut, but here is the database download: Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, Excel to Access automation, Automate Excel import, remove header footer rows, Access database import, VBA recordset tutorial, Excel data cleaning VBA, delete top rows Excel VBA, Access VBA data import, clean spreadsheet for database, Brent's import challenge
Intro In this video, I will show you how to automate the process of importing data from Microsoft Excel into Microsoft Access, even when your Excel spreadsheets contain extra rows above the header or summary data at the bottom. We'll cover using VBA and DoCmd.TransferSpreadsheet to bring in data, setting up error handling, and working with record sets to identify and delete unwanted rows. You'll learn how to clean up imported data to ensure only the records you need are added to your Access database.Transcript Welcome to another TechHelp video brought to you by Access LearningZone.com. I'm your instructor, Richard Rost. Today, we're going to talk about automating the importing of data from Excel to Access. Now, I've covered this before, but this is a specific situation where there might be some extra garbage in the spreadsheet. Sometimes people add stuff at the top, like extra rows with meaningless information, or summary rows at the bottom, which don't do you any good if you just want to get this data into Access and start working with it in your database.
In today's video, I'm going to show you how to automate that import and clean up any unnecessary rows that are in that spreadsheet. Today's question comes from Brent in Midwest City, Oklahoma, one of my gold members. Brent says, "I receive a daily quote report with multiple rows above the header and a total row at the bottom that I don't need. Currently, I manually clean up the sheet before importing it into Access, but I'd like to automate this process. I've watched the import tutorials, but I'm not sure how to handle the unnecessary rows in an automated setup. Any guidance would be appreciated."
So this was posted in the forums, and here is a sample of what Brent is dealing with. Here's his sheet right here, and you can see that there's some nonsense up at the top. I get stuff like this all the time, so he basically wants to delete these top rows up here that don't have normal data in them. Here's the rest of the data, and then at the bottom—it's not shown on here—but at the bottom, there are extra summary rows. So we want to get rid of all that stuff too.
I've put together a spreadsheet from the last Middle-Earth contact trade show where all the Middle-Earth people get together. I've got the contact date and time, customer notes, phone number, and so on. I've got some rows at the top here that might contain meaningless information, and then at the bottom, there's a count of how many records there are, and I don't need that stuff either. I just want the stuff in the middle. I just want the records because I want to pull this into my Access database and be able to work with it.
So what we're going to do is import this data into Access, as I've shown you previously, and I'll talk about that video in just a minute. Then we're going to delete everything up until and including that header row. I'll show you how to do that with a record set. We're going to loop through it. Then once that's done, we can look and make sure these are all valid records with valid dates, and as soon as we find one that's not, we'll delete everything after that point as well. So that's our algorithm.
Now, what do you need to be able to get to this point? Well, first, make sure you watch this video where I show you how to import a specific sheet from Microsoft Excel into your Access database. Watch the developer part of it because we're going to be using the same code today—that transfer spreadsheet code. This will, of course, require some knowledge of VBA. If you've never done any VBA programming before, go watch this video. It's about 20 minutes long and will teach you everything you need to know to get started. Also, go watch my video on record sets. A record set is a programming tool that allows you to loop through the records in a table. So after we import them, we can loop through and examine each record and decide what we want to do with it. We want to delete it, keep it, and so on.
These are all free videos available on my website and YouTube channel. Watch those first and then come back.
Now, before we get started, I want to mention that there are some things you could do manually. You could open up the sheet and delete these rows yourself. Or you could set up a named range and then just import that specific name range. But what Brent wants is not to have to mess with all this, and I get it. I've got some stuff myself that I have to import once in a while from other vendors, and it's a pain to open up the file, make changes, and save it. If I can save a minute a day by having something automated, that'll save me a couple of hours over the course of a year. So it's worth it if you can spend an hour or so to automate it, and then it's done. We're not going to do any manual manipulations here.
What we are going to assume, though, is that the first row here in the header has the phrase "Contact Date." If the person who controls this spreadsheet changes this, you may have to update your code. But in cases like this, most of the time, that doesn't happen often. So that's the only assumption we're going to make.
Let's start by getting the data into the database. This is the TechHelp free template. You can grab a free copy of this off my website if you want to, but this will work with just about any database. I already have a button right here called "Hello World," and we'll call this "Import Excel Data." I'm going to right-click, build event, that'll open up a code builder. Let me resize this to fit. Sometimes when I'm playing around off-camera, I resize this thing to make it easier to work with. Okay, Status "Hello World," let's go status "Importing Excel Sheet."
What's the command we did in the other video? Let's go over to get it real quick: docmd.transferspreadsheet. That guy. We are going to import, so acImport. Hit comma again. That'll ignore that version type. The table name I'm going to put it in is "ExcelImportT," comma. What's the file name? I'm going to go to the next line here, line continuation character. The file name—I'm just going to copy and paste it here because I've got it saved in my notes. I don't feel like going to get it again. It's right there. There's a file name: "Vmy_drive\\spreadsheet\\middle_earth_trade_show.xlsx" and next, most importantly, "HasFieldNames" is going to be set to false. That's it because it's going to have a header, and we're going to delete it when it comes in. Again, this is dependent on these columns not changing. Usually, when you get something from a vendor, for example, the layout of this doesn't change that often. It might; you might have to adjust this depending on any changes they make.
Let's test this to make sure it works. I'm going to hit save, come back out here, close it, open it, and hit the button. There we go, ExcelImportT. You can see now there's garbage in here. It automatically ignores any rows at the top that are completely blank, and it should also ignore any at the very bottom that are blank. So this one here is the last one that it imports.
Now we can see what we can do is loop through these records and delete everything up to and including that header row. We're going to look for "Contact Date."
The very next thing I want to do is either delete or clear this ImportT if it exists. If you just run a delete query and delete these fields, if they do change this in the future and add another column, you'll get an "F6" over here, and that data won't fit in this table. So I recommend just deleting the table, and then it'll recreate it when you run the import again.
Let's go back to the code editor. Before we import, we're going to come up top here and say, "Status: Deleting previous sheet import table." How do we do that? DoCmd.DeleteObject. What is the object type? It's acTable. What's the name? "ExcelImportT." Save it, and now if you run it, it'll delete that table and then import a fresh copy. Otherwise, it's going to keep importing on the bottom of the current one if the table already exists.
One problem that will happen is if you manually delete it because you know it's a temporary table and try to run your code, it will say, "I can't find it." So we need a little bit of error handling. Just say, "Hey, this next line might throw an error. If it does, just ignore it." We don't need robust error handling here; "On Error Resume Next" will work just fine. That says if this guy errors out, ignore it. But make sure to turn that off when you're done because you want to know if there's an error in any lines after that one. So here we'll say, "On Error GoTo 0," which turns the error handling back on.
Of course, if you want to learn more about error handling, there's a video for you.
Now we've got that spreadsheet data in my temporary table. I want to look at this—not the wrong table—I want to look (I deleted the table) at this again. There it is. I want to look at this and start from the first record and go down until I find that "Contact Date" header. Does this one have it? Nope. Move to the next record. Does this one have it? Nope. Move to the next record, and so on. Along the way, if that's not it, we'll delete that row. Then we'll have the first record of the data we want.
That's our algorithm. How do we code that?
We'll need some variables. Let's use "rs" for our record set. We'll also need to indicate whether or not we're done, so "done" as a Boolean. I like to put the values I'm reading and evaluating in a variable too, like "s" as a string. Now we'll do "Status: Deleting header rows" and set "rs" to "CurrentDb.OpenRecordset(ExcelImportT)."
I always put the shell of it in here. I say, "While Not rs.EOF" and then "rs.MoveNext," and then "Wend" and then "rs.Close" and "Set rs = Nothing." I always put that stuff in when I'm working with record sets—it's the basic shell of the loop. Don't forget to move next and don't forget to close things up at the end and clear your variables. But now I'm not just looping until the end of the record set; I'm looping until there are no conditions where I'm done—I'm done when I hit that "Contact Date" field.
First, let's initialize "done" as false, and we'll say, "While Not rs.EOF and Not done," which means both of those conditions have to be true. You have to be inside the record set loop, not at the end yet, and not done. Once done, exit.
We'll read that first field, which will be "f1." Those are just field names the import assigns. We're looking for "f1," which equals "Contact Date."
I'll say "s = rs!f1." The problem is if that's null, it'll give me an error because "s" is a string, and you can't easily put nulls into strings like that. So we'll use "Nz" and say if that is a null value, put an empty string in there. That just gets rid of the "Invalid use of Null" problem.
Now we'll say if "s = 'Contact Date'," then "done = True." I'm done. This will be the last row I have to delete.
We'll say "rs.Delete" at the current record, "rs.MoveNext" is already there, and then it loops.
With the algorithm here, let's take a look at the records. Let me try to get these sideways. We'll start with "done" as false. I'm sitting on this record now, and, "if rs.EOF—nope, that's not true—are we done? Nope, not done." "S" equals that first record; it's "Middle Earth," so it's not "Contact Date." We'll delete that row and move to the next record. Deleting records with a record set loop is a little different than deleting records in tables, queries, and forms. This record will stay there; it'll just say "Deleted." You still have to move the cursor to the next record. So delete that record and advance the cursor.
Move to the next record. Now it's "null" or "empty string," so continue on. Delete it, move to the next record, and continue. Now we're on "Contact Date." So now s is "Contact Date," yes. So "done" is true. We've set "done" to true, but we're still going to delete and move next. So delete this, move next. I'm sitting here, but now loops; "done" is true, and it exits. This should have the effect of deleting all at the top of the table.
Ready to see it in action?
Everything looks good. Debug, compile once in a while, and hit it. Now, let's take a peek. There we go. Let's double-check: is Bilbo Baggins the first record? Yes, Bilbo, Frodo, Golem, araGrad. We're good. We got the correct records in there.
Next up, we have to go down this list and find out where the last valid record is and delete everything after that. It's similar to what we just did with a slight twist, and we'll talk about that in tomorrow's video. Tune in tomorrow, the same time, the same channel. Or, if you're a member, you can watch it right now because that's one of the benefits of being a member.But that's going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part two.
A special thank you and shout out to our diamond sponsors. First, we have Juan Soto with Access Experts Software Solutions, Manufacturing Experts, specializing in Access and SQL Server. Juan is a 13-time Microsoft Access MVP. You can check them out at accessexperts.com.
Another shout out to Sammy Shama from Shama Consultancy. Sammy is a certified Microsoft Office specialist and he not only offers access application development, but he also provides one-on-one tutoring services. So if you need someone to hold your hand and help you with your Access project, Sammy is your guide. Check them out at ShamaConsultancy.com.
TOPICS:
Automating data import from Excel to Access
Handling extra rows above the header
Removing summary rows at the bottom
Looping through records with record sets
Checking for valid data before import
Using DoCmd.TransferSpreadsheet for import
Error handling in VBA code
Deleting existing tables before import
Setting up variables for record sets
Identifying and removing header rows
Deleting unwanted rows with record sets
COMMERCIAL:
In today's video, we're going to learn how to automate importing data from Excel to Access, even when the spreadsheet comes with extra garbage like unwanted rows at the top or summary rows at the bottom. You will discover how to clean up these spreadsheets for streamlined data entry into your Access database. Whether you're dealing with reports full of nonsense or need to automate repetitive tasks, we're here to make it easier. Plus, we'll take a look at deleting unwanted header rows and ensuring only valid data makes it into your database. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. Why does Brent want to automate the process of importing data from Excel to Access?
A. To increase data storage capacity
B. To improve data security
C. To save time by avoiding manual clean-up of unnecessary rows
D. To enhance spreadsheet design
Q2. What type of code does the instructor mention will be used to automate the data import process?
A. JavaScript
B. SQL
C. Python
D. VBA (Visual Basic for Applications)
Q3. What is the purpose of a record set in this context?
A. To visualize the data in a graphical format
B. To store data temporarily for backup
C. To loop through records and perform actions on each
D. To secure data with encryption
Q4. What assumption is made about the header row in the Excel sheet?
A. It contains the number of records
B. It has the phrase "Total Sales"
C. It is always at the bottom of the sheet
D. It contains the phrase "Contact Date"
Q5. What command is used to import data into an Access database?
A. docmd.exportspreadsheet
B. docmd.sendobject
C. docmd.transferspreadsheet
D. docmd.deleteobject
Q6. What does the "HasFieldNames" parameter set to false indicate during the import process?
A. The spreadsheet contains no field names
B. The spreadsheet has duplicate field names
C. The spreadsheet field names will change
D. The spreadsheet contains a header that should be ignored during import
Q7. How does the instructor suggest handling error situations during the deletion of the import table?
A. By returning a detailed error message
B. By creating custom error messages
C. By using "On Error Resume Next" to ignore the error
D. By logging the error for future reference
Q8. What should be done if the structure of the spreadsheet changes in the future?
A. The import process should be stopped entirely
B. Adjust the VBA code to accommodate the changes
C. Manually import the data each time
D. Ignore any changes and continue with the original code
Answers: 1-C; 2-D; 3-C; 4-D; 5-C; 6-D; 7-C; 8-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone is about automating the import process of data from Excel into Microsoft Access, specifically when dealing with spreadsheets that contain extraneous information. I'm your instructor, Richard Rost. We'll address situations where Excel sheets might have unnecessary rows at the top or bottom, which can complicate the import process.
This topic arises from a question posed by one of our gold members, Brent, who deals with a daily quote report cluttered with unwanted rows above and below the needed data. Currently, he cleans up the file manually before importing it to Access and seeks a way to automate this task.
I've created a sample spreadsheet simulating Brent's issue, involving extra rows at the top with irrelevant data and summary rows at the bottom. Our goal is to import only the necessary data into Access, eliminating all unwanted rows.
Initially, we'll tackle importing this data into Access by discussing how to remove everything above the header row and unnecessary records at the bottom. This process involves setting up a record set and looping through the dataset until we find the genuine header, then erasing any surplus records.
To embark on this task, it's crucial to refer back to previous tutorials on importing Excel sheets into Access, specifically those covering the 'TransferSpreadsheet' command within VBA. You'll need some VBA proficiency, so if you're new to it, watch my introductory video on VBA programming.
As we start, I'll assume the spreadsheet's header contains the phrase "Contact Date." Should this change, you'll need to modify your code. However, such structural changes in reports are infrequent.
We'll systematically process the spreadsheet data using the TechHelp Free Template, downloadable from my site. I'll guide you through setting up an 'Import Excel Data' button to automate the data import process. Using VBA commands like 'DoCmd.TransferSpreadsheet,' we'll import data into a temporary Access table named "ExcelImportT."
Before each import, we should delete the existing temporary table to avoid appending to current data. We'll employ error handling to gracefully manage any missing table scenarios without program interruptions.
Once the data is in Access, we'll loop through the records, purging each until we hit the genuine header row marked by "Contact Date." This operation involves using a record set to pinpoint and remove unnecessary rows both above and below the core data. I'll demonstrate how to build this logic using a basic loop and conditional statements within VBA.
You'll find that automating this process not only saves time but also minimizes human error. While today's tutorial focuses on trimming excess data from the top, tomorrow, we'll continue our discussion on managing data at the spreadsheet's bottom. For those eager to see what's next, remember that members can access subsequent video installments right away.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Automating data import from Excel to Access
Handling extra rows above the header
Removing summary rows at the bottom
Looping through records with record sets
Checking for valid data before import
Using DoCmd.TransferSpreadsheet for import
Error handling in VBA code
Deleting existing tables before import
Setting up variables for record sets
Identifying and removing header rows
Deleting unwanted rows with record sets |
