SQL in Excel

I want to highlight one of features of Excel which is ‘Microsoft Query’. As far I as know this so called tool has over twenty years, but looks even older.

Why even bother?

Some time ago I was production data analyst, where one of my tasks were to pull data from SQL-based systems. But I had difficult time with IT department guys. They didn’t allow anyone to anything: no permission to access servers, no installing programs, no upgrades. Even I couldn’t have Excel upgraded to Professional version nor installed addons like PowerQuery. Their restrictions were so ridiculous that they refused requests from operational director (for example he simply want to have some image viewer). They were considering themselves as some kind of warlocks whose are the only ones who understands technology and everybody else can do nothing. So I ended up only with Excel in my tool-belt, and that vintage MS Query.

Pros & cons of MS Query

+ available in every version of excel

– can’t change connection with database after establishing

– can’t make relations one-to-many on on more than 2 tables

– network drives have to be mapped