Text/tab or excel download of webform results not handling foreign characters correctly

What is the configuration of the server hosting your site, i.e. operating system (IIS, BSD, Linux), which database (Postgre, mySQl) and which version of PHP do you have running?

I know it's no consolation to you really, but Gmail has been battling with a similar issue since March (http://www.google.ru/support/forum/p/gmail/thread?tid=71e534bc815d6e8f&h...). Seems to be a bit of a sticky one.

As far as I know, converting the text from UTF-8 to UTF-16LE will always be necessary in order to make files readable by Excel. In my experience, all other spreadsheet applications (and most text editors) have to follow suit with the precedent set by Excel, and all of them support UTF-16LE.

I've trawled the web a bit and this seems to be some kind of arm-wrestle between Excel and the client operating systems locale settings... I think. Perhaps the best answer would be to add a global Webform downloads config option to export tsv/csv with either utf8 or utf16 encoding (similar to the long-format vs short-format for email addresses).

There are reports, also, that Excel expects/assumes a csv/tsv file to be in ascii. That probably means that utf8 *should* be okay.

Could you upload the exact .tsv and .csv files that you get out of Webform to this issue? That might help me reproduce the problem and determine if the downloaded file is the problem or if my version of Excel is just smarter than yours.

Ah good, well at least it failed on my version too. Another quick question that might help solve the problem. Do you have the mbstring extension on your server? Drupal will give you a notice if you don't have it on the admin/reports/status page. It should say, "Unicode library" with the value of "PHP Mbstring Extension".

Cool, thanks for checking. I'm going to try inputting the same information into my local and see if the export reproduces a bad file on my machine also. I'm a bit booked so it may take a while for me to confirm.

Well shoot. My computer and a couple sites I'm running on Webform all seem to export just fine. Attached is a sample text file (tsv) that I downloaded from my local machine. Your exports don't work on my computer, but I'm guessing my exports will probably work on yours. There must be some environmental difference between your installation of Webform and mine, but I don't know what that could be.

Could you run this SQL query to double-check that your database table is properly formatted as UTF-8? I have no idea if it could be causing a problem, but it's worth a check:

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "drupal7"
  AND T.table_name = "webform_submitted_data";

And this one too:

SELECT character_set_name FROM information_schema.`COLUMNS` C
WHERE table_schema = "drupal7"
  AND table_name = "webform_submitted_data"
  AND column_name = "data";

Replace "drupal7" with the name of your database. These checks are for the webform_submitted_data table, doing the same thing for webform_submissions and webform_component would also be a good idea.

I'm still at a loss for this problem. I'll need more information on how to reproduce this problem from a fresh install, but I'm not sure what more information would help reproduce the problem. :\

Results when downloading webform entries on both systems are identical.

This makes it sound like something at the Drupal level. With two completely different OS's and versions of PHP producing the same (wrong) result. I suppose one thing we haven't tried is setting up an entirely new installation of Drupal and Webform and seeing if those exports have the same problems.

Subscribing to this bug.

After every module update I comment out the mb_convert_encoding(..) lines in webform.export.inc, because all non-Windows users complain about exported data files being unreadable (csv and tab separated xls). Even for most Windows clients standard UTF-8 seems to be more reliable.

Could this UTF-16LE thing be turned into a selectable option on the download form?

Our webserver is running on Debian GNU/Linux 6 with MySQL database.

Not that this helps, but I'm also experiencing this problem with 6.x-3.17. I did the queries in #13 above and I got 'utf8' both times.

My data contains Polish characters. However, what's really strange is that this only affects my two most recent webforms. I did some other ones a couple months ago which also contain Polish characters and the CSV files generate fine (even now). I have no idea what the difference is.

Applying the patch from #16 fixes the problem for me.

Please let me know if there is any way I can contribute to fixing this!

Hey @dsnopek, the biggest problem we have is that we don't know what causes the characters to be corrupted. I personally have never seen the corrupted characters on any site I run, making solving the problem difficult. Since this problem just started happening recently to you, you may be uniquely positioned to figure out when it started and what caused it.

Could you check with your hosting company if any recent upgrades were made to your server? Updated versions of PHP, APC, MySQL, etc perhaps? And if so, from which version to which. Or if you made any Drupal configuration changes recently (adding new modules for example), that may also be of interest.

I'll see if I can get some info from the hosting company.

They actually just moved my account to a new server and the problem completely disappeared even without a patch. So, it's got to be a server config thing. Unfortunately, I don't have access to the old server so I can't explore the differences myself.

Yeah, I don't think I made any Drupal config changes between the webforms that worked and the ones that didn't.

They actually just moved my account to a new server and the problem completely disappeared even without a patch. So, it's got to be a server config thing.

Thanks, that in itself is something we've not be able to definitively prove. So far everyone whose had the problem hasn't found a "fix" at all, so it's good to know that the exact same Drupal site on a different server doesn't have the problem (pointing to server config like you say). Still not sure what to recommend on the whole, sounds like upgrading PHP would be a good start for most users having this problem.

This all the hosting company would give me from the old server:

$ php -version
PHP 5.2.17 (cli) (built: May  4 2011 06:02:41)
Copyright (c) 1997-2010 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2010 Zend Technologies
   with the ionCube PHP Loader v3.1.32, Copyright (c) 2002-2007, by ionCube Ltd., and
   with Zend Extension Manager v1.2.2, Copyright (c) 2003-2007, by Zend Technologies
   with Zend Optimizer v3.3.3, Copyright (c) 1998-2007, by Zend Technologies

MySQL: mysql-5.0.95-1.el5_7.1

I asked for the php.ini as well, but their response was "it's configurable" - however, I didn't configure it, I used the defaults.

On the new server its:

$ php -version
PHP 5.3.2 (cli) (built: Apr  5 2010 07:09:31) 
Copyright (c) 1997-2010 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2010 Zend Technologies

$ rpm -qa | grep mysql-server
mysql-server-5.0.95-1.el5_7.1

$ mysql --version
mysql  Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (i686) using readline 5.1

Unfortunately, that doesn't seem too helpful. MySQL appears to be the same. And PHP goes all the way from 5.2 -> 5.3, so we can't isolate a small change. I also don't know if the fact that PHP comes from an rpm has an effect, because it could be patched somehow. The OS on the new machine is "CentOS release 5.8 (Final)." I don't know about the old one.

Hope something in there ends up being useful! :-)

Regards,
David.

Thanks David! The MySQL versions being the same is great, pointing to PHP as the most likely culprit. I wonder if your previous box had MBString. I still think that's a likely source of the problem, even though @wnicklin had it installed on his site. You can check if MBString is installed from the command line with this command:

php -i | grep mbstring

If it returns anything that it is installed.

We haven't had any confirming problems of this for a year now. Did anyone else who had this problem get it resolved?

I can confirm the problem still exists in 6.x-3.18

I got a D7 multisite-installation with three sites. All sites use the same webform module and the same html.tpl.php. Two of them, based on a Bartik theme, do the excel export in UCS 2 Little Endian and ecxel can show this file correctly. The third site, based on omega framework, exports the webformresults in UTF 8, and excel cant interpret correctly. So it seems not to be the webform module that changes the coding of the exported file. It depends on something I don´t know at the moment.

Are these sites on the same server? I doubt the theme has anything to do with the exporting problem. The file export doesn't use the theme system in any way, so I don't think they're related.

It depends on something I don´t know at the moment.

Right, me neither... until this can be reliably reproduced from instructions, there's nothing I can do to fix the exported TSV files.

In the 4.x version, we've *just* added a new Excel-native exporter. It uses Excel's real file format (xlsx) to export files, so it works with UTF-8 characters, new-lines, and everything else. In my experience it also imports into LibreOffice, OpenOffice, and Google Docs better than TSV files, so long-term, I think that's going to be the solution for users having this problem. This feature was just added in #1140026: Excel-native exporter, fixing new line and UTF-8 importing problems, so it won't be available until the next 4.x release (7.x-4.x-alpha10).

Back to postponed until exact steps can be provided to reproduce. TSV/CSV files just aren't good at solving this problem (CSV files supposedly only support ASCII characters *by design*). However for most users, this hasn't been a problem. It seems to be a server-specific issue when a site is unable to produce a file with proper character encoding.