Populate HTML table with JSON data

Question

We would be grateful for some guidance, [we are early into coding] and have looked at lots of examples but can cannot get our JSON to import into the table.

At that moment we have the table data in-line however a correctly formatted JSON file is now available and automatically updates and we want to load it into the table on the fly when the page is loaded.

This is an example of what were currently use:

<div>
<p> *** OTHER STUFF HERE ***<p/>
    <table id="gable">
        <colgroup>
            <col class="twenty" />
            <col class="fourty" />
            <col class="thirtyfive" />
            <col class="twentyfive" />
        </colgroup>
        <tr>
            <th onclick="sortTable(0)"><span class="glyphicon glyphicon-sort"></span>&nbsp&nbspCOUNTRY</th>
            <th onclick="sortTable(1)"><span class="glyphicon glyphicon-sort"></span>&nbsp&nbspLOCATION</th>
            <th onclick="sortTable(2)"><span class="glyphicon glyphicon-sort"></span>&nbsp&nbspBALANCE</th>
            <th onclick="sortTable(3)"><span class="glyphicon glyphicon-sort"></span>&nbsp&nbspDATE</th>
        </tr>
    </table>
</div>
<script>
var data = [
    { "COUNTRY":"UK", "LoC":"London", "BALANCE":"78,573", "DATE":"1/06/2018" },
    { "COUNTRY":"US", "LoC":"New York", "BALANCE":"43,568", "DATE":"18/05/2018" },
    { "COUNTRY":"PL", "LoC":"Kraków", "BALANCE":"12,362", "DATE":"22/06/2018" },
    { "COUNTRY":"AU", "LoC":"Townsville", "BALANCE":"7,569", "DATE":"1/07/2018" },
    { "COUNTRY":" ", "LoC":"BALANCE:", "BALANCE":"142,072", "DATE":" " }
];
var table = document.getElementById('gable');
data.forEach(function(object) {
    var tr = document.createElement('tr');
    tr.innerHTML = '<td>' + object.COUNTRY + '</td>' +
        '<td>' + object.LoC + '</td>' +
        '<td>' + object.BALANCE + '</td>' +
        '<td>' + object.DATE + '</td>';
    table.appendChild(tr);
});
</script>

There are a lot more lines of data, the table has CSS styling and applies the sortTable(n) function to the Headers. It displays perfectly, looks and functions like we want,

We've Googled [lots] and tried various load / populate scripts and attempted to get the example on w3schools working - https://www.w3schools.com/js/js_json_html.asp - alas we're fairly new to this.

Our JSON file /assets/sample.JSON is correctly formatted and meets the requirements.

How do we do a simple import of the JSON to populate the table id="gable"?

Answer 1

Ok, so in this solution I am going to assume that your external json file is called 'example.json'

Your external file should look something like this example.json:

[
  { "COUNTRY":"UK", "LoC":"London", "BALANCE":"78,573", "DATE":"1/06/2018" },
  { "COUNTRY":"US", "LoC":"New York", "BALANCE":"43,568", "DATE":"18/05/2018" },
  { "COUNTRY":"PL", "LoC":"Kraków", "BALANCE":"12,362", "DATE":"22/06/2018" },
  { "COUNTRY":"AU", "LoC":"Townsville", "BALANCE":"7,569", "DATE":"1/07/2018" },
  { "COUNTRY":" ", "LoC":"BALANCE:", "BALANCE":"142,072", "DATE":" " }
]

The html remains the same all the changes have been made in the script tags. I split the functionality into 2 new functions. The first function (get_json_data) gets the json data from the external json file. The second function (append_json) appends the data to the table.

I have put comments throughout the code to explain what everything is doing. if you have any questions or if something is unclear let me know.

here is the code for the html file:

<div>
        <p> *** OTHER STUFF HERE ***<p/>
        <table id="gable">
            <colgroup>
                <col class="twenty" />
                <col class="fourty" />
                <col class="thirtyfive" />
                <col class="twentyfive" />
            </colgroup>
            <tr>
                <th onclick="sortTable(0)"><span class="glyphicon glyphicon-sort"></span>&nbsp&nbspCOUNTRY</th>
                <th onclick="sortTable(1)"><span class="glyphicon glyphicon-sort"></span>&nbsp&nbspLOCATION</th>
                <th onclick="sortTable(2)"><span class="glyphicon glyphicon-sort"></span>&nbsp&nbspBALANCE</th>
                <th onclick="sortTable(3)"><span class="glyphicon glyphicon-sort"></span>&nbsp&nbspDATE</th>
            </tr>
        </table>
    </div>
    <script>
        //first add an event listener for page load
        document.addEventListener( "DOMContentLoaded", get_json_data, false ); // get_json_data is the function name that will fire on page load

        //this function is in the event listener and will execute on page load
        function get_json_data(){
            // Relative URL of external json file
            var json_url = 'example.json';

            //Build the XMLHttpRequest (aka AJAX Request)
            xmlhttp = new XMLHttpRequest();
            xmlhttp.onreadystatechange = function() { 
                if (this.readyState == 4 && this.status == 200) {//when a good response is given do this

                    var data = JSON.parse(this.responseText); // convert the response to a json object
                    append_json(data);// pass the json object to the append_json function
                }
            }
            //set the request destination and type
            xmlhttp.open("POST", json_url, true);
            //set required headers for the request
            xmlhttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
            // send the request
            xmlhttp.send(); // when the request completes it will execute the code in onreadystatechange section
        }

        //this function appends the json data to the table 'gable'
        function append_json(data){
            var table = document.getElementById('gable');
            data.forEach(function(object) {
                var tr = document.createElement('tr');
                tr.innerHTML = '<td>' + object.COUNTRY + '</td>' +
                '<td>' + object.LoC + '</td>' +
                '<td>' + object.BALANCE + '</td>' +
                '<td>' + object.DATE + '</td>';
                table.appendChild(tr);
            });
        }
    </script>

Answer 2

you can have a function to create the table independent from your data fields:

function updateTable(tableId, jsonData){

  var tableHTML = "<tr>";
  for (var headers in jsonData[0]) {
    tableHTML += "<th>" + headers + "</th>";
  }
  tableHTML += "</tr>";

  for (var eachItem in jsonData) {
    tableHTML += "<tr>";
    var dataObj = jsonData[eachItem];
    for (var eachValue in dataObj){
      tableHTML += "<td>" + dataObj[eachValue] + "</td>";
    }
    tableHTML += "</tr>";
  }

  document.getElementById(tableId).innerHTML = tableHTML;
}