xll_inet

This is still a work in progress, but I’ve been eating my own dog food and liking it: Win Inet for Excel. Let me know if you find something that breaks and open an issue on GitHub so I can fix it.

This add-in lets you grab a URL and put the characters returned in memory. Parse them as XML or HTML using the battle-tested libxml2 library used by etree in Python. Extract anything you want by manually traversing nodes or use XPath to zero in on exactly what you are after. It also has functions for parsing CSV and JSON into an Excel range.

JSON maps quite neatly to Excel. A JSON object is a two row range with keys in the first row and values in the second. A JSON array is a one row range of values. JSON primitive types number, string, boolean, and null correspond to Excel types xltypeNum, xltypeStr, xltypeBool, and xltypeErr with val.err = xlerrNull. Excel needs a little help to play nice with ranges that are recursive. You can use HLOOKUP to return a primitive type using the key and returning the second row but if the value is an object or array you will get a #VALUE!. Use JSON.VALUE(val, key) instead. The key can be an array of keys to recurse through or a jq-like dotted index. JSON arrays are indexed by 0-based numbers. Unlike jq, they should not be enclosed in square brackets.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s