How to remove Undefined values in JAVASCRIPT from excel values (when it's blank, use blank rather than undefined)?

Question

I found this code that works fine but when there is a blank value in excel, it shows undefined, while i would like it to remain blank.

Any help would be great, however I just started programming a few days ago, so details on the syntax would be great (like what to remove out of the code and what to replace).

Thanks

<!DOCTYPE html>
<html>
  <head>
    <meta name="viewport" content="width=device-width, initial-scale=1">  
    <title>Excel to HTML Table | Javacodepoint</title>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.5/xlsx.min.js"></script>    
  </head>
  <body>
    <h1>Upload an excel file to display in HTML Table</h1>
    <!-- Input element to upload an excel file -->
    <input type="file" id="file_upload" />
    <button onclick="upload()">Upload</button>  
    <br>
    <br>
    <!-- table to display the excel data -->
    <table id="display_excel_data" border="1"></table>
    <script>
     
      // Method to upload a valid excel file
      function upload() {
        var files = document.getElementById('file_upload').files;
        if(files.length==0){
          alert("Please choose any file...");
          return;
        }
        var filename = files[0].name;
        var extension = filename.substring(filename.lastIndexOf(".")).toUpperCase();
        if (extension == '.XLS' || extension == '.XLSX') {
            //Here calling another method to read excel file into json
            excelFileToJSON(files[0]);
        }else{
            alert("Please select a valid excel file.");
        }
      }
       
      //Method to read excel file and convert it into JSON 
      
      function excelFileToJSON(file){
          try {
            var reader = new FileReader();
            reader.readAsBinaryString(file);
            reader.onload = function(e) {
 
                var data = e.target.result;
                var workbook = XLSX.read(data, {
                    type : 'binary'
                });
                var result = {};
                var firstSheetName = workbook.SheetNames[0];
                //reading only first sheet data
                var jsonData = XLSX.utils.sheet_to_json(workbook.Sheets[firstSheetName]);
                //displaying the json result into HTML table
                displayJsonToHtmlTable(jsonData);
                }
            }catch(e){
                console.error(e);
            }
      }


      //Method to display the data in HTML Table
      function displayJsonToHtmlTable(jsonData){
        var table=document.getElementById("display_excel_data");
        if(jsonData.length>0){
            var htmlData='<tr><th>Student Name</th><th>Address</th><th>Email ID</th><th>Age</th></tr>';
            for(var i=0;i<jsonData.length;i++){
                var row=jsonData[i];
                htmlData+='<tr><td>'+row["hgyu"]+'</td><td>'+row["Address"]
                      +'</td><td>'+row["Email ID"]+'</td><td>'+row["Age"]+'</td></tr>';
            }
            table.innerHTML=htmlData;
        }else{
            table.innerHTML='There is no data in Excel';
        }
      }
    </script>
  </body>
</html>

Chris Barr · Accepted Answer · 2023-05-10T22:07:00.607

What is happening is when you are asking for some data that might not exist in each row. When that happens, that data is undefined so that's what it will convert to a string and show. A way around this is to do row['Age'] || '' which which somewhat treat row['Age'] as a boolean of sorts, if it resolves to a "falsey" value like undefined then it will take the other side of the or statement and return an empty string like you want.

So here is all that needs to change to do this:

for(var i=0;i<jsonData.length;i++){
  var row=jsonData[i];

  var hgyu = row["hgyu"] || '';
  var address = row["Address"] || '';
  var email = row["Email ID"] || '';
  var age = row["Age"] || '';

  htmlData+='<tr><td>'+hgyu+'</td><td>'+address
            +'</td><td>'+email+'</td><td>'+age+'</td></tr>';
}

And here is the whole thing working below.

// Method to upload a valid excel file
function upload() {
  var files = document.getElementById('file_upload').files;
  if(files.length==0){
    alert("Please choose any file...");
    return;
  }
  var filename = files[0].name;
  var extension = filename.substring(filename.lastIndexOf(".")).toUpperCase();
  if (extension == '.XLS' || extension == '.XLSX') {
      //Here calling another method to read excel file into json
      excelFileToJSON(files[0]);
  }else{
      alert("Please select a valid excel file.");
  }
}

//Method to read excel file and convert it into JSON 

function excelFileToJSON(file){
    try {
      var reader = new FileReader();
      reader.readAsBinaryString(file);
      reader.onload = function(e) {

          var data = e.target.result;
          var workbook = XLSX.read(data, {
              type : 'binary'
          });
          var result = {};
          var firstSheetName = workbook.SheetNames[0];
          //reading only first sheet data
          var jsonData = XLSX.utils.sheet_to_json(workbook.Sheets[firstSheetName]);
          //displaying the json result into HTML table
          displayJsonToHtmlTable(jsonData);
          }
      }catch(e){
          console.error(e);
      }
}

//Method to display the data in HTML Table
function displayJsonToHtmlTable(jsonData){
  var table=document.getElementById("display_excel_data");
  if(jsonData.length>0){
      var htmlData='<tr><th>Student Name</th><th>Address</th><th>Email ID</th><th>Age</th></tr>';
      for(var i=0;i<jsonData.length;i++){
          var row=jsonData[i];
          var hgyu = row["hgyu"] || '';
          var address = row["Address"] || '';
          var email = row["Email ID"] || '';
          var age = row["Age"] || '';
          htmlData+='<tr><td>'+hgyu+'</td><td>'+address
                +'</td><td>'+email+'</td><td>'+age+'</td></tr>';
      }
      table.innerHTML=htmlData;
  }else{
      table.innerHTML='There is no data in Excel';
  }
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.5/xlsx.min.js"></script>    

<h1>Upload an excel file to display in HTML Table</h1>
<!-- Input element to upload an excel file -->
<input type="file" id="file_upload" />
<button onclick="upload()">Upload</button>  
<br>
<br>
<!-- table to display the excel data -->
<table id="display_excel_data" border="1"></table>

EDIT

Adding on here to demonstrate how to add a new table for each excel row, as discussed in the comments on this answer.

// Method to upload a valid excel file
function upload() {
  var files = document.getElementById('file_upload').files;
  if(files.length==0){
    alert("Please choose any file...");
    return;
  }
  var filename = files[0].name;
  var extension = filename.substring(filename.lastIndexOf(".")).toUpperCase();
  if (extension == '.XLS' || extension == '.XLSX') {
      //Here calling another method to read excel file into json
      excelFileToJSON(files[0]);
  }else{
      alert("Please select a valid excel file.");
  }
}

//Method to read excel file and convert it into JSON 

function excelFileToJSON(file){
    try {
      var reader = new FileReader();
      reader.readAsBinaryString(file);
      reader.onload = function(e) {

          var data = e.target.result;
          var workbook = XLSX.read(data, {
              type : 'binary'
          });
          var result = {};
          var firstSheetName = workbook.SheetNames[0];
          //reading only first sheet data
          var jsonData = XLSX.utils.sheet_to_json(workbook.Sheets[firstSheetName]);
          //displaying the json result into HTML table
          displayJsonToHtmlTable(jsonData);
          }
      }catch(e){
          console.error(e);
      }
}

//Method to display the data in HTML Table
function displayJsonToHtmlTable(jsonData){
  var table=document.getElementById("display_excel_data");
  if(jsonData.length>0){
      var htmlData=''
      
      for(var i=0;i<jsonData.length;i++){
          var row=jsonData[i];
          var hgyu = row["hgyu"] || '';
          var address = row["Address"] || '';
          var email = row["Email ID"] || '';
          var age = row["Age"] || '';
          
          htmlData+='<table border="1">'
           +'<tr><th>Student Name</th><th>Address</th><th>Email ID</th><th>Age</th></tr>'
           +'<tr><td>'+hgyu+'</td>'
           +'<td>'+address+'</td>'
           +'<td>'+email+'</td>'
           +'<td>'+age+'</td>'
           +'</tr></table>';
      }
      table.innerHTML=htmlData;
  }else{
      table.innerHTML='There is no data in Excel';
  }
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.5/xlsx.min.js"></script>    

<h1>Upload an excel file to display in HTML Table</h1>
<!-- Input element to upload an excel file -->
<input type="file" id="file_upload" />
<button onclick="upload()">Upload</button>  
<br>
<br>
<!-- table to display the excel data -->
<div id="display_excel_data"></div>

Basically in the actual HTML it now just uses a <div> for everything to be inserted into. In the script now the table and the table headers are moved inside of the for loop so that it runs each time.

Hi @FiniteLooper Is there a way from the previous code to build a table for each row in excel (rather than a TR, it is a Table?)? For example, rather than htmlData+='' , it becomes htmlData+='
' , but it doesn't work. — l4cky, May 10 '23 at 20:21
It can certainly work, but the problem I'm guessing you are having is that the current code has a `` element already in the HTML and then all the ``'s are being added into it. If you want to add a new table for each excel row you will have to move some stuff around because you cannot put a `
` inside another `
` to achieve what you want. I will add onto my answer to show you what I mean — Chris Barr, May 10 '23 at 21:56
Hi @FiniteLooper I can not thank you enough for the help, it's working great! I am learning a lot from you. I am trying to use the same understanding to a similar situation, but I encounter a problem. I think it might have to do with some javascript inside a javascript function.. I don't know how to post the code in the comment reply, so I would just post it in a new question: https://stackoverflow.com/questions/76237221/javascript-inside-a-javafunction-not-loading-not-sure-how-to-syntax-it — l4cky, May 12 '23 at 14:20

How to remove Undefined values in JAVASCRIPT from excel values (when it's blank, use blank rather than undefined)?

1 Answers1

EDIT