Auto updating to excel odbc dating the cheetah bottleneck


With ODBC, you can summarise, and select just the data you need, in an Excel workbook before importing it into SQL Server.

You can join data from different areas or worksheets.

Worksheets, or areas within worksheets, become tables.

There are some features missing, of course, but you can do joins between tables, filter rows to taste, do aggregations and some string manipulations.

It is a Once and Future technology, developed before its time, but now showing its value for processing large volumes of data, despite its quirks, poor documentation and lackluster support.

If you use the ODBC driver, then your Excel workbook becomes a little SQL-based relational database.

It is reasonably easy to insert data from Excel into SQL Server, or the reverse, from any other ODBC database to any other, using Power Shell.

The most important direction is from Excel to SQL Server, of course.

You can even get data from the result of a SQL Server SELECT statement into an Excel spreadsheet.

Phil Factor shows how, and warns of some of the pitfalls.

This means that you need pull far less data into SQL because you can do a lot of selection and pre-processing before the data gets anywhere near SQL server.

If, for example, you only need the total, count, and variance of a day’s readings, then why on earth would you want to import more than those aggregated figures?

It is quicker than automating Excel and you can do it without requiring a copy of Excel. The most important thing, though, is that you can aggregate before you send the data.

It is possible to do a lot of filtering and aggregation of data before it ever gets to SQL Server, since you can turn an existing Excel Workbook into a poor-man’s relational database, or even create one. I always feel slightly awkward in talking about ODBC.


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>