How to read csv and xlsx file to Html table form with Javascript

Question

In my script, user can upload their CSV file and view the content with the HTML form. They are also able to add a new column at the first row of the HTML table. For example, when a user uploaded an excel file that contains 2 records, the script will automatically add a new row named Serial infront of the existing record. For now when user upload a csv file, it will show a weird empty column in the HTML table but it worked fine with xlsx format. Does anyone know what the cause of this issue ?

My Excel file:

Example (When a user uploads xlsx. format file, it will display correctly with no issue):

But if the user uploads a .csv file, there is a weird empty column will appear：

So how can I make the null column to dissapear? to work on both CSV and xlsx format? Now it seems like it only works for xlsx files.

Full Code

<!DOCTYPE HTML>
<html>

<head>


  <script type="text/javascript" src="https://unpkg.com/[email protected]/dist/xlsx.full.min.js"></script>
</head>

<body>
  <div class="container">
    <h2 class="text-center mt-4 mb-4">Convert Excel to HTML Table using JavaScript</h2>
    <div class="card">
      <div class="card-header"><b>Select Excel File</b></div>
      <div class="card-body">

        <input type="file" id="excel_file" />

      </div>
    </div>
    <div id="excel_data" class="mt-5"></div>
  </div>
</body>

</html>

<script>
  const excel_file = document.getElementById('excel_file');

  excel_file.addEventListener('change', (event) => {

    if (!['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'application/vnd.ms-excel'].includes(event.target.files[0].type)) {
      document.getElementById('excel_data').innerHTML = '<div class="alert alert-danger">Only .xlsx or .xls file format are allowed</div>';

      excel_file.value = '';

      return false;
    }

    var reader = new FileReader();

    reader.readAsArrayBuffer(event.target.files[0]);

    reader.onload = function(event) {

      var data = new Uint8Array(reader.result);

      var work_book = XLSX.read(data, {
        type: 'array'
      });

      var sheet_name = work_book.SheetNames;

      var sheet_data = XLSX.utils.sheet_to_json(work_book.Sheets[sheet_name[0]], {
        header: 1
      });

      if (sheet_data.length > 0) {
        var table_output = '<table class="table table-striped table-bordered">';

        for (var row = 0; row < sheet_data.length; row++) {

          table_output += '<tr>';
          if (row == 0) {
            table_output += '<th>' + ['Serial'] + '</th>'
          } else {
            table_output += '<td>' + row + '</td>'
          }

          for (var cell = 0; cell < sheet_data[row].length; cell++) {

            if (row == 0) {

              table_output += '<th>' + sheet_data[row][cell] + '</th>';

            } else {

              table_output += '<td>' + sheet_data[row][cell] + '</td>';

            }

          }

          table_output += '</tr>';

        }

        table_output += '</table>';

        document.getElementById('excel_data').innerHTML = table_output;
      }

      excel_file.value = '';

    }

  });
</script>

ProDec · Accepted Answer · 2021-10-16 09:37:05Z

1

Replace your JS library with latest version

https://cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js

Have a try.

I think your running version's default behavior does not treat the file with single column as CSV. Try multiple columns with comma separator, it works.

answered Oct 16, 2021 at 9:37

ProDec

5,4001 gold badge6 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

How to read csv and xlsx file to Html table form with Javascript

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related