How to Convert JSON to Microsoft Excel

Introduction

In this small example FME reads from a website that has a JSON feed reporting air quality values. We can rename some of the attributes and delete the ones not needed for the Excel output. The source data is for the website for the World Quality Index project that has a JSON API - http://fme.ly/air. The data looks like this:

Video

Note: This video was recorded using FME 2016, the interface might vary slightly.

Step-by-step Instructions

1. Start FME Workbench

If it isn’t open already, launch FME Workbench.

2. Create a New Workspace

On the Start page, select the New option under Create Workspace.

3. Add a JSON Reader

Start typing “JSON” without anything selected on the canvas, then select the JSON format from the list of Readers by double-clicking or by using the arrow keys and the Enter key to add it to the canvas.

The source data is for the website for the World Quality Index project that has a JSON API - http://fme.ly/air

Copy the above link and paste it in the Dataset section.

When you add a JSON reader to FME Workbench, you need to instruct it on how to interpret the JSON. This is done using the Schema Scan Mode reader parameter. Select the Schema Scan Mode: Auto. The elements inside the JSON will be flattened into attributes. These are marked in the Overview image above.

NOTE - There may be occasions when Auto Schema Scan Mode may not be appropriate for the JSON data that you are reading in, I would recommend checking out our JSON Reader Configuration for anyone who may have more complex JSON Data. Reading JSON article.

Run the workspace and inspect the data.

4. Add a VertexCreator

Add a VertexCreator to the canvas by typing “VertexCreator” to bring up the list of FME Transformers in the Quick Add Search. Select the VertexCreator from the list of Transformers by double-clicking or by using the arrow keys and the Enter key to add it to the canvas.

Once the transformer has been added to the canvas, connect the JSON Reader to the VertexCreator.

The VertexCreator is used to extract X and Y coordinates from the JSON dataset. In this case, we will open the VertexCreator parameters and specify that the X value will read the “lon” JSON attribute and the Y value will read the “lat” JSON Attribute.

5. Add an AttributeManager

Add an AttributeManager to the canvas by typing “AttributeManager” to bring up the list of FME Transformers in the Quick Add Search. Select the AttributeManager from the list of Transformers by double-clicking or by using the arrow keys and the Enter key to add it to the canvas.

In the example workspace the AttributeManager has been used to remove and rename attributes as appropriate.

The AttributeManager is also being used to validate the 'aqi' value - converting a value of '-' to a <null> so that it can be used in arithmetic expressions or statistics calculations. Click the dropdown for the “AirQualityIndex” Attribute Value and select Conditional Statement. Set the Conditional Statement to the following.

If aqi = '-' then AirQualityIndex = <null> else AirQualityIndex = aqi

6. Add an Excel Writer

Similar to adding a reader, type “Excel” then select the Microsoft Excel Writer format from the list of Writers.

Click on the dataset ellipsis to specify where to save your data and name the file: AirQuality (for example, C:\Users\Documents\FME\Output\AirQuality.xlsx)

Set the Definition to Automatic to ensure that all attributes created or renamed are written to the Excel file.

When you add the Excel writer to your workspace, choose Sheet Definition: Automatic... This means the columns in the Excel writer will be defined from the attributes created in the workspace - in this case, AttributeManager. You can see that any attributes renamed or removed in the AttributeManager are automatically reflected in the Excel writer. If you want to fine-tune the attribute definitions, you can switch the Attribute Definition to Manual.

For more information on working with Excel, see Tutorial: Getting Started with Excel.

Results

Excel Output:

Data Attribution

World Air Quality Index project, gathers Air Quality data from EPA organizations from around the world.