0

In my application I have a list of items which can be exported to CSV.

For this, I create a Blob as follows:

var BOM = "\ufeff";
var blob = new Blob([csv], {
        type: 'csv;charset=utf-8'
});

In case that the data in this list contains special characters, the exported file was not opened correctly in MS Excel. So I added a line to my code (the second line in the following snippet), as I found in many Q&A forums:

 var BOM = "\ufeff";
 var csv = BOM + csv;
 var blob = new Blob([csv], {
          type: 'csv;charset=utf-8'
 });

That works - the CSV is opened correctly in Excel, but then, when saving the file - it is save in text format and not as CSV. Which meant I need to "Save As" the file and change the default type if I want it to be saved correctly.

Is it really like this? Do I really have to choose between the two options - see the file or save it correctly?

user2717436
  • 775
  • 1
  • 10
  • 23

1 Answers1

1

Yes this is a shame but it is really like this. From Excel a CSV is ANSI encoded per default and there is not a directly possibility to save CSV in any unicode encoding. Microsoft itself suggest using Notepad to change the encoding. See How to save an address book to a CSV file by using the UTF-8 encoding format so that the CSV file can be imported to Windows Mail. See also How can I save a csv with utf-8 encoding using Excel 2013?

Only other possibility is using VBA and create the CSV file using ADODB.Stream or Scripting.FileSystemObject.

How to use ADODB.Stream to create unicode encoded CSV file is answered multiple times already. For example: how to Export excel to csv file with "|" delimted and utf-8 code. Simply change the delimiter "|" to ",". This is the basic approach. Maybe you have to extend it to provide text delimiter also, if the delimiter can be part of the data.

Using CreateTextFile Method of Scripting.FileSystemObject is simpler but only allows Unicode which is UTF-16LE rather than UTF-8.

Community
  • 1
  • 1
Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • Can you please elaborate a little more about the other possibility you suggested here? – user2717436 Jan 25 '17 at 11:32
  • OK I now understand. That means I will need to write my code in VBA. in JavaScript I have no other option but to teach my users how to open \ save the exported files? – user2717436 Jan 25 '17 at 12:10
  • Related: [saving text as UTF-8 from Excel](http://stackoverflow.com/a/9093957/11683) – GSerg Jan 25 '17 at 12:12
  • @user2717436: How to teach Excel to be able opening a UTF-8 encoded CSV properly you have found already: give it a BOM. How to teach the user to be able saving a UTF-8 encoded CSV file from Excel is impossible since Excel can't do this. It will either save CSV ANSI encoded or TXT tabulator delimited and Unicode (UTF-16LE) encoded. So yes for the save part the only possibility is to extend Excel's programming using VBA or whatever. – Axel Richter Jan 25 '17 at 12:21