Export Visualforce CSV with UTF-8 (With BOM) format

Question

I am trying to export a Csv file from a Visualforce page.

I have the following:

<apex:page controller="CaseCSVController" contentType="text/csv#File.csv"  showheader="false" >

This way I export it as UTF-8 (Without BOM), thus the file opens "Broken" on Excel. Also if I edit the file and save it, the Excel changes its format to ANSI, which Salesforce doesn't accept.

If I change my code to any of those:

<apex:page controller="CaseCSVController" contentType="text/csv, charset=UTF-8#File.csv"  showheader="false" >

OR

<apex:page controller="CaseCSVController" contentType="text/csv; charset=UTF-8;#File.csv"  showheader="false" >

OR

<apex:page controller="CaseCSVController" contentType="application/vnd.ms-excel#File.csv; charset=UTF-8"  showheader="false" >

It opens correctly on Excel, but with ANSI format and again, Salesforce doesn't accept.

I must EXPORT an CSV file WITH UTF-8 characters and after the user edit some lines, IMPORT with the changes.

Mostly important: I want to export on the right format and not tell to the users convert by themselves.

Answer 1

You can make this work by including the BOM at the start of the VisualForce page.

In the VF page on the first line I have (the strange use of outputText is something I've inherited from an existing implementation of a CSV export):

<apex:outputText value="{!utf8Bom}"/><apex:outputText value="Organisation"/> 
<apex:outputText value="{!COMMA}"/>

And in the matching controller I have:

public String utf8Bom {get {return '\uFEFF';} private set;}

The contentType is set to application/vnd.ms-excel#Export.csv;charset=UTF-8.

By the time the UTF-8 CSV is output, it has the correct EFBBBF bytes at the start. In my testing (Excel 16 on the Mac) these files are opened correctly with all the non ASCII text intact without having to explicitly import them and force Excel to use UTF-8.

The users can now just double click on the files without extra steps. Once the BOM is there Excel will also save the CSV file successfully as UTF-8 after the user has edited it.

It was this Trailblazer Community post that pointed me in the right direction to get the BOM to work.

Answer 2

Best way, to do this encoding, do not open csv in Excel. Use Notepad++ or text editor to edit the csv. You will get correct encoding at Notepad++ editor

Excel is very unpredictable for this kind of formatting.

Just in case, you want to save .csv from Excel with proper encoding then during Save, select Tools, then Web Options, then in the dialog select Encoding Tab and choose correct encoding.