Say what you will about Microsoft. The fact of the matter is that they have long provided some of the most effective tools for small business management. Microsoft Excel and Microsoft Access are both often used as database tools, though only one of them is designed that way.
Microsoft Access is the database tool, and is much maligned in the IT world. That makes sense for mid-sized or large organizations. There are better tools for managing large volumes of mission-critical data, like Microsoft SQL Server or Oracle.
But for small businesses, Microsoft Access provides a lower entry point, allowing you to get started with databases with little investment in time, dollars and training.
If you’re like most small businesses, you’ve probably got at least some of your data stored in Microsoft Excel. No matter how good your software is, you probably find yourself supplementing it with Microsoft Excel for reporting, data analysis, or for sharing with others.
If you’ve also got an Access database, it would be great if you could take the data you’ve worked with in Excel and import it into your Access database. Microsoft has thought of that, of course, and its actually quite easy to do. At the same time, though, its fraught with issues. When it works, you can seamlessly integrate data between the two tools. But when it doesn’t work (which, in my experience, is often), it can be endlessly frustrating to figure out what’s wrong and how to fix it.
I’m planning to write a series of blog posts on this topic, but for today, we’re going to start with a few basic rules that will greatly increase your chances of success.
Let’s consider some sample data, and see what happens when we import it into Access:
In this example, we see the data structured using techniques that are common to Excel. The author has carefully designed this document to be easy on the eyes. There's a clear heading at the top, identifying the type of data we're looking at. The user can quickly locate the vendors from each state, by scanning column A. On rows 5 and 6, it’s apparent to the reader that “DEF, Inc.” has two contacts.
This works great in Excel, but when we try to import it into Access, we’re going to run into a host of issues. That’s because we formatted the data for human consumption, not database consumption. For the Access database to be able to process this data, we’re going to have to make it less pretty, but more functional.
The first hint of trouble will come when you preview the data in the Access Import Spreadsheet Wizard:
If you haven’t done this before, you might not spot the problem yet. That little check box at the top – the one that says “First Row Contains Column Headings” – is your clue. Access needs column headings. If I proceed without them, this is what I end up with:
Check out the column headings here. I’ve got “Field1”, “Field2”, etc. These headings are not great, because they are not descriptive. As you work with this data in queries, forms and reports, you’ll constantly be trying to remember which data is in Field1 vs Field 2 vs Field 3, etc.
Let’s look past that for a minute. Notice that the first row of data contains only “VENDOR LIST”. The second row is blank. The third row, where we see “Vendor Name” and “Contact”, is from our heading row in Excel. These should be our column headings!
So, what’s going on here?
As previously stated, the spreadsheet is set up for humans, not Microsoft Access. We’re going to have to reformat the Excel data into a structure that Access can understand.
To get your thought processes moving in the right direction, here’s a little introduction to Access tables, as compare to Excel.
Excel is a huge mass of cells, with thousands of columns and rows. You can use these cells, columns and rows however you see fit. It’s very loose, allowing you lots of creativity. Think of it as a blank art canvas – you can choose what paint colors to use and what subject to paint.
Access is much more structured. This is both good and bad – it allows you less creativity, but it protects the integrity of your data. In order to import your data into Access, you need to make the Excel version more structured, so that it more closely matches Access’ expectations. Going back to the art analogy, this is more like a paint-by-numbers, where the subject is pre-defined, and the colors are suggested to you. You can still be somewhat creative, but thanks to the pre-defined structure, you're going to end up with an identifiable subject, even if you've got no skills.
To prepare your Excel data for importing, consider these three basic rules for structuring your data:
There can only be ONE row of headings, and it has to be row 1.
There can’t be any blank rows in the data set.
Every row has to stand alone. Remember how “DEF, Inc” appeared on row 5 but not 6? That means that row 6 doesn't stand alone. If you ONLY had row 6 to look at, it would not provide enough info – you wouldn’t know which vendor the data belonged to.
There are exceptions to these rules, of course, but they provide a great starting point.
Let’s do the work to correct the sample Excel sheet, so that it adheres to these rules.
Rule #1 states that we must only have a single row of headings, and it has to be on row 1. I’ll need to delete my “Vendor List” heading, and also the blank row beneath it. My Excel sheet will then look like this:
An addendum to rule #1 is that every column that contains data must have a heading. Once again, it's not completely true, but for now, it's almost always a good idea. For this reason, I’ll add the heading in cell A1:
My data now complies with rule #1.
Rule #2 states that you can’t have any blank lines. In the above screen shot, I’ve still got a blank on row 6. It makes the file easier to read, but each Excel row is going to become a record in the Access table, so this would end up being a blank record. Blank records are useless in Access, and will, in fact, cause endless headaches in your queries, forms and reports. So get rid of them:
Finally, rule #3 states that each row must stand on its own. For row 2, that’s no problem. It looks like this when viewed alone:
However, rows 3, 4, 5 and 7 are all troublesome, because they are missing information. Here’s row 3, all by itself:
As you can see, the data is incomplete. Looking at this row only, there’s no way to identify the state.
Row 4 is worse, since the vendor name is also missing:
In order to correct these, we need to make sure every row has all the data it needs. I’ll fix my Excel file to look like this:
Now each row has a complete set of data. Our Excel sheet doesn’t look as good as when we started, but to Microsoft Access, this makes a lot more sense. When I import this version into Access, my table looks like this:
That’s better!
This data is now is good shape to be presented in an Access query, form or report.
As you can see, there are simple rules that you can follow in your Excel sheets that will decrease the problems you run into when importing to Access.
Next up: In a future blog, I’ll talk about how to merge your newly imported data into an existing Access table. Subscribe below to get notified when that post is live.
I hope this post helps you convince Excel and Access to play nicely together. If you run into questions, don’t hesitate to reach out via email.
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.
#northportsolutions #learningneverstops #accessdatabase #importexceldata
© North Port Solutions, LLC, 2022. All rights reserved.