how to transform json data into table

Question

I have the below api call that returns data in JSON:

https://xama-was-service.herokuapp.com/api/socialone/databoards/10042?1=2019-02-01T00:00:00.000Z&2=test

That returns data as below:

[
    [
        {
            "Empid": 2326,
            "Empname": "Sam Smith",
            "AbsenceId": 12840,
            "Comment": "a001t000004FQgHAAW",
            "AbsenceStartDate": "2019-05-31T00:00:00.000Z",
            "AbsenceEndDate": "2019-05-31T00:00:00.000Z",
            "JobId": 400004,
            "AbsenceRequestId": ""
        },
        {
            "Empid": 3387,
            "Empname": "Joe bloggs",
            "AbsenceId": 12842,
            "Comment": "a001t000004FK67AAG",
            "AbsenceStartDate": "2019-06-06T00:00:00.000Z",
            "AbsenceEndDate": "2019-06-10T00:00:00.000Z",
            "JobId": 700004,
            "AbsenceRequestId": ""
       }
    ]
]

I would like to move this into excel and also power bi but i cannot transform it into a table?

Can anyone advise how to format the returned data into a table or what code to use on the original call to help with this?

ideal end product would be as below but not sure how to achieve?

Thanks.

Answer 1

This will parse your data into a comma delimited string (CSV).

You just need to separate each row element with a comma ,, and each row with a new line character \n. Excel knows this format, though sometimes you may need to use the text to columns function to let it know the data is comma delimited.

const data = [

    [

        {

            "Empid": 2326,

            "Empname": "Sam Smith",

            "AbsenceId": 12840,

            "Comment": "a001t000004FQgHAAW",

            "AbsenceStartDate": "2019-05-31T00:00:00.000Z",

            "AbsenceEndDate": "2019-05-31T00:00:00.000Z",

            "JobId": 400004,

            "AbsenceRequestId": ""

        },

        {

            "Empid": 3387,

            "Empname": "Joe bloggs",

            "AbsenceId": 12842,

            "Comment": "a001t000004FK67AAG",

            "AbsenceStartDate": "2019-06-06T00:00:00.000Z",

            "AbsenceEndDate": "2019-06-10T00:00:00.000Z",

            "JobId": 700004,

            "AbsenceRequestId": ""

       }

    ]

]



window.generateCSV = function () {
  let CSVData = ''
  // set the column names
  for (const value of Object.keys(data[0][0])) {
    CSVData = CSVData.concat(value + ',')
  }
  CSVData = CSVData.slice(0, CSVData.length - 1)
  CSVData = CSVData.concat('\n')
  
  // parse the data
	for (const tbl of data) {
		for (const row of tbl) {
			for (const value of Object.values(row)) {
      	CSVData = CSVData.concat(value + ',')
      }
      CSVData = CSVData.slice(0, CSVData.length - 2)
      CSVData = CSVData.concat('\n')
    }
  }
  document.getElementById("csvdata").innerText = CSVData

}

<input type="button" value="generateCSV" onclick="generateCSV()">

<div id="csvdata">

</div>

After saving the output string to a .txt or .csv through notepad, I can open in excel to get this.