Search code examples
javascripthtmlhtml-tablejquery-selectorsexport-to-csv

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


Assume, Im having a table like below:MyTable

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>

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(/(\r\n|\n|\r)/gm, '').replace(/(\s\s)/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.

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

Thanks.


Solution

  • 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(/(\r\n|\n|\r)/gm, '');
                        text=text.replace(/(\s\s)/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>