How to drop a column when exporting data from html table to csv file?

Assume, Im having a table like below:

I need to drop the “Contact” column and export the remaining column to the CSV file.

HTML:

<button id="downl" onclick="dropColumn('mytableid');">Download</button>

JavaScript
​x
 
<button id="downl" onclick="dropColumn('mytableid');">Download</button>
​

On click of the download button, js function will get called.

JavaScript

//Drop Column
function dropColumn(mytableid){
 var clonetable = $('#mytableid').clone();
 clonetable.find('td:nth-child(2),(the:nth-child(2)').remove();
 download_table_as_csv(clonetable);
}

//Download as CSV
function download_table_as_csv(table_id, separator = ',') {
    // Select rows from table_id
    var rows = document.querySelectorAll('table#' + table_id + ' tr');
    // Construct csv
    var csv = [];
    for (var i = 0; i < rows.length; i++) {
        var row = [], cols = rows[i].querySelectorAll('td, th');
        for (var j = 0; j < cols.length; j++) {
            // Clean innertext to remove multiple spaces and jumpline (break csv)
            var data = cols[j].innerText.replace(/(rn|n|r)/gm, '').replace(/(ss)/gm, ' ')
            // Escape double-quote with double-double-quote (see https://stackoverflow.com/questions/17808511/properly-escape-a-double-quote-in-csv)
            data = data.replace(/"/g, '""');
            // Push escaped string
            row.push('"' + data + '"');
        }
        csv.push(row.join(separator));
    }
    var csv_string = csv.join('n');
    // Download it
    var filename = 'export_' + table_id + '_' + new Date().toLocaleDateString() + '.csv';
    var link = document.createElement('a');
    link.style.display = 'none';
    link.setAttribute('target', '_blank');
    link.setAttribute('href', 'data:text/csv;charset=utf-8,' + encodeURIComponent(csv_string));
    link.setAttribute('download', filename);
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
}

JavaScript
 
//Drop Column
function dropColumn(mytableid){
 var clonetable = $('#mytableid').clone();
 clonetable.find('td:nth-child(2),(the:nth-child(2)').remove();
 download_table_as_csv(clonetable);
}
​
//Download as CSV
function download_table_as_csv(table_id, separator = ',') {
    // Select rows from table_id
    var rows = document.querySelectorAll('table#' + table_id + ' tr');
    // Construct csv
    var csv = [];
    for (var i = 0; i < rows.length; i++) {
        var row = [], cols = rows[i].querySelectorAll('td, th');
        for (var j = 0; j < cols.length; j++) {
            // Clean innertext to remove multiple spaces and jumpline (break csv)
            var data = cols[j].innerText.replace(/(rn|n|r)/gm, '').replace(/(ss)/gm, ' ')
            // Escape double-quote with double-double-quote (see https://stackoverflow.com/questions/17808511/properly-escape-a-double-quote-in-csv)
            data = data.replace(/"/g, '""');
            // Push escaped string
            row.push('"' + data + '"');
        }
        csv.push(row.join(separator));
    }
    var csv_string = csv.join('n');
    // Download it
    var filename = 'export_' + table_id + '_' + new Date().toLocaleDateString() + '.csv';
    var link = document.createElement('a');
    link.style.display = 'none';
    link.setAttribute('target', '_blank');
    link.setAttribute('href', 'data:text/csv;charset=utf-8,' + encodeURIComponent(csv_string));
    link.setAttribute('download', filename);
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
}
​

Error:

Uncaught DOMException: Failed to execute 'querySelectorAll' on 'Document': 'table#[object Object]tr' is not a valid selector.

JavaScript
 
Uncaught DOMException: Failed to execute 'querySelectorAll' on 'Document': 'table#[object Object]tr' is not a valid selector.
​

How to export table data to CSV by excluding a specific column?.Any way to achieve this.

Thanks.

Answer

If you find a collection of table headers and from that find the cell that contains the exclusion term ( Contact ) in it’s textContent you can use that index later to exclude table cells ( per row ) of the same index.

<!DOCTYPE html>
<html lang='en'>
    <head>
        <meta charset='utf-8' />
        <title>Export HTML table - exclude column by text content or other criteria</title>
        <script>
            document.addEventListener('DOMContentLoaded',()=>{
            
                const preparetext=function(text,regex,rep){
                    text=text.replace(/(rn|n|r)/gm, '');
                    text=text.replace(/(ss)/gm, ' ');
                    text=text.replace(/"/g, '""');
                    return text;
                };
            
                document.querySelector('input[type="button"][name="export"]').addEventListener('click',e=>{
                    let table=document.querySelector('table#geronimo');
                    let colHeaders=table.querySelectorAll('tr th');
                    let colRows=table.querySelectorAll('tr:not( .headers )');
                    
                    let index=-1;
                    let exclude='Contact';
                    let headers=[];
                    let data=[];
                    
                    colHeaders.forEach( ( th, i )=>{
                        if( th.textContent!=exclude )headers.push( [ '"', preparetext( th.textContent ), '"' ].join('') )
                        else index=i;
                    });
                    
                    data.push( headers.join(',') );
                    data.push( String.fromCharCode(10) );
                    
                    
                    
                    if( index > -1 ){
                        colRows.forEach( tr => {
                        
                            let cells=tr.querySelectorAll('td');
                            let row=[];
                            
                            cells.forEach( ( td, i )=>{
                                if( i !== index ) row.push( [ '"', preparetext( td.textContent), '"' ].join('') )
                            });
                            
                            data.push( row.join(',') );
                            data.push( String.fromCharCode(10) );
                        });
                        
                        let a=document.createElement('a');
                            a.download='export_'+table.id+'_'+( new Date().toLocaleDateString() )+'.csv';
                            a.href=URL.createObjectURL( new Blob( data ) );
                            a.click();  
                    }
                })
            });
        </script>
    </head>
    <body>
        <table id='geronimo'>
            <tr class='headers'>
                <th scope='col'>Company</th>
                <th scope='col'>Contact</th>
                <th scope='col'>Country</th>
            </tr>
            <tr>
                <td>Jolly Roger Cookery School Ltd</td>
                <td>Blackbeard</td>
                <td>Jamaica</td>
            </tr>
            <tr>
                <td>Autonomous Hedgehog Collective</td>
                <td>Mr. Ben</td>
                <td>United Kingdom</td>
            </tr>
            <tr>
                <td>The Cock Inn</td>
                <td>Miss. Tilly Lykes</td>
                <td>Scotland</td>
            </tr>
            <tr>
                <td>Hooker Furniture</td>
                <td>Hubert</td>
                <td>Hanoi</td>
            </tr>
            <tr>
                <td>Horrible Haggis Hunt</td>
                <td>Horace Hubert</td>
                <td>Hungary</td>
            </tr>
            
        </table>
        <input type='button' name='export' value='Download CSV' />
    </body>
</html>

JavaScript
 
<!DOCTYPE html>
<html lang='en'>
    <head>
        <meta charset='utf-8' />
        <title>Export HTML table - exclude column by text content or other criteria</title>
        <script>
            document.addEventListener('DOMContentLoaded',()=>{
            
                const preparetext=function(text,regex,rep){
                    text=text.replace(/(rn|n|r)/gm, '');
                    text=text.replace(/(ss)/gm, ' ');
                    text=text.replace(/"/g, '""');
                    return text;
                };
            
                document.querySelector('input[type="button"][name="export"]').addEventListener('click',e=>{
                    let table=document.querySelector('table#geronimo');
                    let colHeaders=table.querySelectorAll('tr th');
                    let colRows=table.querySelectorAll('tr:not( .headers )');
                    
                    let index=-1;
                    let exclude='Contact';
                    let headers=[];
                    let data=[];
                    
                    colHeaders.forEach( ( th, i )=>{
                        if( th.textContent!=exclude )headers.push( [ '"', preparetext( th.textContent ), '"' ].join('') )
                        else index=i;
                    });
                    
                    data.push( headers.join(',') );
                    data.push( String.fromCharCode(10) );
                    
                    
                    
                    if( index > -1 ){
                        colRows.forEach( tr => {
                        
                            let cells=tr.querySelectorAll('td');
                            let row=[];
                            
                            cells.forEach( ( td, i )=>{
                                if( i !== index ) row.push( [ '"', preparetext( td.textContent), '"' ].join('') )
                            });
                            
                            data.push( row.join(',') );
                            data.push( String.fromCharCode(10) );
                        });
                        
                        let a=document.createElement('a');
                            a.download='export_'+table.id+'_'+( new Date().toLocaleDateString() )+'.csv';
                            a.href=URL.createObjectURL( new Blob( data ) );
                            a.click();  
                    }
                })
            });
        </script>
    </head>
    <body>
        <table id='geronimo'>
            <tr class='headers'>
                <th scope='col'>Company</th>
                <th scope='col'>Contact</th>
                <th scope='col'>Country</th>
            </tr>
            <tr>
                <td>Jolly Roger Cookery School Ltd</td>
                <td>Blackbeard</td>
                <td>Jamaica</td>
            </tr>
            <tr>
                <td>Autonomous Hedgehog Collective</td>
                <td>Mr. Ben</td>
                <td>United Kingdom</td>
            </tr>
            <tr>
                <td>The Cock Inn</td>
                <td>Miss. Tilly Lykes</td>
                <td>Scotland</td>
            </tr>
            <tr>
                <td>Hooker Furniture</td>
                <td>Hubert</td>
                <td>Hanoi</td>
            </tr>
            <tr>
                <td>Horrible Haggis Hunt</td>
                <td>Horace Hubert</td>
                <td>Hungary</td>
            </tr>
            
        </table>
        <input type='button' name='export' value='Download CSV' />
    </body>
</html>
​

Advertisement

Answer