How to Export HTML Table Data as .CSV File

How do I export html table data that has contains a comma to a .csv file?

Wrap things in a quote when you are getting the inner text. This will prevent the CSV from reading the comma as a break and will instead keep it as a single cell.

Below is your code from above, I just removed the FORM element so that you can actually run it here.

Line Changed:

row.push('"' + cols[j].innerText + '"');

Or using template string to make it cleaner:

row.push(`"${cols[j].innerText}"`);

function exportTableToCSV(filename) {
                var csv = [];
                var rows = document.querySelectorAll("table tr");
                
                for (var i = 0; i < rows.length; i++) {
                    var row = [], cols = rows[i].querySelectorAll("td, th");
                for (var j = 0; j < cols.length; j++) 
                    row.push('"' + cols[j].innerText + '"');
                csv.push(row.join(","));        
                }
                // Download CSV file
                downloadCSV(csv.join("\n"), filename);
            }
            
            function downloadCSV(csv, filename) {
                var csvFile;
                var downloadLink;
                // CSV file
                csvFile = new Blob([csv], {type: "text/csv"});
                // Download link
                downloadLink = document.createElement("a");
                // File name
                downloadLink.download = filename;
                // Create a link to the file
                downloadLink.href = window.URL.createObjectURL(csvFile);
                // Hide download link
                downloadLink.style.display = "none";
                // Add the link to DOM
                document.body.appendChild(downloadLink);
                // Click download link
                downloadLink.click();
            }

/* W3.CSS 4.13 June 2019 by Jan Egil and Borge Refsnes */
            html{box-sizing:border-box}*,*:before,*:after{box-sizing:inherit}
            .w3-container:after,.w3-container:before,.w3-panel:after,.w3-panel:before,.w3-row:after,.w3-row:before,.w3-row-padding:after,.w3-row-padding:before,
            .w3-container,.w3-panel{padding:0.01em 16px}.w3-panel{margin-top:16px;margin-bottom:16px}
            table {
              font-family: arial, sans-serif;
              border-collapse: collapse;
              width: 100%;
            }

            td, th {
              border: 1px solid #dddddd;
              text-align: left;
              padding: 8px;
            }

            tr:nth-child(even) {
              background-color: white;
            }
            tr:nth-child(odd) {
              background-color: white;
            }

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
        <meta name="google" content="notranslate">        
        <meta name="viewport" content="width=device-width, initial-scale=1">
    </head> 
    <body>    
        <div class="w3-container w3-orange" >
             <h2 class="w3-animate-left">Server Table</h2>
       
              <table id="bob">
                      <tr>
                        <th>Process Number</th>
                        <th>Connection String</th>
                        <th>Database Name</th>
                        <th>Table Name</th>
                        <th>Run Status</th>
                      </tr>
                      <tr>
                        <td>1</td>
                        <td>Server1,1433</td>
                        <td>tempdb</td>
                        <td>Table1</td>
                        <td>Pass</td>
                      </tr>
                      <tr>
                        <td>2</td>
                        <td>Server2,1433</td>
                        <td>tempdb</td>
                        <td>Table3</td>
                        <td>Pass</td>
                      </tr>
                    </table>
                <p></p>
            <p></p>
            <button onclick="exportTableToCSV('Server_Data.csv')">Export Results To CSV File</button>
     
        </div>
</body></html>

How to download html table as csv along with images src

For anyone who reads this in the future without having Excel and who wants to get the data formatted as CSV. This is how to do that:

let rows = document.getElementsByTagName('tr');

let cells;
let csv = "";

let csvSeparator = ";"; // Sets the separator between fields
let quoteField = true; // Adds quotes around fields

let regex = /.*<img.*src="(.*?)"/i

for (let row = 0; row < rows.length; row++) {
  cells = rows[row].getElementsByTagName('td');
  if (cells.length === 0) {
    cells = rows[row].getElementsByTagName('th');
  }
  for (let cell = 0; cell < cells.length; cell++) {
    if (quoteField) { csv += '"'; }
    
    if (regex.test(cells[cell].innerHTML)) {
      csv += cells[cell].innerHTML.match(regex)[1];
    } else {
      csv += cells[cell].innerText;
    }
    
    if (quoteField) { csv += '"'; }
    
    if (cell === cells.length - 1) {
      csv += "\n";
    } else {
      csv += csvSeparator;
    }
  }
}

function downloadToFile(content, filename, contentType) {
  const a = document.createElement('a');
  const file = new Blob([content], {type: contentType});
  
  a.href = URL.createObjectURL(file);
  a.download = filename;
  
  // To generate a link, use this:
  a.innerHTML = "Download CSV";
  document.body.appendChild(a);

  // If you want to automatically download then use this instead:
  /*
  a.click();
    URL.revokeObjectURL(a.href);
  */
}

console.log(csv);

downloadToFile(csv, 'csv-export.csv', 'text/plain');

* {
  font-family: sans-serif;
}

table {
  border-collapse: collapse;
}

table, th, td {
  border: 1px solid #ccc;
  padding: 5px 10px;
}

a {
  text-decoration: none;
}

<table>
    <thead>
      <tr>
        <th>Name</th>
        <th>Occupation</th>
        <th>Photo</th>
      </tr>
    </thead>
    <tbody> 
      <tr>
        <td>John Doe</td>
        <td>Student</td>
        <td><img src="xyz.com/image.png"></td>
     </tr>
     <tr>
        <td>Jane Doe</td>
        <td>Teacher</td>
        <td><img src="abc.com/image.png"></td>
     </tr>
    </tbody>
 </table>

Converting HTML table to CSV file using python

You can use pandas itself to do this. You have messed up with the import statement. Here is how you do it correctly:

import pandas as pd
df = pd.read_html('https://aim-sg.caas.gov.sg/aip/2020-10-13/final/2020-09-10-Non-AIR'
              'AC/html/eAIP/ENR-3.1-en-GB.html?s=B2EE1C5E1D2A684224A194E69D18338A560504FC#ENR-3.1')

df[0].to_csv('ENR3.0.csv', index = False)

If you want to get all the dataframes present within the variable df, then replace the last line with this:

for x in range(len(df)):
    df[x].to_csv(f"CSV_File_{x+1}", index = False)

How to Export HTML Table Data as .CSV File

How do I export html table data that has contains a comma to a .csv file?

How to download html table as csv along with images src

Converting HTML table to CSV file using python

Related Topics

Leave a reply