Using SQL to transform Excel data

[cyiangou]

I'd like to share the results of an experiment that I started about a week ago, inspired by this forum.

As a programmer equally at home with spreadsheets and databases, I see many questions here that are better suited to a database solution than a spreadsheet one. However, this IS a spreadsheet forum, and part of the fun is the challenge of seeing how we can solve problems 'that shouldnt be solvable in a spreadsheet'. And people solve these all the time - brilliantly. But many a time it feels as if these solutions are too 'heavy-duty', labour-intensive and usually only suitable for once-off use.

Excel is first and foremost, a superlative data transformation tool, and yet incongruously, it lacks easy access to the greatest data transformation technique of all: SQL.

So this is what I set out to do:

To enable SQL as an additional technique to transform Excel data. Additionally, to make this usage as easy and accessible as possible.

To achieve this, I have encapsulated and abstracted the technical aspects of opening connections, recordsets and processing, that is usually of little concern to a user's business needs, in a class. My aim is that the more complicated code contained in the class need never be seen or understood by a normal user; all they need to do is use it for their needs. For all this, the code contained in the class module turned out surprisingly small.

This is a small demo showing how you can make a SQL execution editor in 5 lines of code using the class. In this sample, you can change the SQL shown (if you know sql) to try it out further.
FileSQL.xlsm

All you need to do to use this component:

• Import the class module 'ExcelDataEngine.cls' into your workbook in the vba editor (or create a class module and paste it in).
• Mark certain tables of data in your workbook as SQL-able tables by naming the top-left corner of the range as TableBaseName_Table.
• Write an SQL statement to query this/these table(s). (Need to refer to table in SQL as '{RangeName_Table} AS AliasName').
• Set up some minimal vba code using the class, execute the statement and post the resulting data to a blank sheet in the workbook.

That's all.

Consider the following 'simple' problem.

I have a directory listing of files in a folder as shown below:

FileListing.jpg

I am required to do the following:

1. Get a listing of each file extension type, showing a count and total size of each group.
2. Show a matrix of file activity by day of week, and hour of day (I'll show this in a subsequent post)

Constraints:

• The range of possible file extensions is unknown, they must therefore be determined dynamically.
• Similarly, the date range is unknown and must be determined dynamically.

Those of you that can solve this kind of problem know:

• It's not for a beginner user of Excel.
• If it's a formula solution, it's going to get complex.
• The solution you come up is probably highly-tailored to the specific problem, and of little use to another.

In contrast, this is the complete SQL statement that achieves requirement 1 above, using my class:
(Pfah. Forum uploader sees SQL as an 'SQL injection attack'). Here's the bitmap version then.

sqlsnip.jpg

To execute it, this is all the vba code that you need to write:

Please Login or Register  to view this content.

Advantages of this method

• Uses no spreadsheet overhead; should be minimal on memory in general (vba array usage will still be a limitation)
• No need to copy formulas further down than is necessary to cater for unknown record counts
• Uses no formulas, and minimal vba data processing
• It should be very fast for large data (I haven't tested)
• Generic, the class can be re-used for many different problems

I would very much like your comments, suggestions, and especially whether you might find such a library useful. I will occasionally use this library to solve suitable problems on the forum. If anyone else wishes to use it, you're welcome. All I ask is that the first time you use it, you 'pay' for it by giving me a rep point. After that, it would be good if you mentioned that you are using my library to solve the request. Also please do not delete my credit notice in the code. Here is the source code for the class (also contained in example file above).

ExcelDataEngine.txt
(I had to rename this as .txt otherwise the Forum uploader considers it an 'Invalid' file. After downloading, please rename it back to .cls)

[Pauleyb]

Rep +1. Thank you for helping out the community with some helpful ideas and code. I love SQL and to generalize it into some of the more daunting Excel problems is a great idea.

Pauley

[cyiangou]

Thank you, PauleyB!

[Kyle123]

It's quite a neat solution can you make it work with dao? Querying the open workbook with ado causes a well documented (and still unfixed despite dao being pretty much dead) memory leak, that's only resolved when excel is restarted.

SQL in excel is something that I always think sounds useful, but ultimately can usually solve in an easier and faster way using a pivot table.

That said, this is still handy

[Kyle123]

You may also be interested in http://www.excelforum.com/excel-prog...ide-excel.html

[cyiangou]

Didn't know about the memory leak issue, thanks. I can surely make a DAO version; I still love DAO above all others.

There will still be ample cases where an SQL solution would be better than pivot. Eg. Cleaning downloaded data to use in a pivot.

[cyiangou]

Thanks for that link. Very similar, but different enough that it uses SQLite.

I like the ADO / DAO method because you can use vba functions in the queries.

[Kyle123]

Out of interest, did you find that reading the record set into an array to write to the sheet was more efficient than using copyfromrecordset?

Yes the link's a different different angle and does things in a different way, but it's also very neat.

[cyiangou]

"CopyFromRecordset". Jeez, things that I've known, used, forgotten about, and then re-invented. I have a long list of these. Will check it out (again).

I was in fact thinking of ways to write out the recordset in 'pages' to avoid holding the whole array at once.

[Kyle123]

Damn, I thought you'd gotten a quicker way to write a record set to a range

[cyiangou]

Challenge accepted.

[cyiangou]

Kyle, some updates:

• From my very first google hits on the DAO option, it looks like there's already problems regarding age & version compatibility, so I won't follow that option any further.
• CopyFromRecordset. Not only did I once know this, but when I googled it now, all the links were already visited! Tsk. Ok, tried it now but it doesn't allow me to include R1C1 formula definitions as a field value, and have the sheet accept it as such when populating (eg. '=RC[-1]/RC[-2]' AS Hsize FROM {File_Table} to create additional, dynamic calculation fields). So efficiency wasn't the only reason I chose the current method. Paging option still looking viable and necessary.

[cyiangou]

@Kyle,

Here's a good example, I think. Show me a better way (or even a good way) of solving this one with normal Excel methods.

I have 11 reps, selling 5 products, across 3 regions.

• Each rep is dedicated to a region.
• There is a standard, landed price for each of the 5 products.
• There is a transport surcharge applicable to each region for transportation from the landing port. ie. Region with lowest surcharge is nearest the port.
• Profit margin above landed+transport cost is 25%.

Create a master pricelist showing the product price for each product, for each rep.

Using my class module:
SQL_relational.xlsm

• One SQL statement (see image). Made even easier using MSAccess GUI.
• This much vba:

Please Login or Register  to view this content.

relational.JPG