Excel Import Cleanup 2
By Richard Rost
2 years ago
Import Excel & Remove Header/Footer Rows, Part 2
In this Microsoft Access tutorial, I will show you how to automatically remove unwanted header and footer rows from imported Excel data, creating a clean data set for your database. We'll work with record sets, employ date validation, and finalize the data for efficient importing. This is part 2.
Members
There 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!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, Excel import, Microsoft Access, remove header rows, delete footer rows, automated cleanup, Access VBA, isDate function, record set management, Visual Basic Editor, loop through records, query design, CDate function, merge fields, data cleanup, ISO dates, AccessLearningZone
Intro In this video, we continue with part two of the automated Excel import cleanup series in Microsoft Access. I will show you how to use VBA to loop through your imported data, identify and keep only the records with valid dates, and remove any extra rows from the top and bottom of your spreadsheet. We'll also talk about using record sets, the isDate and CDate functions, and how to merge date and time fields together to create a clean data set ready for your database. This is part 2.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Today is part two of my automated Excel import cleanup video series, where I'm teaching you how to import a spreadsheet with junk at the top and junk at the bottom. We're going to clean up all that junk and give you a nice clean table. This is part two, so if you haven't watched part one yet, go watch part one, then come on back.
At the end of part one, we imported our data. We've got just to the first row here; everything above it was deleted. We looked for that word, that phrase, contact date. Now what we're going to do is we're going to say, okay, I'm going to assume every row here down has a valid date in it. If not, as soon as I find a row that does not have a valid date in it, we're going to delete everything after that point. Okay, and this, of course, assumes that's how your data is set up. Yours may vary. You handle it however you want to handle it. I'm just showing you the Lego pieces.
All right, so let's go back to our code. Now, I have two buttons up here on my quick launch toolbar. This just opens up the Visual Basic Editor. This one opens up the code for the form that you're in, but you have to be in design view. Sometimes I get lucky, I just hit this, but this opened up to whatever random module I happen to be in, which was the customer list form, which is not where I want to be. So we have to go into design view. And now I can hit this button. It'll take me to this guy's code.
Here we are. We've deleted the header rows. At this point, I do want to close the record set because, like I said before, we're going to be sitting in a record set that's open. It's got four deleted records at the top. When we close this and then reopen it again, we're going to get a nice, fresh record set, with this as the first record. Now we just have to loop down until we find that.
Instead of working with an already open record set, which the pointer gets messed up when you start deleting records in a record set, things tend to get weird. So I try to avoid it. I'll close and reopen the record set if I can. Yeah, there are ways to refresh it. No, this is just easier. There's what's best and what's practical. This is practical. Sometimes I like to put in the actual comments, right? Importing sheet. This is more for you. I like how the green kind of stands out, right, deleting header rows. And you can very quickly and easily see where you're at in the code. Loop through valid records with dates.
Now we're going to start looping through records. We're going to use another variable similar to done. I'm going to call it found end. I've found the end of the records. Could you reuse done? Yeah, you could, but I'm just going to declare found end as a Boolean. Just makes more sense in my head. I don't know why. We're going to reopen the record set. If you want, you can just copy this. Copy that. Paste it here. We're going to say found end equals false, just like we do with done. Whoops, I hit enter one too many times there.
Okay, while not rs.eof. Now with this one, we are going to loop to the end of the entire record set. Found end is just going to determine whether or not we delete something in the middle there. Same thing with the s. We're going to say s equals the current record's f1 field. If it's a date, we're just going to continue on our merry way and keep reading until we find one that's not a valid date. What can we use to figure out if it's a valid date or not? There happens to be a function called is date, and that will tell you if it's a date or not.
If you look carefully here, look at the data in here. That's an actual string value. This got imported as a string. I can tell because it's lined up on the left side of the field. This, on the other hand, came in as a number. That's the time. Access imported that as an actual time value. But this is imported as a date or as a string, excuse me. I can also tell because my default time value is ISO time, which is year, month, day, which is how everything should be. It's one of my life missions to get everyone to convert to ISO dates year, month, day. It makes sense everywhere. There's no confusion. Do it. Go watch this video.
All right, so anyhow, where are we? We've got a string value. Now we're going to check to see if that is a valid date. It'll look at a string value too and say, okay, the value that's in this string is a date. I'm going to say if this is not a valid date, if not is date s, then we found the end. We found the last record. So found end equals true. Now, if I am at the end, if I found the end, I'm going to delete everything from this point on till I hit the end of the record set. So if found end, then rs.delete. Goodbye. You are the weakest row. Goodbye. And then I still got a rs.move next.
When rs.close, close. And then we can set our rs equal to nothing. Let's bring this guy down. We don't put that up there. We still want rs. We'll make it nothing down here. There we go. And then you can status done if you want to. And hey, when we're done, let's open up that table. Take a look at it. DoCMD.openTable ExcelImportT. All right. Save it. Debug compile once in a while. I'm going to close that. Close it. Click it. Run it.
And look at that. See? Let's make sure that Boramir is the last record. And it is. So what happened was it looped down all this. That's a valid date. That's a valid date. That's a valid date. Got down to here, which is not a valid date. As soon as it hits that, found end is equal to true. From this point to the rest of the loop, everything after that point is now deleted. If found end, rs.delete. If found end, rs.delete. Because nothing else will set it back to false. It's just going to continue to the end of the record set and mark everybody. Bye bye.
And so that's the process. Now we're left with a clean set of data. You might want to clean this up a little bit more. You could do that with a simple query. Let's create a query design. Let's bring in that temp table. And what can we do here? Let's see. Well, we can make a valid contact date time. Contact date time by merging those fields together. CDate, which is convert to date, F1. And then we can add to it F2, because this is numeric. And remember, if we take a date value and add a number to it, that's a number of days. So 0.5 is noon, half a day. Let's see what that looks like. Okay.
There we go. We put our date time together in one nice field. That's verify. Let's see here 10, 15, 10, 30 AM, 11, 11, 30. Yep, looks good. And then we can bring the other fields into whatever we got. Let's see. The customer name is in F3. The notes is in F4. And the phone number, did they have phone numbers in Middle-earth? That's F5. And there we go. Now we've got a nice set of data that we can now go forth and do whatever we want with in our database. We can import it anywhere, existing contact table, or whatever you feel like doing. Save this as my Excel Import Q, and I'm off to the races.
And there you go. That's how you take a sheet, delete whatever extraneous information is off the top of it, read in all the records, delete the crap off the bottom, and then you're left with a nice clean set of data just like this. That's it. That's pretty good, right? No manual editing required. See, we spent, what, 20, 25 minutes setting this up. Now it's going to save us at least a minute or two every day from having to open this thing up and make changes before we import it.
Some other videos for you to watch. Here's a video that talks about variables in more detail. Here's a video where I talk about that is date function. This is cool because you can use it to check and see if a year is a leap year. Just make a date calling it February 29th of that year and see if it's a valid date. And that is date will tell you. That's pretty cool.
And of course, if you like learning with me, if you like my style, if you enjoy watching my videos and you want to learn more about how to program in VBA for Microsoft Access, come and check out my developer lessons. I've got tons and tons of lessons available on my website starting from the basics. We walk you through step by step, all the stuff in the order that you should learn it. It's a really good course. I had a good time recording it and I'm still making new ones. I've got developer 46 coming out pretty soon. If you want to learn more about record sets, specifically I start covering them in developer 16.
I say start covering them because record sets are pretty in-depth and involved. I use them all the time. I constantly use record sets. They're really, really cool. I've got dozens of lessons on record sets starting with developer 16. That's going to do it. That's your TechHelp video for today. Hope you enjoyed this little two-part series. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Importing spreadsheets with extra header/footer data
Cleaning up imported data in Excel with VBA
Looping through record sets in VBA
Verifying valid dates using isDate function
Deleting invalid records in record sets
Creating queries to clean up data
Merging date and time fields in Access
Using CDate function to convert strings to dates
Appending numerical time to date fields
Organizing fields into a clean data set in Access
COMMERCIAL:
In today's video, we're continuing with part two of our automated Excel import cleanup series. You'll learn how to tidy up your spreadsheets by removing unwanted data from the top and bottom, ensuring only the necessary records remain. We'll dive into loop functions, utilizing VBA to identify valid dates and discard anything beyond that point. You'll see how to merge fields into a clean data set, ready for use in your Access database. Stick around to polish your data import skills without manual edits. 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. What is the purpose of the video series mentioned in the transcript?
A. To explain how to manually edit an Excel spreadsheet before importing
B. To interactively edit real-time spreadsheets with others
C. To teach automated methods for cleaning imported data in Excel
D. To demonstrate various Excel formulas to make data visualization easier
Q2. Why is it recommended to close and reopen the record set after deleting header rows?
A. It is required to refresh the data table in Excel
B. It prevents issues with pointer references after deleting records
C. It helps to save RAM and improve system performance
D. It automatically backs up the data before performing more operations
Q3. What is the function 'is date' used for in the context of the video?
A. To validate if a string can be converted to a number
B. To determine if a cell contains a valid date
C. To convert strings into different date formats
D. To calculate the days between two given dates
Q4. What happens when a non-date value is encountered during the loop through the records?
A. It skips the record and proceeds to the next one
B. The record is marked but kept for further analysis
C. An error message is displayed, stopping the process
D. All subsequent records are deleted
Q5. According to the video, why does the instructor prefer using ISO dates?
A. They include time zone information by default
B. They are easier to read and comprehend than other date formats
C. They are universally accepted and reduce ambiguity
D. They are automatically supported in older versions of software
Q6. What is the purpose of the temporary query created near the end of the video?
A. To sort and organize the records for printing
B. To verify correct conversion and combining of date and time fields
C. To remove duplicates from the imported dataset
D. To provide a backup before making final changes to the database
Q7. What does the variable 'found end' indicate in the script?
A. It marks the beginning of valid records
B. It signals the discovery of the first record with valid data
C. It determines whether further action is needed to handle non-date fields
D. It identifies and confirms the endpoint of valid records
Answers: 1-C; 2-B; 3-B; 4-D; 5-C; 6-B; 7-D
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 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. Today, we continue with part two of my series on automating Excel import cleanup. In this lesson, I will guide you through the process of importing a spreadsheet that has unnecessary information at the top and bottom. The goal is to eliminate that clutter and create a clean table. This is building on what we discussed in part one, so if you haven't viewed part one yet, please do so before proceeding.
At the conclusion of part one, we successfully imported our data up to the first valid row, removing everything above it. We identified this row by searching for the phrase 'contact date.' We will now assume that every row following has a valid date. Our task is to delete the data beyond any row that lacks a valid date, contingent on your data's structure. This is just a template to illustrate possible steps; your data may require different handling.
I'll explain how we're going to proceed. I will open the Visual Basic Editor and the code related to the current form, ensuring I am in Design View. I want to close the existing record set since it contains unwanted deleted records at the top. Once we close and reopen it, we will obtain a fresh record set, starting from the first valid record.
To avoid the complications that arise when manipulating an open record set with deletions, it's more practical to close and reopen it. Though there are ways to refresh a record set, closing and reopening is simpler and more efficient.
In the code, I like to add comments for clarity, like 'Importing sheet' or 'deleting header rows,' which make it easier to track the execution of the code. We will start looping through the records using a variable I will call 'found end,' a Boolean value indicating the discovery of the last valid record. While looping, we will rely on the IsDate function to identify valid dates. If a date isn't valid, 'found end' will be set to true, and we will proceed to delete subsequent rows.
When I determine that the end has been reached due to invalid data, I will delete all following rows. The process involves moving to the next record until the set is closed. It's important to ensure that we continuously manage and release resources correctly by closing the record set and setting it to nothing.
Our task concludes by opening the table to verify its state. By using a query, we can further refine the table to include valid contact dates by merging date and numeric fields, creating a complete datetime entry. The approach is to ensure that we handle these modifications without needing manual editing later on.
This session outlines the process of managing and cleaning up imported data, creating a database-ready dataset. The time spent setting this system up will save time on repeated tasks in the future by automating the preparation step.
If you're interested in further learning about topics such as variables, functions like IsDate, or more detailed VBA programming for Microsoft Access, developer lessons are offered on my website. These lessons start with the basics and progress step by step. Additionally, if you're keen on understanding record sets, you can find in-depth lessons starting from developer level 16. Record sets are a vital part of many tasks and I frequently explore them in my teachings.
That concludes today's tutorial. This two-part series aims to provide a solid understanding of automated Excel imports in Access. Live long and prosper, my friends. Visit my website for a complete video tutorial that provides step-by-step instructions on everything discussed here.Topic List Importing spreadsheets with extra header/footer data
Cleaning up imported data in Excel with VBA
Looping through record sets in VBA
Verifying valid dates using isDate function
Deleting invalid records in record sets
Creating queries to clean up data
Merging date and time fields in Access
Using CDate function to convert strings to dates
Appending numerical time to date fields
Organizing fields into a clean data set in Access |
