Using Access to Clean Excel Data, Part 5
I’ve been covering the use of Microsoft Access tools to clean your Excel data for the past few weeks. Today is the final entry in that series, and our topic is getting the cleaned data back to Excel.
To recap, my premise is this:
Microsoft Access has lots of tools that can help you clean up your Excel data. I’ve covered many of them in the last few posts, including the find duplicates query, update queries and several useful functions.
Here are links to the other articles in this series, for your convenience:
Importing Excel Data to Access
Appending Excel Data to an Access Table
Using Access to Clean Excel Data, Part 1: Group By and Update queries
Using Access to Clean Excel Data, Part 2: Replace, Mid and Right functions
Using Access to Clean Excel Data, Part 3: Nested functions
Using Access to Clean Excel Data, Part 4: Finding duplicate and nearly-duplicate entries
Crossing the Finish Line
The final step is to return the cleaned data to Excel.
Before we do that, let me suggest that you consider keeping the data in Access. While Excel is an excellent tool for storing your data, it allows for sloppy data entry. Access keeps things tighter. There’s a lot to this decision. If you’re thinking it over, read my previous blog on Access vs Excel.
Assuming you decide you’re going to stick with Excel, let’s see how you’ll get your data back there.
Microsoft Access has an Export wizard that makes this an easy task. Select your table, and then check out the “External Data” ribbon. Here’s mine, but yours may vary if you’re using a different version of Access:
As you can see, I’ve selected the “Excel Vendor List” table, which is the one that contains my cleansed Excel data. On the External Data ribbon, in the Export group, there’s an Excel button. When I click that, the options are pretty straight forward:
Use the Browse button to select the location for the exported file.
Note: do not overwrite your old Excel file, unless you’ve got a backup copy! It’s always possible that something could go wrong, and you’ll end up with a corrupt file. So, keep the original, just in case.
Formatting and Layout
Let’s talk about the “Export data with formatting and layout” option. This won’t make much difference on your Excel file. Here are the two side-by-side for my file:
As you can see, when I checked the “Export data with formatting and layout” option, it added a little formatting to my headings, and resized my columns. It’s a minor difference. There’s not a heck of a lot of formatting you can perform on an Access table, so this option isn’t going to do much for you.
I’d take one additional step with this exported data. My original Excel sheet that I imported had formatting and validation rules on it. I don’t want to recreate those features, so I’ll copy and paste the data from my newly-created Excel file into my original file. Once again, back up your original before you do this! I can’t stress that enough.
That concludes my series on using Microsoft Access to clean your Excel data. In my next Microsoft Access blog, I’m going to start a new series that focuses on linking Microsoft Access to data in other databases, including Excel, SQL Server, and other sources. To be notified when the I post entries in that series, subscribe using the form below.
Got a Microsoft Access topic you’d like to see me cover in a future blog? Use the comment form below to submit your suggestions.
As always, reach out if you need help with your Access, Excel or VBA applications.
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 firstname.lastname@example.org.
#northportsolutions #learningneverstops #microsoftaccess #msaccess #database #dataanalysis #smallbusiness #excel #microsoftexcel #msexcel
© 2022 North Port Solutions, LLC. All right reserved.