Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba
Last post I encouraged readers to approach updating databases with care and professionalism. Some may wonder why then, use Excel at all if updates require the level of care in traditional programming. The answer is that Excel is a standard means for exchanging data.
As you know, the world is moving to software as a service, cloud computing, and disparate data systems. In other words, much of the data you need to run your business is, or soon will be, on the web. This data is usually in need of cleansing, transformation, denormalization, and integration with your host systems. The good news is just about every website that offers any kind of export, supports Excel, CSV, or XML formats – all of which Excel handles nicely. And by using the functionality available in Excel, you can shorten the chain of objects required to cleans and move data from website to your own server to just one object – no temporary files, no server side programs, no triggers, scheduled events, system administrator involvement – just one Excel spreadsheet.
A real world example is just now being realized in the trucking industry. The government has mandated a new means for scoring drivers and carriers. The new scoring system is called CSA-2010 and is intended to identify which drivers are likely to be involved in an accident. The new scoring system will (in my opinion) determine which drivers continue to work and which carriers continue to do business. The data for CSA-2010 comes from tens of thousands of police officers in the field from various jurisdictions who, when they pull over a trucker for a violation or at a scale, record an inspection result and upload it through a series of systems that are ultimately collected at the federal level. Carriers need this information integrated with their driver performance systems so they can take action to remediate at risk drivers and so they can improve their chances for earning business in an already cut-throat market. The problem is, with so many sources, the data often isn’t clean.
No problem. The data can be exported through XML to Excel where it is extremely easy to find inconsistencies, fix, cross check with other systems (using the data extract methods in the prior posts), and, finally, uploaded onto servers where it becomes part of the overall driver performance system.
The basic process is to read through an Excel range, pluck out values, match them to column/field names, and execute a simple SQL Insert (or Update, or even Delete) statement like:
INSERT INTO table_name (column1, column2, column3,...) VALUES (Cell(lRow, 1), Cell(lRow, 2), Cell(lRow, 3),...)
Now constructing such Insert (or Update) statements can be tedious so as we have in the past, we will be using the “Fields” table and code to automate most of that for us.