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.