How to Load Csv values in Datatable?

Question

How to read the values from a csv file and load it into a table using datatables plugin?

I have a static table :

<table id="myTable" class="table table-striped" >  
        <thead>  
          <tr>  
            <th>1</th>  
            <th>2</th>  
            <th>3</th>  
            <th>4</th>  
            <th>5</th>  
            <th>6</th>  
            <th>7</th>  
            <th>8</th>  
            <th>9</th>  
          </tr>  
        </thead>  
        <tbody>  
          <tr>  
            <td>a</td>  
            <td>s</td>  
            <td>d</td>  
            <td>f</td>  
            <td>f</td> 
            <td>f</td> 
            <td>f</td> 
            <td>f</td> 
            <td>f</td> 
          </tr>  
          <tr>  
            <td>a</td>  
            <td>s</td>  
            <td>d</td>  
            <td>f</td>  
            <td>f</td> 
            <td>f</td> 
            <td>f</td> 
            <td>f</td> 
            <td>f</td>  
          </tr>  
            
        </tbody>  
      </table>  
      </div>
</body>  
<script>
$(document).ready(function(){
    $('#myTable').dataTable();
});
</script>

but instead of defining all the data inside of the html tags , i want to read the values from csv files , how can i achieve that ?

Related question: [DataTables - Invalid JSON Response Query](https://stackoverflow.com/questions/68151757/datatables-invalid-json-response-query). Basically you have to transform your CSV data into a valid JSON structure. — andrewJames, Jul 04 '21 at 20:18

AlexSp3 · Answer 1 · 2021-07-04T20:41:27.293

I have made a code to do exactly what you want time ago:

document.getElementById('yourfile').onchange = function(evt) {
    var files = document.getElementById('yourfile').files; // FileList object
    var csv2json = new CSVToJSON();
    csv2json.parseCSV(files[0]);
}

var CSVToJSON = function() {
    this.parseCSV = function(file) {
        var reader = new FileReader();
        reader.onload = function(e) {
            var data = e.target.result;
            var json_object = _convertCSVtoJSON(data);
            Json2table(json_object);
        };
        reader.onerror = function(ex) {
            console.log(ex);
        };
        reader.readAsBinaryString(file);
    };
};

function _convertCSVtoJSON(csv) {

    var lines = csv.split("\n");
    var result = [];

    // NOTE: If your columns contain commas in their values, you'll need
    // to deal with those before doing the next step 
    // (you might convert them to &&& or something, then covert them back later)
    // jsfiddle showing the issue https://jsfiddle.net/

    var headers = lines[0].split(",");

    for (var i = 1; i < lines.length; i++) {
        var obj = {};
        var currentline = lines[i].split(",");

        for (var j = 0; j < headers.length; j++) {
            obj[headers[j]] = currentline[j];
        }
        result.push(obj);
    }
    return result; //JSON
}

function Json2table(obj) {
    $("#yourTable").html("");
    var largerKey = [];
    var maxkeylength = 0;
    var rows = Object.keys(obj);

    // This part is just to take the row with the higher number of columns in case your table isn´t an square

    for (var i = 0; i < rows.length; i++) {
        var keys = Object.keys(obj[i]);

        if (maxkeylength < keys.length) {
            largerKey = keys;
            maxkeylength = keys.length;
        }
    }

    ///////////////////////////////////////

    $("#yourTable").append("<thead>");
    for (var i = 0; i < largerKey.length; i++) {
        $("#yourTable").append("<th>" + largerKey[i] + "</th>");
    }
    $("#yourTable").append("</thead>");

    for (var i = 0; i < rows.length; i++) {
        var keys = Object.keys(obj[i]);
        var csvData = "";
        for (var j = 0; j < keys.length; j++) {
            csvData += "<td>" + obj[i][keys[j]] + "</td>";
        }
        $("#yourTable").append("<tr>" + csvData + "</tr>");
    }
    return;
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<html>
  <input type="file" id='yourfile'>
  <table id='yourTable'></table>
</html>

How to Load Csv values in Datatable?

1 Answers1