TableSorter : csv export and accented characters

Question

With TableSorter, when I export my table in a CSV file, the accented characters doesn't appear correctly.

How to solve that ?

Answer 1

In version 2.16.4, the output_encoding option was added to the output widget.

The demo now has a select dropdown in which you can choose "utf8 BOM" required to make the csv file work properly in Excel.

You can set this option to include a BOM by default as follows:

output_encoding : 'data:text/csv;charset=utf8,%EF%BB%BF'

Answer 2

With version 2.30.6, the solution proposed above didn't work for me.

I read an article saying the unicode character \uFEFF should be specified at the beginning of the CSV data. So, my code below insert this character only once and I reset the boolean immediately to do it only once. Dont pay attention about scroller_* widget options if you don't use the widget 'scroller' (same for 'columnSelector' and 'filter' widgets).

Here to show UTF8 data in Excel, use the callback function output_formatContent and see my code below.

var insertBOM = true;

var $tablesorterScroll = $(".tablesorter-scroll");
$tablesorterScroll.tablesorter({
    sortInitialOrder: 'desc',
    widthFixed : false,
    showProcessing: true,
    widgets: ['filter', 'columnSelector', 'scroller', 'output'],
    widgetOptions: {
        // Set the max-height property of the table for the scroller widget.
        scroller_height : 412, // sized with table CompanyBoard statistics (fits like that without scrollbar and finishes at the end of the row)

        // Delay in milliseconds before the filter widget starts searching; This option prevents searching for
        // every character while typing and should make searching large tables faster.
        filter_searchDelay :  200,

        // target the column selector markup
        columnSelector_container : $('#columnSelector'),
        // column status, true = display, false = hide
        // disable = do not display on list
        columnSelector_columns : {
            //0: 'disable' /* set to disabled; not allowed to unselect it */
        },
        // remember selected columns (requires $.tablesorter.storage)
        columnSelector_saveColumns: true,

        // container layout
        columnSelector_layout : '<label><input type="checkbox">{name}</label>',
        // data attribute containing column name to use in the selector container
        columnSelector_name  : 'data-selector-name',

        /* Responsive Media Query settings */
        // enable/disable mediaquery breakpoints
        columnSelector_mediaquery: false,
        // toggle checkbox name
        columnSelector_mediaqueryName: 'Auto: ',
        // breakpoints checkbox initial setting
        columnSelector_mediaqueryState: false,
        // hide columnSelector false columns while in auto mode
        columnSelector_mediaqueryHidden: false,

        // set the maximum and/or minimum number of visible columns; use null to disable
        columnSelector_maxVisible: null,
        columnSelector_minVisible: null,
        // responsive table hides columns with priority 1-6 at these breakpoints
        // see http://view.jquerymobile.com/1.3.2/dist/demos/widgets/table-column-toggle/#Applyingapresetbreakpoint
        // *** set to false to disable ***
        columnSelector_breakpoints : [ '20em', '30em', '40em', '50em', '60em', '70em' ],
        // data attribute containing column priority
        // duplicates how jQuery mobile uses priorities:
        // http://view.jquerymobile.com/1.3.2/dist/demos/widgets/table-column-toggle/
        columnSelector_priority : 'data-priority',

        // class name added to checked checkboxes - this fixes an issue with Chrome not updating FontAwesome
        // applied icons; use this class name (input.checked) instead of input:checked
        columnSelector_cssChecked : 'checked',

        output_saveFileName : 'TableExport.csv',
        output_separator: ';', // Excel recognize it and shows data in separated column without doing "Text to columns" Excel option.
        output_replaceQuote: '\'',
        output_delivery: 'd', // (p)opup, (d)ownload
        output_saveRows: 'v', // (a)ll, (v)isible, (f)iltered, jQuery filter selector (string only) or filter function
        output_encoding: "data:text/csv;charset=utf-8",
        output_formatContent: function (config, widgetOptions, data) {
            // data.isHeader (boolean) = true if processing a header cell
            // data.$cell = jQuery object of the cell currently being processed
            // data.content = processed cell content
            //    (spaces trimmed, quotes added/replaced, etc)
            // **********
            // use data.$cell.html() to get the original cell content
            var BOM = "";
            // Add BOM at file starting
            if (insertBOM) {
                BOM = "\uFEFF"; // set Excel enconding UTF-8
                insertBOM = false;
            }

            return BOM + data.content.replace(/&amp;/g, '&'); // data.content is HTML text converted so '&' has been converted to &amp; which is the HTML reprensetation of the '&' character. Convert it back to show '&' in the CSV.
        }
    }
});

EDIT (15.10.2020) My initial solution proposed above doesn't work when output_headerRows: true AND if the first header row has a rowspan. A colspan is ok. To handle that situation, I found a way : Before: The BOM was inserted by the function output_formatContentwhich is raised row after row (and a condition to apply the BOM only once) Now: Use the output_callback function that is raised only once at the end of the data processed, so can insert the BOM. IMPORTANT: You need tablesorter v2.25.1+ to be able to return data instead of true as before.

output_callback : function(config, data, url) {
        BOM = "\uFEFF"; // The BOM character to force Excel opens CSV as UTF-8
        return BOM + data;
    }