Excel's much better data import features

We’re in the middle of a series on getting live internet data directly into Excel and it’s been a pleasant surprise.  Currency Exchange Rates and Stock/Index data can automatically update your Excel worksheets.

Microsoft has been improving the data connection features in Excel, especially Excel for Windows.  It’s happened gradually and not always communicated well to customers.  And there’s been bewildering name changes, so you’re forgiven for not following developments.

Promoting these new features isn’t helped by eye-glazing descriptions like this “… enhances self-service business intelligence (BI) for Excel with an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured,”

The ‘bottom line’, as they say, is this:  you now have a lot more powerful, yet easier, options for getting data from other sources, arranging it and adding to Excel. From there you can work with the data in a standard worksheet, PivotTable, PivotChart etc.  The data links can be ‘one-off’ or updateable.

The range of data sources is much improved.  It’s not just SQL Server and primitive web page scraping anymore.

The management of the incoming feed is soooo much better.  You can filter the data, change values to suit special cases, merge feeds and you can resort.

Sorting alone is a big deal.  VLOOKUP() needs the source list to be sorted but there was no simple way to ensure a list was always sorted after a refresh.  It’s been a long time coming but finally we have it. David Goodmanson explained VLOOKUP() for Office-Watch.com back in 2009.

You can import XML feeds and also the very common JSON format as well.  JSON is another data transfer format which comes as a plain text file (like XML).  There was VBA code to deal with JSON data but now Excel can handle it directly.

Data Explorer begat PowerQuery begat Get & Transform

Originally these features were called ‘Data Explorer’ but the marketing team had a long lunch and came up with the name ‘Power Query’.

Power Query is available for:

Excel 2010, but only for Volume licences of Office 2010 Professional Plus with Software Assurance  download.

Excel 2013 (Professional Plus, Office 365 ProPlus, Excel 2013 Standalone) get all Power Query features download.

Excel 2013 (all other Office bundles) get PowerQuery but without the connectors for some databases, mostly high-end ones. ( Corporate Power BI Data Catalog, Azure-based data sources, Active Directory, HDFS, SharePoint Lists, Oracle, DB2, MySQL, PostgreSQL, Sybase, Teradata, Exchange, Dynamics CRM, SAP BusinessObjects, Salesforce.)  download.

Now, in Excel 2016 for Windows they are integrated into the main program and yet another name change to ‘Get & Transform’ on the Data tab.

The ‘From File’ name is a little misleading.  You might think that means the data file has to be saved on a local drive or network share. The File | Open dialog box would confirm that suspicion but it’s wrong.  You can enter a web url into these options to grab details from the Internet (no need to download first, then load into Excel).

Good, old CSV files (comma delimited text) can be imported as can Microsoft’s preferred XML.  The new arrival is JSON, another plain text data format much loved of web programmers.

If you’ve thought that importing data into Excel was too hard or have VBA code to make it happen, check out PowerQuery/Get & Transform.  Chances are that Microsoft has caught up with your needs with a simpler and faster solution.