Buttons export to CSV doesn't seem to take charset setting into account

Buttons export to CSV doesn't seem to take charset setting into account

wooocashwooocash Posts: 1Questions: 1Answers: 0

In following example:

https://jsfiddle.net/s4qrtpnp/

When exporting the table via Buttons extension to CSV I've set the charset to be iso-8859-1.

However the exported CSV file is still in UTF-8 causing a pound character (£) to appear as "ÂŁ" when opening directly in Excel.

Do you know what exactly charset setting does? As it doesn't seem to do what's described on this page:

https://datatables.net/reference/button/csvHtml5

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Interesting. Button's uses:

    new Blob( [output], {type: 'text/csv'+charset} ),
    

    So basically the charset it set by whatever is defined.

    Your example doesn't work because the JSFiddle page is UTF-8 (which you can check by opening your browser's console and typing document.characterSet).

    Button's doesn't do any explicit character set conversion - that's outside the scope of that software. The charset option is for cases where the document's charset can't be automatically detected (which should never be the case) - it's probably redundant as an option.

    Allan

  • StefanRStefanR Posts: 12Questions: 2Answers: 2

    I'm facing the same problem - A character appended to £ sign when opened in Excel.
    Is there a particular charset I should use on the button to fix this?

  • StefanRStefanR Posts: 12Questions: 2Answers: 2
    Answer ✓

    Answering my own question, this worked for me

    extend: 'csv',
    charset: 'UTF-16LE',
    fieldSeparator: '\t',
    bom: true

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Thanks for posting back - good to hear you have that working. Worth noting that some text Editors don't like UTF-16LE and might show the BOM.

    Allan

  • cromartie1984cromartie1984 Posts: 2Questions: 0Answers: 0
    edited March 2017

    Hi,
    my colleague can read the csv in his excel, but the accent (french character) does not appear correctly, why ?
    I tried everything, with "bom", without "bom"
    i tried charset : 'utf-8' or 'iso-8859-1', 'UTF-16LE'
    i must export it with the fieldSeparator : ';'

    Has anyone encountered the same problem ?

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Is it present in the CSV file if you view it in a UTF8 text Editor? If so, then its an Excel issue.

    Allan

  • cromartie1984cromartie1984 Posts: 2Questions: 0Answers: 0

    yes it is present, so i guess it's an excel issue

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    I think you need to import such files in Excel rather than "open" them.

    Either way, Excel's handling of CSV files cause many headaches!

    Allan

  • rikerike Posts: 3Questions: 0Answers: 0
    edited May 2017

    Hi,

    I've got a very similar problem. My website is using UTF-8. For sure, one should not have to modify the charset, but I want to be able to export CSV files for people who use french Windows OS with a french Excel version. One might not believe it, but these programs assume that french CSV files are encoded in ISO-8859-1 and will show faulty accents otherwise.

    So I've tried to use 'charset: "iso-8859-1"' as a config variable when I call Buttons/csvHtml5. EDIT: This config is taken into account.

    I've also tried to modify my HTML page header to match one of these charsets as the code in datatables seems to indicate that this is taken into account.
    I tried with <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> and <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> - the first one is supposed to be HTML4 compatible, the second one HTML5. However, this does not have any effect. I did it for debugging only anyway.
    In both cases, console.log(document.charset) and console.log(document.characterSet) "UTF-8" is returned - at least in latest Chromium and Firefox.

    EDIT: the config.charset seems to be taken into account when using ISO-8859-1. But the resulting file is still using UTF-8. I'm unsure if this is due to some code in __saveAs._

    Any ideas how this might be solved?

    Cheers!

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Could you check if the file has a BOM? It shouldn't be default from the export, but it would be worth checking if that is the case.

    Do you have a link to the page?

    Allan

  • Yves5944Yves5944 Posts: 1Questions: 0Answers: 0
    edited April 2019

    Hi,
    I'd the same problem.
    This worked for me :

                    buttons: [
                        {
                            extend: 'csv',
                            text: 'Export csv',
                            charset: 'utf-8',
                            extension: '.csv',
                            fieldSeparator: ';',
                            fieldBoundary: '',
                            filename: 'export',
                            bom: true
                        }
                    ]
    

    Note you have to strip the fieldBoundary, excel doesn't like them ...

  • SHENRYSHENRY Posts: 1Questions: 0Answers: 0

    I solved the problem by adding charset = "utf8" in the call of js of datatables in my pages's head

    example

  • PixeloPixelo Posts: 9Questions: 3Answers: 0

    Thx Yves5944

  • GODYXHGODYXH Posts: 1Questions: 0Answers: 0
    edited November 2019

    I used jconv to fixed this problem

    import jconv from 'jconv'

     customize: function (csv) {
       // データを行毎に分割する
       var splitCsv = csv.split('\r\n')
       $.each(splitCsv.slice(1), function (index, csvRow) {
         // データを列毎に分割する
         var csvCellArray = csvRow.split(',')
         $('#myGrid tbody tr').each(function (i, d) {
           if ( d.cells.length > 1 && i === index) {
             csvCellArray[6] = d.cells[5].children[0].innerText
             csvCellArray[7] = d.cells[6].children[0].innerText
             csvCellArray[8] = d.cells[7].children[0].children[0].value
             csvCellArray[9] = d.cells[8].children[0].children[0].value
           }
         })
         var csvCellArrayQuotes = '' + csvCellArray.join(',') + ''
         splitCsv[index + 1] = csvCellArrayQuotes
       })
       csv = splitCsv.join('\r\n')
       csv = jconv.convert(csv,'UTF-8','Shift_JIS')
       // alert(csv);
       return csv
     }
    
  • oxcoreoxcore Posts: 11Questions: 1Answers: 0

    @Yves5944 thanks for sharing, work amaizng!

This discussion has been closed.