CSV files not Unicode/UTF-8 aware

Marked http://drupal.org/node/210649 as duplicate.

It turns out the downloaded CSV files have the wrong text encoding, which is what causes all the weirdness with Excel. Strangely, I can open the file in TextEdit (on a Mac) and the file looks fine. Opening the same file with BBEdit or Excel ends up with the screwy characters.

I did a bit of research on this and it seems that there's nothing that Webform can do on it's end. CSV inherently only supports ASCII text (without special characters). Changing the download headers from UTF8 to ASCII (or other encodings) doesn't have any effect. So as it is, the correcting needs to happen on the side of the desktop application to read in the file correctly.

This could be fixed in different approach. I changed the extension of the file from .csv to .txt in webform_report.inc to be: drupal_set_header("Content-Disposition: attachment; filename=". preg_replace('/\.$/', '', str_replace(' ', '_', $node->title)) .". txt");, and it works since text files is UTF-8 aware. Then I used the text import wizard at Excel to import data to a worksheet. May be the design of submissions need to be rearranged.

Does this change work?

This sounds like another work-around. If you're going to manually be importing the CSV through the wizard, you could probably change the name of the file from .csv to .txt after downloading anyway. It should still work even after downloading the file. Webform is properly setting the headers on download, it just happens that Excel refuses to read a CSV file directly and use UTF-8 characters.

Yes ... you are right. I downloaded .csv file and opened it using Notepad and it works. It is the same as .txt file. ... it just happens that Excel refuses to read a CSV file directly and use UTF-8 characters. that is exactly what happens!

So as you mentioned before ... the correcting needs to happen on the side of the desktop application to read in the file correctly ...

Thanks quicksketch

Thanks for the confirmation Marat. I tested it pretty extensively when I looked into this, but all on a Mac. Thanks for backing up the conclusion for Windows also. So... let's summarize: Excel doesn't read UTF-8 CSV files :P

Note that this isn't technically a bug on Excel's part, since the CSV spec itself doesn't allow for UTF-8 characters. It might be very likely then that this bug will never be corrected.

> So... let's summarize: Excel doesn't read UTF-8 CSV files :P

OpenOffice does :) and you may save as .xls

I was researching this further recently, as I found that Excel can easy import a CSV file with non-latin characters as long as Excel exports it. This revealed that Excel exports in "MacRoman" encoding on Macs, and "Windows-1252" on Windows. Great. So Excel *can* import CSV files with non-latin characters, as long as you stick to the encoding used on each platform.

However! I found some more interesting information, such as Excel can read UTF-16LE (little endian) on both Macs and PCs, but all the results are shifted into a single column. Using the Excel "Text to Columns" function easily corrects this however.

Considering it's much harder to get the encoding correct than it is to use "text to columns" I've changed Webform's downloads to use UTF-16LE. I also found that Excel works *very* well if using tabs instead of commas in the download (also in UTF-16LE), so I've changed the default delimiter to a tab instead of a comma, since it seems to work universally.

As a super-bonus, Excel will read these tab-delimited files just fine when given a .xls extension (though Office 2007 will complain that the file contents don't match the extension). Anyway, all these new changes will be in the 2.2 version for everyone to try out.