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.

CSV data is not JSON - they are completely different structures. DataTables expects JSON-structured data not CSV-structured data. Also, where is your <table> element and where is the mapping from your data source to table columns? — andrewJames
– andrewJames, Commented Jun 27, 2021 at 13:46
There are various examples here - do they help to clarify what you need to do? — andrewJames
– andrewJames, Commented Jun 27, 2021 at 13:46
Thanks for your reply andrewjames. There is nothing wrong with the table columns mapping: — ridgedale
– ridgedale, Commented Jun 27, 2021 at 14:41
<div class="mrgs-html"> <table id="mrgs" class="display" style="width:100%"> <thead> <tr> <th>Type</th> <th>Qtr</th> <th>Year</th> <th>Surname</th> <th>Forename(s)</th> <th>SpseName</th> <th>District</th> <th>Volume</th> <th>Page</th> </tr> </thead> </table> </div> — ridgedale
– ridgedale, Commented Jun 27, 2021 at 14:41

Kinglish · Accepted Answer · 2021-06-29 16:29:09Z

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>

edited Jun 29, 2021 at 16:29

answered Jun 27, 2021 at 15:25

Kinglish

23.7k4 gold badges25 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

33 Comments

ridgedale Over a year ago

Thanke for your reply Kinglish. It would definitely save time if I could avoid having to convert the files. However, when I replaced the document ready function code with your code and edited the url line to point to the csv file only the table header from the <table> code is displayed. No data is displayed, no search field, no sort facility, no show x number of entries dropdown. What am I overlooking?

Kinglish Over a year ago

I updated my answer, the code and added an example snippet

ridgedale Over a year ago

I can see the code snippet works, but when I replace the code with:

ridgedale Over a year ago

$(document).ready(function() { $('#mrgs').DataTable( { let csv =

First name, Last name, Address, City, State, Zip John,Doe,120 jefferson st.,Riverside, NJ, 08075 Jack,McGinnis,220 hobo Av.,Phila, PA,09119 Stephen,Tyler,7452 Terrace At the Plaza road,SomeTown, SD, 91234, Blankman,,SomeTown, SD, 00298

ridgedale Over a year ago

I was trying to work how to upvote and accpet the answer. It is quite frankly not obvious. As I now understand the person raising the question has to click the up-arrow (to the top left of the answer) to upvote the answer and click on the tick below the arrows to accept the answer. I hope that is correct. Many thanks again.

|

Collectives™ on Stack Overflow

DataTables - Invalid JSON Response Query

1 Answer 1

33 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

33 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related