DataTables - Invalid JSON Response Query

Question

I've created a page using a .csv file (comma delimited file) as the data source, but when I load the page an Invalid JSON response error is returned. When I check Network > XHR I can't see any error information and nothing is displayed under the Response tab. However, when I click on the OK button to dismiss the error message, all the data from the .csv file is displayed under the Response tab.

The same issue results whether I host the files locally or on my webserver.

Could this be an issue with a configuration in the https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js file? I've provided the relevant header code I've used for reference below:

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css">
<style type="text/css" class="init"></style>

<script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script>
<script type="text/javascript" language="javascript" src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" class="init">
$(document).ready(function() {
    $('#mrgs').DataTable( {
        "ajax": 'mydata.csv'
    } );
} );
</script>

Does anyone have any ideas what could be causing this issue. Any assistance would be greatly appreciated.

Answer 1

You can preprocess your csv to be in the array of object values format DataTable wants.

Here is the complete solution ready to plug into your page

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href='https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css' />
<script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
<table id="mrgs" class="table"></table>
<script>
$(document).ready(function() {
    $.ajax({
        url: "./data.csv",
        context: document.body
    }).done(function(csv) {
        let allTextLines = csv.split(/\r\n|\n/);
        let headers = allTextLines[0].split(',').map(e => e.trim());
        let lines = [];
        for (let i = 1; i < allTextLines.length; i++) {
            let line = allTextLines[i].split(',') // take the comma separated line, turn into an array
                .reduce((b, a, index) => { // b is the accumulator, a is the iteration value
                    b[headers[index].toLowerCase().replace(/\s+/g, '')] = a.trim(); // set the object property and value
                    return b; // return the accumulator for our next iteration
                }, {});
        if (Object.keys(line).length === headers.length) lines.push(line)            }
        // now fix headers to be object/value pairs
        headers = headers.map((e) => ({
            title: e,
            data: e.toLowerCase().replace(/\s+/g, '')
        }))
        // console.log(headers)
        //  console.log(lines)
        $('#mrgs').DataTable({
            columns: headers,
            data: lines
        });
    });
});
</script>

$(document).ready(function() {
  let csv = `Type,Qtr,Year,Surname,Forenames,SpseName,District,Volume,Page 
  Marriages,Dec,1837,JAMES,Ann,,Mansfield,15,942 
  Marriages,Dec,1839,Karlton,Diana,,Mansfield,15,1017 
  Marriages,Dec,1841,Mepham,Elizabeth,,Mansfield,15,994 
  Marriages,Sep,1842,CASPIAN,Sophia,,Mansfield,15,617 
  Marriages,Dec,1842,Kennedy,Mark,,Mansfield,15,957
  Marriages,Dec,1843,Crampus,Elizabeth,,Mansfield,15,1034 
  Marriages,Mar,1846,Dalton,Paulina,,Mansfield,15,741 
  Marriages,Dec,1846,JAMIESON,William,,Mansfield,15,1031 
  Marriages,Dec,1848,Rodon,Reuben,,Mansfield,15,1096 
  Marriages,Mar,1849,PHILBERT,Reuben,,Mansfield,15,703 
  Marriages,Dec,1849,STARKEY,Thos,,Mansfield,15,1092 
  Marriages,Jun,1850,Porter,John,,Mansfield,15,843`
  let allTextLines = csv.split(/\r\n|\n/);
  let headers = allTextLines[0].split(',').map(e => e.trim());
  let lines = [];
  for (let i = 1; i < allTextLines.length; i++) {
    let line = allTextLines[i].split(',') // take the comma separated line, turn into an array
      .reduce((b, a, index) => { // b is the accumulator, a is the iteration value
        b[headers[index].toLowerCase().replace(/\s+/g, '')] = a.trim(); // set the object property and value
        return b; // return the accumulator for our next iteration
      }, {});
    if (Object.keys(line).length === headers.length) lines.push(line)
  }
  // now fix headers to be object/value pairs
  headers = headers.map((e) => ({
    title: e,
    data: e.toLowerCase().replace(/\s+/g, '')
  }))
  // console.log(headers)
  //  console.log(lines)
  $('#mrgs').DataTable({
    columns: headers,
    data: lines
  });
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href='//cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css' />
<script src="//cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>

<table id="mrgs" class="table"></table>