Excel CSV export encoding

Question

According to my tests, Excel on Mac, when asked to save data as CSV file, uses MacRoman encoding. My questions are:

1. Does it always happen or there is some setting that controls it?
2. If there's a setting, is there a way to make it save as UTF-8? If not, is there a way to export Excel file with characters not in MacRoman preserving these characters?

Answer 1

I have found a blog post which outlines an Applescript solution to a similar problem. I've adapted it to your needs. Note that I haven't done any proper quoting of cell values or anything, it will need some tweaking to be more robust. It's also not the fastest solution in my minimal testing… but it's a start.

The key is write X to openFile as «class utf8»

tell application "Microsoft Excel"
    activate

    set outFile to (path of active workbook)
    set fileName to (name of active workbook)
    set outFile to (outFile & ":" & fileName & ".csv")
    set openFile to open for access file outFile with write permission
    set eof openFile to 0

    set lastCol to count of columns of used range of active sheet
    set lastRow to count of rows of used range of active sheet

    repeat with r from 1 to lastRow
        set rowStr to (value of cell r of column 1 of active sheet)
        repeat with c from 2 to lastCol
            set cellVal to (value of cell r of column c of active sheet)
            set rowStr to rowStr & "," & cellVal
        end repeat
        write rowStr & return to openFile as «class utf8»
    end repeat
    close access openFile    
end tell

Answer 2

I found the easiest thing to do was to just let excel export to csv in its own dumb way, then open up a terminal and do the following:

iconv -t UTF8 -f MACROMAN < file.csv > file-utf8.csv

Answer 3

Having done a fair amount of research on this I think I can safely say that you can't change the export encoding. However what you could do is add a step to your export procedure whereby after saving the csv to disk you could open it in a text editor (I used Coda but quite a few others will do this) and use that to then save it out using another encoding.

Answer 4

Why not just simply open the created file in TextEdit and then use cmd+shift+S to save a duplicate and then choose from dropdownlist UTF-8?

The big advantage is that you do not have to know explicit the encoding created by Excel like in method proposed by Bart. For my language (polsih) it was not Macroman, but some variation of it.

Answer 5

Simply use an editor like "Editra" - Open the csv-file - Change the encoding, e.g. to UTF-8, by klicking the encoding-type in the status bar of editor window - Save the file again (it is now UTF-8) - Done :o)

Answer 6

Use Open Office to save the csv file. There you can choose the encoding.