06 – Excel Importing – Using Excel As A Way To Obtain Data

In the previous few posts, we have covered different ways to retrieve information from Excel and get it into Access. In this post, we will be discussing some of the pros and cons of using Excel to cart your data around.

Excel As A Database

A series on Excel importing wouldn’t be a series without a look at the relationship that Excel has with the database community. It may or may not surprise you to read this but Excel is (probably) the most widely used database technology in the world. Although not technically a database, it does perform the most fundamental role that a database should perform which is that it can store data. Not only can Excel store data, it can store it in a 2-dimensional grid where we are able to cross-reference information we are looking for. The fact that Excel has almost no security, encourages denormalised table design and can’t run SQL queries is irrelevant; Excel is what a lot of people are used to and trying to move them away from it to a better, more robust database solution is one of the biggest challenges that a developer can face.

Jason L Baptiste wrote a famous blog post on the subject and has received quite a lot of attention from interested parties. Forbes have made their own contribution to the debate and it must be said, it makes for quite interesting (and scary) reading.

What we do know, however, is that users are comfortable with Excel and fighting against that trend might be futile at best. So, where does that leave us Access users/developers.

Convincing An Excel User To Switch To Access

Convincing a life-long Excel user to switch to Access can be a daunting task. If you study computing at university level, change management is a concept that is covered in depth as getting users to shift from one system to another can cause a lot of frustration and it is normally the developer who bears the brunt of this frustration.

There are, however, some resources you can use to help the user make the “right choice”. Microsoft have a neat little test you can take which tells you whether you require Excel or Access for a particular scenario. It is cleverly framed as it pushes and nudges the user to make the right choice when it comes to Excel or Access. Stephen Brower has a YouTube video explaining the difference and, of course, we have our own video highlighting where Access is more appropriate than Excel.

Ultimately, the ability to move a user from Excel to Access will largely depend upon your level of influence with your client/boss/other. But building relationships and getting trust are really important for developers and shouldn’t be taken lightly as subjects.

Passing Data With Excel

As it stands, a lot of people are going to be using Excel for years to come. But this isn’t necessarily all bad. It does, at least, give us a convenient way to move data between locations. Excel may be the most widely used database solution in the world but it can’t be far off being the most widely used way of distributing lists of data. It is very common for companies to receive information through emailed Excel sheets or, as is becoming more and more popular now, dropboxes. However the Excel sheet is sent, it is still an Excel sheet!

The Pros of Excel Data Distribution…

We have coined a phrase! Excel Data Distribution (you heard it hear first) does have some advantages over other forms of data distribution. Let’s see what some of them are:

• What else will you use? If not Excel then what? XML? Actually, XML is getting more and more popular as  means of distributing data but isn’t as “simple” as Excel. You could, theoretically, create an Excel sheet, convert it to XML and then send it as an email but where is the real advantage here? You are just making extra work for yourself and there are no greater security benefits.
• Lots of people have Excel. Excel is very popular and there is a version in almost every office in the land (or so Microsoft would have you believe). The ubiquitiousness of Excel is a definite plus here as for another data distribution service, we would require some kind of software to render the data.
• We get Excel! At its most basic level, Excel really is simple to use. Open it up and you have somewhere you can list your shopping, add a couple of numbers or make boxes different colours. No fuss, no hassle!

It also has some cons…

…And The Cons

• Security. This is surely the biggest con. There is no security on a non-password protected workbook and (if we are being honest) very little when a workbook is password protected (it is easy to find software that will unlock a Microsoft Excel password). Excel files can also easily be copied which means you really do have little security as clones could be floating about your office, under no-ones control.
• Formatting. Data is not always optimally formatted when Excel is used. This is a definite issue when importing to Access as Access is more than a little picky as to what data it will and won’t accept.
• Versioning. Your copy of Access may not accept the version of Excel that the data has been sent in if it is pre-2007. In reality, this is a minor issue as the user who sent the Excel sheet can Save As the Excel file in a compatible format.

Conclusion

Excel is a very poor substitute for a real database technology and lacks many standard features that help to ensure data is accurate and up-to-date.  Yet it is one the most popular database  solutions in the world for its familiarity and ease of use. Although we would like to convince all those erroneous Exceleers to shift their allegiances to Access, in reality we may just be whistling in the wind. The end result is that we often find ourselves required to import data from an Excel sheet that may or may not be formatted correctly.

At least Access can natively handle this scenario (and if not, a little VBA goes a long way)!