How to download Json array values to excel

Ask the Experts and Postman Tips
1

I have created an API, and got the values as shown, I need to convert json arrays to excel. When i tried to convert am getting the value as “Object,Object” instead of original values. Or else how to remove header, as in this case “{
“Daily Milk Report”: [” . Please help me…

1
11366×768 97.3 KB

2
21366×768 72 KB

2

This is a tricky one for sure, not something I have had to deal with myself. However it looks like you need to iterate though each element in the response and insert that data into the spreadsheet.

Is the logic for this on the server end of our application or are you doing this export within your Postman requests? If it’s within Postman, maybe you could share the logic so we can better understand what its doing.

3

HI @venkateshm2280,

Not entirely clear how you’re getting your data out from the response into Excel, but if you’re just copying and pasting you could try something like

const res = pm.response.json();
const DMR = res && res[`Daily Milk Report`];
DMR && DMR.forEach(
   r=>console.log(Object.values(r).map( v=>`"${v}"`)
      .reduce((str,v)=>str + `,${v}`))
   )

This extracts each object from the Daily Milk Report array, and prints to console the values of all the elements(eg. Session_ID) with quotes. Then just select the text and paste into excel as csv data

In detail:

  • forEach(…) runs over each object in DMR array
  • Object.values(…) extracts the object’s values into an array
  • map(…) puts quotes around each value
  • reduce(…) joins them all up with commas
  • `…strings…` are template literals where ${…expression…} is replaced with its value

This code does assume that each object has the same elements in the same order. (Things would get a bit more complicated if you could not guarantee this. There are probably javascript solutions out there to more reliably convert JSON to csv for excel. )

4

Its pretty easy to just do this in Excel. Save the response from Postman as json and import it in Excel under the Data tab - Get data. See some guidance here Import data from external data sources and here How to Parse Custom JSON Data using Excel

1 Like