Converting a Uniform Arrayed JSON Response to CSV Format in Postman / Blogs / Perficient

The audience for the blog:

Before we get into the topic, let’s first just understand the ideal audience and purpose for the engagement:

1. If you want to convert an API response that is in array format and has a consistent structure into CSV format to retrieve it in a console log, this blog is for you.
2. The only limitation is that your array must be uniform.

Introduction:

Let’s first understand the fundamental Postman architecture needed before we go on to the actual topic.

After installing the Postman software, we must use Postman to build a new collection.

Click the “+” icon under the Collection tab on the left to start a new collection.

Thus, a new collection will be produced. Give your collection a new name of your choosing.

Now simply hover over Collection to reveal the more actions tab. Select it, then select the add request option to add a new request.

Open the request, choose the “GET” method, then add the request URL.

You have the option of renaming your request.

Click the send button on the right side of the request after inputting the request URL.

Below the request is the response body. JSON format is the default for the returned response.

Now, You Might Ask, What is JSON Format?

• JSON is an abbreviation for JavaScript Object Notation. It is typically utilized for data transmission between web apps and servers.
• The JSON data format is a “key”: “value” pair.
• Arrays and objects comprise most of the two structured kinds found in JSON.

Here is an Illustration of a JSON Structure:

In the request tab, there are two editors. The first is a pre-request editor, while the second is a test editor.

Here is Where the Topic’s Most Crucial Part Begins

1: Pre-request Script Editor: This section contains scripts that will run before the request. We’ll build scripts to check a few conditions that must be satisfied before executing the request.

2: Tests Editor: These scripts run in response to the request. Tests are crucial since they establish checkpoints to ensure the response status is good, the retrieved data is accurate, and other tests.

We are now prepared to implement the code to convert the necessary iterating data from the entire response body from JSON to CSV and output it in the console log.

The Code that must be entered into the tests editor is shown below:

var jsonData = pm.response.json().data // Here “.data” is used because in response of request, “data” is the tag having arrayed data and we need only arrayed data for conversion purpose.
var replacer = (key, value) => value === null ? '' : value // specify how you want to handle null values here
var heads = Object.keys(jsonData[0]) 
var csv = [
  heads.join(','), // header row first
  ...jsonData.map(row => heads.map(fieldName => JSON.stringify(row[fieldName], replacer)).join(','))
].join('\r\n')

console.log(csv);

Sample request used:

https://reqres.in/api/users?page=null

Response:

{
    "page": 1,
    "per_page": 6,
    "total": 12,
    "total_pages": 2,
    "data": [
        {
            "id": 1,
            "email": "george.bluth@reqres.in",
            "first_name": "George",
            "last_name": "Bluth",
            "avatar": "https://reqres.in/img/faces/1-image.jpg"
        },
        {
            "id": 2,
            "email": "janet.weaver@reqres.in",
            "first_name": "Janet",
            "last_name": "Weaver",
            "avatar": "https://reqres.in/img/faces/2-image.jpg"
        },
        {
            "id": 3,
            "email": "emma.wong@reqres.in",
            "first_name": "Emma",
            "last_name": "Wong",
            "avatar": "https://reqres.in/img/faces/3-image.jpg"
        },
        {
            "id": 4,
            "email": "eve.holt@reqres.in",
            "first_name": "Eve",
            "last_name": "Holt",
            "avatar": "https://reqres.in/img/faces/4-image.jpg"
        },
        {
            "id": 5,
            "email": "charles.morris@reqres.in",
            "first_name": "Charles",
            "last_name": "Morris",
            "avatar": "https://reqres.in/img/faces/5-image.jpg"
        },
        {
            "id": 6,
            "email": "tracey.ramos@reqres.in",
            "first_name": "Tracey",
            "last_name": "Ramos",
            "avatar": "https://reqres.in/img/faces/6-image.jpg"
        }
    ],
    "support": {
        "url": "https://reqres.in/#support-heading",
        "text": "To keep ReqRes free, contributions towards server costs are appreciated!"
    }
}

Explanation of The Above Code:

Var jsonData = pm.response.json().data

In the above line, jsonData is a local variable that stores the complete response inside the “data” tag, an array object in response.
So while using this code for your response conversion, you must replace the “data” tag name as per your response.

So considering the above example present in the screenshot, jsonData will contain a response current inside the “data” tag, i.e., “id,”” email,” “first_name,” “last_name,” and “avatar.”

It will not contain “page,””per_page,” “total,” or “total_pages” values because it is out of the scope of the “data” tag.

A pm is a postman object that is predefined. Therefore, to retrieve response data or add any type of assertion to validate response data, we must utilize pm as an object when using any method or function.

In scripts added to the Tests Editor, the pm. Response object gives access to the data returned in the response for the current request.

Of the various attributes and methods offered by the pm.response object, pm.response.json() is one of them. With this function, we can obtain the response body in json format and put it in a variable, and its type will be an object.

Replacer:

All object attributes that are undefined will be excluded by JSON.stringify. When removing data from the server explicitly, there are times when we also wish to remove it from the database. Dropping undefined can lead to issues in this situation.

In this case, a second parameter called the replacer function is used. The current key and the value being strung together are the two inputs for the function. For example, if some value is undefined, this enables us to replace undefined with some other value.

Object.keys():

The array is returned by Object. Keys () contain strings corresponding to the enumerable attributes that may be found directly on the object. The order of the properties is the same as what would result from manually looping over the object’s properties.

Join:

The join() method returns an array as a string. The initial array is unaffected by it. The join method allows any separator to be supplied. Commas are standard (,).

Map:

A Map holds key-value pairs where the keys can be any datatype. A Map remembers the original insertion order of the keys.

Code Output:

Inside the console log in Postman, we’ll get the requested data in CSV format.

“In Conclusion, using the above methods, we can easily convert uniform arrayed data in our API’s response into a CSV format. This will enhance our code readability and usability.

This is all about converting uniform arrayed data into CSV format in Postman. I hope you enjoyed reading this post and found it to be helpful.

“Keep Coding”