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.

What do you mean *move this into excel*... you mean generate a csv or xslx file? And what do you mean *transform into a table* ? You mean display a table in the markup with your data? — Isaac Vidrine, Apr 17 '19 at 15:16
you need to parse (develop a parser) the json and convert it to csv format for example in order to import it to excel. If you want to do the conversion manually, you can use this online service https://sqlify.io/convert/json/to/csv — Hichem BOUSSETTA, Apr 17 '19 at 15:28
Your source data is an array that contains just one element (another) array of objects. Is this correct? — Yevhen Horbunkov, Apr 17 '19 at 15:53
If you need to do that task on recurring basis, you may check out jQuery [DataTables](https://datatables.net) plug-in. It may render your JSON into HTML table with little of effort and provide export to Excel/CSV feature. — Yevhen Horbunkov, Apr 17 '19 at 15:55
`PowerQuery` in Excel will do that directly. See [json to excel using power query](https://stackoverflow.com/questions/42060625/json-to-excel-using-power-query) — Ron Rosenfeld, Apr 18 '19 at 00:24

Rowan Baker-French · Accepted Answer · 2019-04-17T21:06:30.880

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.

how to transform json data into table

1 Answers1