38

I'm currently developing CSV export with XSLT. And CSV file will be used %99 percent with Excel in my case, so I have to consider Excel behavior.

My first problem was German special characters in csv. Even fact that CSV encoding is UTF8, Excel cannot open properly CSV file with UTF8. The special characters are getting weird symbols. I found a solution for this problem. I just added 3 additional bytes(EF BB BF - a.k.a BOM Header) beginning of content bytes. Because UTF8 BOM is way to say that 'hey dude, it is UTF8, open it properly' to Excel. Problem solved!

And my second problem was about separator. The default separator could be comma or semicolon depending on region. I think it is semicolon in Germany and comma in UK. So, in order to prevent this problem, I had to add the line in below:

<xsl:text>sep=;</xsl:text>

or

<xsl:text>sep=,</xsl:text>

(This separator was not implemented as hard-coded)

But my problem which I cannot find any solution is that if you add "sep=;" or "sep=," beginning of the file while the CSV file is being generated with UT8-BOM, the BOM doesn't help for showing special characters properly anymore! And I'm sure that BOM bytes are always in the beginning of byte array. This screen shot is from MS Excel in Mac OS X:

enter image description here

First 3 symbols belong to BOM header.

Have you ever had like this problem or do you have any suggestions? Thank you.

Edit:

I share the printscreens.

a. With BOM and <xsl:text>sep=;</xsl:text>

enter image description here

b. Just with BOM

enter image description here

The Java code:

// Write the bytes
ServletOutputStream out = resp.getOutputStream();
if(contentType.toString().equals("CSV")) {
  // The additional bytes in below is prefix indicates that the content is in UTF-8.
  out.write(239);
  out.write(187);
  out.write(191);
} 
out.write(bytes); // Content bytes, in this case XSL

The XSL code:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="text" version="1.0" encoding="UTF-8" indent="yes" />

    <xsl:template match="/">
    <xsl:text>sep=;</xsl:text>
    <table>
        ...
        </table>
</xsl:template>
Adem İlhan
  • 1,360
  • 2
  • 15
  • 26
  • 2
    What do you mean my _the BOM breaks_? Do you write out the character sequence `sep=;` as the first bytes of the file instead of the BOM header? – Marcus Rickert Dec 05 '13 at 11:56
  • Your chances for a good answer improve quite a bit if you show the input, XSLT code and expected/ actual output. – Mathias Müller Dec 05 '13 at 12:26
  • "*...many regional versions of Excel will not be able to deal with Unicode in CSV. One simple solution when encountering such difficulties is to change the filename extension from .csv to .txt; then opening the file from an already running Excel with the "Open" command.*"(http://en.wikipedia.org/wiki/Comma-separated_values) You may also consider producing a [SpreadsheetML](http://en.wikipedia.org/wiki/SpreadsheetML) type of file. – michael.hor257k Dec 05 '13 at 14:20
  • Unfortunately, it is out of scope. It has to have only CSV – Adem İlhan Dec 05 '13 at 15:01
  • 1
    Well, then you'll need to compromise on *something*. FWIW, I don't think you need to announce the separator - Excel should be smart enough to figure it out on its own. Not to mention that it's not conforming to the CSV "standard" (such as there is). I don't know what the other 1% is, but I believe quite a few applications would trip on that. – michael.hor257k Dec 05 '13 at 17:29
  • But the thing is, while you are generating CSV with XSL, you have to use comma or semicolon as separator! Let's say you used semicolon(for Germany) as a separator in XSL and user's region is UK(default separator is comma for excel), then all the columns will be in the first row, because there is no separator! Unfortunately The compromising is worst case. Maybe even there is no solution for this situation, but maybe someone has another solution for the problems that I mentioned... – Adem İlhan Dec 06 '13 at 09:17
  • Why not use a comma as the separator, and quote the fields properly? – Ben Mar 23 '14 at 15:39
  • I don't know, but I'm glad to know I'm not just going insane and someone else has this problem. – Casey Mar 05 '15 at 17:45

3 Answers3

11

You are right, there is no way in Excel 2007 to get it load both the encoding and the seperator correctly across different locales when someone double clicks a CSV file.

It seems like when you specify sep= after the BOM it forgets the BOM has told it that it is UTF-8.

You have to specify the BOM because in certain locales Excel does not detect the seperator. For instance in danish, the default seperator is ;. If you output tab or comma seperated text then it does not detect the seperator and in other locales if you seperate with semi-colon it doesn't load. You can test this by changing the locae format in windows settings - excel then picks this up.

From this question: Is it possible to force Excel recognize UTF-8 CSV files automatically?

and the answers it seems the only way is to use UTF16 le encoding with BOM.

Note also that as per http://wiki.scn.sap.com/wiki/display/ABAP/CSV+tests+of+encoding+and+column+separator?original_fqdn=wiki.sdn.sap.com it seems that if you use utf16-le with tab seperators then it works.

I've wondered if excel reads sep=; and then re-calls the method to get the CSV text and loses the BOM - I've tried giving incorrect text and I can't find any work around that tells excel to take both the sep and the encoding.

Community
  • 1
  • 1
Luke Page
  • 7,968
  • 1
  • 18
  • 22
11

This is the result of my testing with Excel 2013.

If you're stuck with UTF-8, there is a workaround which consists of BOM + data + sep=;

Input (written with UTF8 encoding)

\ufeffSome;Header;Columns
Wîth;Fàncÿ;Stûff
sep=;

Output

|Some|Header|Columns|
|Wîth|Fàncÿ |Stûff  |
|sep=|      |       |

The issue with solution is that while Excel interprets sep=; properly, it displays sep= (yes, it swallows the ;) in the first column of the last row.

However, if you can write the file as UTF16-LE, then there is an actual solution. Use the \t delimiter without specifying sep and Excel will play ball.

Input (written with UTF16-LE encoding)

\ufeffSome;Header;Columns
Wîth;Fàncÿ;Stûff

Output

|Some|Header|Columns|
|Wîth|Fàncÿ |Stûff  |
phuclv
  • 27,258
  • 11
  • 104
  • 360
Pier-Luc Gendreau
  • 11,989
  • 4
  • 51
  • 60
1

I can't write comments yet, but I'd like to address @Pier-Luc Gendreau's solution. While it is possible to open it in European Excel (which by default uses ;as delimiter) and have full utf-16LE support, it is apparently not possible to use this technique when you specify sep=,.

The issue with solution is that while Excel interprets sep=; properly, it displays sep= (yes, it swallows the ;) in the first column of the last row.

For me it did not work if I specified a delimiter which wasn't the default one (;in my case) so I assume Excel did not interpret the last line correctly and swallowed the last delimiter because this is the default behavior.

Please correct me if I'm wrong

Michi Eisele
  • 33
  • 1
  • 6
  • I don't think you're wrong but I don't think that makes my answer wrong either! It's a workaround to a very specific issue so it really depends on your exact needs. – Pier-Luc Gendreau Apr 15 '21 at 01:59