Answered Can I force Excel to automatically open a UTF-8 csv file correctly?

[progdev1]

Hi all, I have an application that outputs a csv file in the charset UTF-8 and opens it in Excel. Currently I'm having problems with Excel and UTF-8. When it opens a file with a umlaut character. The character fails to display correctly. Does anyone know if it is possible to instruct excel 07 on the client side (windows) to open the file in UTF-8 format. I've tried outputting Byte order mark to the file and outputting the following bit of code below but no use. I was wondering Has anyone come across this before and might know a solution.
<code>
output-http-header("Content-Encoding", "UTF-8").
output-http-header("content-disposition","Attachment~;filename=myfile.csv").
output-content-type ("application~/vnd.ms-excel~; charset=UTF-8":U ).
</code>

 

[Rob Fitzpatrick]

As with Progress, Excel deals with a variety of code pages. You should be able to accomplish what you want to do, although you haven't said how your application "opens it in Excel".

When you do this manually in Excel, for example in the Text Import Wizard, there is a "File origin" drop-down that allows you to select the origin code page for your text file. Depending on how your application works, you may need to set this programmatically.

Also, the font being used in your worksheet would obviously have to support the graphical characters being imported. For example even if your data imports correctly, it won't look right if all the cells are formatted with Wingdings.

 

[Rob Fitzpatrick]

Note also that a BOM is neither necessary nor recommended in a UTF-8 file. And the absence of a BOM, in and of itself, does not mean that a file is not UTF-8-encoded.

 

[progdev1]

Hi Rob thanks for your reply. My application is a webspeed application. The file is a csv file that is created on the server. The data is sent to the webtream using the following code. We don't get the format type screen because the file is comma separated as opposed to pipe. The file is definitely utf-8 as I have logged into the server and verified this.

output-http-header("content-disposition","Attachment~;filename=myfile.csv").
output-content-type ("application~/vnd.ms-excel":U ).

    DEFINE VARIABLE     vfileline       AS RAW NO-UNDO.
  

    INPUT STREAM inputfile FROM VALUE("myfile.csv") BINARY NO-ECHO.

    REPEAT:
        LENGTH(vfileline) = 1024.
        IMPORT STREAM inputfile UNFORMATTED vfileline.
        PUT {&WEBSTREAM} CONTROL vfileline.
    END. /* repeat */

    LENGTH(vfileline) = 0.

    INPUT STREAM inputfile CLOSE.

 

[Rob Fitzpatrick]

Why does the delimiter matter? As you saw from my screenshot I happen to prefer using pipe-delimiters in "CSV" files, but that is configurable and the default list separator in Excel is comma.

 

[progdev1]

There are two issues here Firstly, I don't get the popup in your screenshot asking to convert the data, because I use comma as the delimiter not a pipe. Secondly I probably wouldn't want the users using this converter as I wouldn't be confident they would use the converter correctly. I want them just to click on the link and open the file and excel to know to use utf-8. I was wondering if there is any http headers I could use to force excel 07 to use UTF-8 instead of the default currently being used. That is all.

 

[progdev1]

Hi Guys, I found the solution to this
I replaced
output-http-header("Content-Encoding", "UTF-8").
output-http-header("content-disposition","Attachment~;filename=myfile.csv").
output-content-type ("application~/vnd.ms-excel~; charset=UTF-8":U ).

with
output-http-header("content-disposition","Attachment~;filename=myfile.csv").
output-content-type ("application~/vnd.ms-excel~; charset=UTF-8":U ).

Thankfully it works.
As always thank for all your help.

 

[progdev1]

Correction the changes I mentioned above doesn't make any difference
the correct thing is to use the following line of code.
PUT {&WEBSTREAM} CONTROL "~357~273~277".
Excel recognises this byte order mark and outputs the data correctly.

output-http-header("content-disposition","Attachment~;filename=myfile.csv").
output-content-type ("application~/vnd.ms-excel":U ).

    DEFINE VARIABLE     vfileline       AS RAW NO-UNDO.


    INPUT STREAM inputfile FROM VALUE("myfile.csv") BINARY NO-ECHO.

    REPEAT:
        LENGTH(vfileline) = 1024.
        IMPORT STREAM inputfile UNFORMATTED vfileline.
        PUT {&WEBSTREAM} CONTROL "~357~273~277".
        PUT {&WEBSTREAM} CONTROL vfileline.
    END. /* repeat */

    LENGTH(vfileline) = 0.

    INPUT STREAM inputfile CLOSE.