Importing JSON to Excel

Q

What’s the best way to convert data I’ve obtained from websites in JSON format, into a format that I can import into Microsoft Excel?

A

JavaScript Object Notation (JSON) is an increasingly popular format for data obtained by querying online sources such as Twitter. As its name suggests, it’s primarily intended for use with JavaScript, and there are many free online services which will convert small amounts for you with ease.

Spreadsheets such as Excel and Numbers are traditional in the formats they import, working best with plain text separated by tabs or commas (CSV). Transforming JSON’s paired attributes and values into lines of tab- or comma-delimited text isn’t easy. The free statistical language R has a jsonlite package to import from JSON data, and an xlsx package to export to native Excel format. Even that isn’t simple though, as it tends to flatten the data into just two rows of the spreadsheet. If possible, look to using JavaScript to write CSV text direct, or obtain the data in a different format which you could readily convert into tab- or comma-delimited lines of text.

There are also two free JSON databases, Apache CouchDB and MongoDB, which may provide suitable intermediates to help store and convert data, and the format home page at json.org has a huge list of other support packages too. JSON editors like JSON Wizard reveal the structure of JSON data in the form of paired attributes and values, in a list.