As reported here:
http://stackoverflow.com/questions/6588068/which-encoding-opens-csv-file...
There is no way to open a UTF-8 csv file correctly with Excel on UNIX and Windows.

Is there any way to provide an option in the display? I found a workaround that I implemented in several projects already, but an option itself would be great!

Workaround code - in views_data_export_plugin_display_export.inc:

  protected function outputfile_create() {

    $dir = 'temporary://views_plugin_display';

    // Make sure the directory exists first.
    if (!file_prepare_directory($dir, FILE_CREATE_DIRECTORY | FILE_MODIFY_PERMISSIONS)) {
      $this->abort_export(t('Could not create temporary directory for result export (@dir). Check permissions.', array ('@dir' => $dir)));
    }

    $path = drupal_tempnam($dir, 'views_data_export');

//----hack - add UTF-8 BOM for MS Excel use----
    file_put_contents($path, chr(0xEF).chr(0xBB).chr(0xBF));
//--------------------------------------------------------------

    // Save the file into the DB.
    $file = $this->file_save_file($path);

    return $file->fid;
  }
CommentFileSizeAuthor
#46 views_data_export-junk-chars-1701018-45.patch355 bytessangeetharaog
#30 views_data_export-add_optional_BOM_for_CSV-1701018-30.patch3.73 KBdasha_v
#26 views_data_export-Set_the_UTF8_BOM_for_CSV-1701018-21.patch456 bytesdasha_v
#21 views_data_export-Set_the_UTF8_BOM_for_CSV.patch715 bytesAhmadZain
#34 views_data_export-add_optional_BOM_for_CSV-1701018-34.patch1.25 KBAhmadZain
#14 views_data_export-set_utf8_bom-1701018-13.patch880 bytesstockliasteroid
#12 views_data_export-set_utf8_bom-1701018-12.patch757 bytesbkonetzny
#3 views_data_export-set_utf8_bom-1701018-3.patch749 bytessomersoft
#2 views_data_export-set_utf8_bom-1701018-2.patch766 bytessomersoft
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

dropert’s picture

dropert CreditAttribution: dropert commented

I had the exact same problem. Unfortunately, this hack didn't work out for me because of code changes in the version i recently downloaded (7.x-3.0-beta6). I tried to adjust the code, adding the BOM in front of the CSV string in the file_put_contents method. I tried several other solutions adding the BOM in front of the header in the template file but again with no success. My solution was (i had german characters in my csv file) selecting the "ASCII "encoding" in the display and changing the charset of the iconv function (views_data_export.theme.inc) to "ISO-8859-1". It does the job but why doesn't this module use php's own fputcsv function instead?

somersoft’s picture

somersoft CreditAttribution: somersoft commented
Status: Active » Needs review
FileSize
views_data_export-set_utf8_bom-1701018-2.patch766 bytes

Here is a patch file for 7.x-3.0-beta6 with the adding of the BOM conditional and also in a different place as I found this to work better.

somersoft’s picture

somersoft CreditAttribution: somersoft commented
FileSize
views_data_export-set_utf8_bom-1701018-3.patch749 bytes

Here is the version of the patch file with the correct line endings.

Status: Needs review » Needs work

The last submitted patch, views_data_export-set_utf8_bom-1701018-3.patch, failed testing.

somersoft’s picture

somersoft CreditAttribution: somersoft commented

Changing the Version from 7.x-3.x-dev to 7.x-3.0-beta6 so that the automatic test will pass as the test are not a part of that release and the patch file does not patch the test files.

somersoft’s picture

somersoft CreditAttribution: somersoft commented
Status: Needs work » Needs review

#3: views_data_export-set_utf8_bom-1701018-3.patch queued for re-testing.

Status: Needs review » Needs work

The last submitted patch, views_data_export-set_utf8_bom-1701018-3.patch, failed testing.

bkonetzny’s picture

bkonetzny CreditAttribution: bkonetzny commented

Applied the patch from #3 to my local 7.x-3.0-beta6 installation and export works now as expected on windows.

killua99’s picture

killua99 CreditAttribution: killua99 commented

Needs reroll ... some code change.

jjcarrion’s picture

jjcarrion
Primary language Spanish
Location Spain
CreditAttribution: jjcarrion commented
Issue summary: View changes

Applied the patch from #3 and works ok on windows.

jide’s picture

jide CreditAttribution: jide commented
Status: Needs work » Reviewed & tested by the community

RTBCing.

bkonetzny’s picture

bkonetzny CreditAttribution: bkonetzny commented
FileSize
views_data_export-set_utf8_bom-1701018-12.patch757 bytes

Re-rolled patch against latest dev.

Status: Reviewed & tested by the community » Needs work

The last submitted patch, 12: views_data_export-set_utf8_bom-1701018-12.patch, failed testing.

stockliasteroid’s picture

stockliasteroid CreditAttribution: stockliasteroid commented
FileSize
views_data_export-set_utf8_bom-1701018-13.patch880 bytes

This fix works, but this patch won't work if it's a batched export, which is going to be common with any large files. That's because views_data_export_plugin_display_export calls the render_header, render_body, and render_footer methods directly in a batch run, and skips the render() call where this change resides. My fix was to move this code into render_header() instead, since that gets called in both cases.

Grimreaper’s picture

Grimreaper
Primary language French
Location France 🇫🇷
CreditAttribution: Grimreaper commented

Hello,

Patch in comment #14 solved the problem for me.

Thank you very much.

Alan D.’s picture

Alan D. CreditAttribution: Alan D. commented
Status: Needs work » Needs review

Status: Needs review » Needs work

The last submitted patch, 14: views_data_export-set_utf8_bom-1701018-13.patch, failed testing.

Nicolas Bouteille’s picture

Nicolas Bouteille CreditAttribution: Nicolas Bouteille commented
Status: Needs work » Reviewed & tested by the community

My client reported encrypted characters on Windows with a CSV file. Patch fixes the issue thanks!

Steven Jones’s picture

Steven Jones CreditAttribution: Steven Jones at ComputerMinds commented
Status: Reviewed & tested by the community » Needs work

Tests are failing with this patch, sorry!

lazzyvn’s picture

lazzyvn CreditAttribution: lazzyvn commented

i confirme patch #14 works fine

But i think we have to add option UTF8 with BOM in config

AhmadZain’s picture

AhmadZain CreditAttribution: AhmadZain as a volunteer and commented
FileSize
views_data_export-Set_the_UTF8_BOM_for_CSV.patch715 bytes

After searching I managed to solve this issue inside the template it self.

colepacak’s picture

colepacak CreditAttribution: colepacak commented

#21 worked for me. Thanks, AhmadZain.

circuscowboy’s picture

circuscowboy CreditAttribution: circuscowboy commented
Status: Needs work » Reviewed & tested by the community

The template solution is great because I don't have to even wait for a commit or deal with a patch. It works as advertise and I would recommend that this gets added to the module. I would also argue that this is more of a bug then a feature. Thanks for your solution AhmadZain

Status: Reviewed & tested by the community » Needs work

The last submitted patch, 21: views_data_export-Set_the_UTF8_BOM_for_CSV.patch, failed testing.

The last submitted patch, 21: views_data_export-Set_the_UTF8_BOM_for_CSV.patch, failed testing.

dasha_v’s picture

dasha_v CreditAttribution: dasha_v commented
FileSize
views_data_export-Set_the_UTF8_BOM_for_CSV-1701018-21.patch456 bytes

Re-formatting patch #21 for testing.

davidhernandez’s picture

davidhernandez
Primary language English
Location USA
CreditAttribution: davidhernandez at FFW commented
Status: Needs work » Needs review

Status: Needs review » Needs work

The last submitted patch, 26: views_data_export-Set_the_UTF8_BOM_for_CSV-1701018-21.patch, failed testing.

The last submitted patch, 26: views_data_export-Set_the_UTF8_BOM_for_CSV-1701018-21.patch, failed testing.

dasha_v’s picture

dasha_v CreditAttribution: dasha_v at FFW commented
Status: Needs work » Needs review
FileSize
views_data_export-add_optional_BOM_for_CSV-1701018-30.patch3.73 KB

Updated patch.
Made BOM optional and configurable per view.
By default it is disabled, this should resolve issue with existing tests.
Added new test for this BOM parameter.

weri’s picture

weri CreditAttribution: weri at Previon Plus AG commented

I applied and tested the patch #30. It works as expected but I've not done a code review (yet).

amme’s picture

amme CreditAttribution: amme commented

Confirm patch #30 works

NWOM’s picture

NWOM CreditAttribution: NWOM commented
Status: Needs review » Reviewed & tested by the community

#30 applied cleanly and worked as expected. Thank you. I think we can mark this as RTBC.

AhmadZain’s picture

AhmadZain CreditAttribution: AhmadZain as a volunteer and commented
FileSize
views_data_export-add_optional_BOM_for_CSV-1701018-34.patch1.25 KB

Hi sorry for being away

thanks @colepacak and @circuscowboy actually I am still confirming like you guys but I can not get the main reason that makes the patch fail to pass the test, so I reformated the patch and hope that can be confirmed from all sides.

#1701018: CSV export UTF-8 & windows - BOM Ref. About BOM fix in CSV views export.

@dasha_v I removed your patches from this one as you mentioned it in a separate comment and thanks for your support.

Best

The last submitted patch, 34: views_data_export-add_optional_BOM_for_CSV-1701018-34.patch, failed testing.

The last submitted patch, 34: views_data_export-add_optional_BOM_for_CSV-1701018-34.patch, failed testing.

NWOM’s picture

NWOM CreditAttribution: NWOM commented
Status: Reviewed & tested by the community » Needs work
AhmadZain’s picture

AhmadZain CreditAttribution: AhmadZain as a volunteer and commented

HI @NWOM

can you just add a comment to this to make it clear ? is it the testing is not covered or what ?

Thanks in advance.

Zain

NWOM’s picture

NWOM CreditAttribution: NWOM commented
Status: Needs work » Needs review

@AhmadZain Hello, sorry about not commenting. I had meant to set it to "Needs Review", since a new patch was added that had not been reviewed yet. I was just trying to help with the normal patch submission workflow. Normally every patch that gets added needs to be tested by others before being set to "Reviewed & tested by the community", however I instead chose the wrong status as well :P

AhmadZain’s picture

AhmadZain CreditAttribution: AhmadZain as a volunteer and commented

@NWOM no worries. ;)

thanks for your replay

AhmadZain’s picture

AhmadZain CreditAttribution: AhmadZain as a volunteer and commented

Hi All,

Is there any person is looking after this project ?

Best

RoSk0’s picture

RoSk0
he/him
Primary language Russian
Location Wellington
CreditAttribution: RoSk0 at Catalyst IT for Land Information New Zealand commented
Status: Needs review » Reviewed & tested by the community

Patch from #30 applies cleanly and allows to fix an issue in proper way.

I would not recommend to go with approaches form other patches because they are not configurable and have some strange logic.

burshyn’s picture

burshyn CreditAttribution: burshyn commented

Patch from #30 work for me! Thanks dasha_v!

hgoto’s picture

hgoto CreditAttribution: hgoto at Studio Umi commented

I also tested the patch #30 and it works well.

I'm confused. I don't understand why the patch #34 was proposed after the patch #30. The patch #30 has configuration and tests and looks better for me...

Anyway, thank you. +1 for RTBC :)

sangeetharaog’s picture

sangeetharaog CreditAttribution: sangeetharaog as a volunteer commented

I tried patch #30 but it did not work for me. As analyzing the patch I found another way to print print "\xEF\xBB\xBF"; .

This works for me.

sangeetharaog’s picture

sangeetharaog CreditAttribution: sangeetharaog as a volunteer commented
FileSize
views_data_export-junk-chars-1701018-45.patch355 bytes
aschiwi’s picture

aschiwi CreditAttribution: aschiwi at undpaul commented

Adding my experience: the patch in #30 still works for me, thank you!