Consuming an API and transforming JSON payload to Excel (xlsx) in Mulesoft
Introduction
In this post, I walk through step by step how to consume an API and transform its JSON response into an excel file using Mulesoft as shown in the diagram of Figure 1. For this task, the project has been developed using AnyPoint Studio.
The API to be used in this project is the Pokemon API, which is a simple GET HTTP request that takes a pokemon name as input and returns a JSON string with information about this pokemon such as abilities, height, weight, types among other interesting facts about your favorite pokemon.
All the Pokémon data you’ll ever need in one place, easily accessible through a modern RESTful API. (PokeAPI, 2021)
Here you can see the Pokemon API for more information: Link. The purpose of this post is to showcase the steps taken in AnyPoint Studio to archive data transformation in Mulesoft.
If you want to download this project, here is the GitHub repo: Link
Understanding the PokeAPI data
This API is pretty simple to consume, just call the URL with your favorite pokemon to get JSON results. Figure 2 shows the data returned by the API when I choose the pokemon: Scyther. The final output of this exercise would turn this input data into an Excel file.
PokeAPI:
https://pokeapi.co/api/v2/pokemon/scyther
JSON to XLSX (Excel) in Mulesoft AnyPoint Studio
Follow these steps to transform your pokemon data.
1 — Create a mule Project (File->New->Mule Project) [Figure 3]
*Add the project name: pokemonToExcel
*Use Mule 4 as Runtime
*Leave other options as default
*Hit Finish.
2 — From the Mule Pallet add a Listener [Figure 4]
*On the search bar type listener, and drag the HTTP listener to AnyPoint Studio.
*Hit the + button on Connector configuration.
*Leave the configurations as default. (Make sure your 8081 port is available, or change it here).
*Test the connection here to make sure your listener works properly.
In the Listener configuration add the path that your API will use and specify the variable {pokemon} which later will be sent to PokeAPI as Input (Figure 5).
3 — From the Mule Palette ‘Set a Variable’ (Figure 6)
*Using Core>Set Variable, name it pokemonName and set the value to the URL variable from the previous step, using this script:
#[attributes.uriParams.pokemon]
4 — From the Mule Palette, get an ‘HTTP>Request’; drag the object right next to the set variable node. In this step, the PokeAPI is being invoked and consumed.
*Click the + button on Basic Setting, configurations
*Add the PokeAPI host and Base path as follows (Figure 7):
In the HTTP request, leave the method like GET, and add the URL variable created on step 3 (vars.pokemonName) into the path field as shown in Figure 8.
5 — From Mule Palette add a ‘Transform Message’ node just right next to the HTTP request just created.
*On the Input section, hit ‘Define metadata’ (Figure 9).
Follow these instructions to define metadata: (Figure 10)
*On Select metadata type window, click on the + Add button.
*Name it responseStructure.
*Change the type to JSON.
*Change Schema to Example, and save the PokeAPI response into a JSON file on your local drive, call it something like responseExample.JSON and select this file here.
On the output data side, select Define metadata (Figure 11).
On the Select metadata type window, click on the + Add button. (Figure 12)
*Name it excelStructure. Then Select Excel in type.
*Create an excel tab for each object that the API response has. For the root results, I have created a tab called root. For each other object from the response, I have created the following tabs: abilities, forms, game_indices, held_items, moves, past_types, species, sprites, stats, and types.
Having the Input and Output defined, the data transformation takes place. Select the attributes from the input that matches the metadata on the output side. The Output represents the excel file tabs created in the previous step.
TIP: *When an Array<Object> gets matched with an excel tab, make sure to flatten out the array. After dragging and matching the objects, (Figure 13) right-click on the output object and hit, Apply Transformation -> flatten(…)
TIP: Also, for the matching objects which are just: object data types, make sure to make them an array, wrapping them on brackets [] like Figure 14. Doing this the DataWeave compiler will process it as an array of one element which represents a row in the spreadsheet.
The correlation among all the JSON payload and the Excel output is displayed in Figure 15.
The Dataweave script for this transformation is being displayed below in Figure 16.
%dw 2.0
%Data weave script from Transform Message
%JEGFoutput application/xlsx---{root: [{base_experience: payload.base_experience as String,height: payload.height as String,id: payload.id as String,is_default: payload.is_default as String,location_area_encounters: payload.location_area_encounters,name: payload.name,order: payload.order as String,weight: payload.weight as String}],abilities: flatten ( payload.abilities map ( ability , indexOfAbility ) -> ability ),forms: flatten ( payload.forms map ( form , indexOfForm ) -> form),game_indices: flatten ( payload.game_indices map ( gameindex , indexOfGameindex ) -> gameindex),held_items: flatten ( payload.held_items map ( helditem , indexOfHelditem ) -> helditem),moves: flatten ( payload.moves map ( move , indexOfMove ) -> move),past_types: flatten ( payload.past_types map ( pasttype , indexOfPasttype ) -> pasttype),species: [payload.species],sprites: [payload.sprites],stats: payload.stats map ( stat , indexOfStat ) -> stat,types: payload.types map ( vtype , indexOfVtype ) -> vtype}
6 — From Mule Palette add a ‘File> Write’ node, to export the excel file directly to a specific path.
*On the Basic Setting section, click on the plus + button, and leave the File Config as default (Figure 17).
Then add on the write node configurations, the path, and the extension of the output file. Specify the path and output.xlsx (Excel) file name with extension. As shown in Figure 18.
7 — Finally, run the project.
On Any Point Studio, right-click anywhere on the project and click “Run project pokemontoexcel” (Figure 19) this will start the local servers to execute the API.
When the local servers are up and running you will see in the console a message like in Figure 20. This means that the API is running correctly.
To test the API, in your rest client, or your browser use this link: (You can change Scyther for any other pokemon you like, such as Pikachu).
http://localhost:8081/myapi/scytherThe response should be a 200 code status, with the file as payload as shown in Figure 21.
Finally check the file created (Figure 22) on the path specified in step 6. The data from the PokeAPI should be displayed by objects in each tab of the excel file.
Reference
Mulesoft Excel format. Excel Format | MuleSoft Documentation. (n.d.). Retrieved December 19, 2021, from https://docs.mulesoft.com/dataweave/2.4/dataweave-formats-excel
Mulesoft developer Certification Exam — level 1 (mule 4). MuleSoft Developer Certification Exam — Level 1 (Mule 4). (n.d.). Retrieved December 19, 2021, from https://training.mulesoft.com/certification/developer-mule4-level1
PokéAPI Documentation. PokéAPI. (n.d.). Retrieved November 20, 2021, from https://pokeapi.co/docs/v2.
