Encode symbol when creating csv file

user_13081442 0
Viewed 108

In my web page, I can export data into a csv file that we can open and read in Excel. But I noticed that every symbol like ô or Ø are not supported. For example, instead of Ø, I have Ø.

I would like to prevent this but I can't do it at the moment.

app.js :

app.post("/exportLibelles", function(req, res) {
    try {
        dao.exportLibelle().then((value) =>  {
            res.attachment(__dirname+'/ExportLibelle.csv');
            res.status(200).send(value);
        })

    } catch (e) {
        console.log(e);
    }
})

The exportLibelle function :

async exportLibelle() {
  try {
    let request = "SELECT * FROM PROFACE.dbo.ParametresMachines;"

    let results = await sql.query(request);

    return this.convertToCSV(JSON.stringify(results.recordset))

  } catch (e) {
    console.log(e);
  }
}

convertToCSV(objArray) {
  var array = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;
  var str = '', header = '';

  for (var i = 0; i < array.length; i++) {
      var line = '';

      for (var index in array[i]) {
          if (i == 0) {
              if (header != '') {
                 header += ';'
              }
              header += index;
          }
          if (line != '') {
            line += ';'
          }
          line += array[i][index];
      }
      str += line + '\r\n';
  }
  return (header + '\r\n' + str);
}

I tried to use encodeURI() or encodeURIComponent() like this :

convertToCSV(objArray) {
        var array = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;
        var str = '', header = '';

        for (var i = 0; i < array.length; i++) {
            var line = '';

            for (var index in array[i]) {
                if (i == 0) {
                    if (header != '') {
                    header += ';'
                    }
                    header += index;
                }
                if (line != '') {
                line += ';'
                }
                line += array[i][index];
            }
            str += line + '\r\n';
        }

        let csv = header + '\r\n' + str;
        csv = 'data:text/csv;charset=utf-8,%EF%BB%BF;' + csv;
        return encodeURI(csv);
    }

With this code, when I open the csv file, all the data are on one row and the words with symbol look like this Tampon%20GO%20%C3%98%203.8%20&%2012 instead of Tampon GO Ø 3.8 & 12.

express node.js javascript
1 Answers

Had a pretty similar issue recently and was able to solve it by prepending the utf-8 bom (\ufeff) to the csv-string. Only with that Excel was able to correctly interpret it.

So in your case try:

convertToCSV(objArray) {
  //...
  return ('\ufeff'+ header + '\r\n' + str); 
}

and in your controller:

dao.exportLibelle().then((value) =>  {
      res.setHeader('Content-disposition', 'attachment; filename=ExportLibelle.csv');
      res.set('Content-Type', 'text/csv');
      res.status(200).send(value);
})
user_37616280
Related