Excel UDF breaks special characters in 3.3.14.2 (worked in 3.3.12.0)

[Thomymaster]

Hi

 

For the purpose of converting a previously created csv-file to .xlsx i use the following code:

 

FileCopy($sExportFileName,@TempDir & "\tmp.txt")    ; the key here is not to use .csv because then Excel will behave dumb
            Local $oExcel=_Excel_Open(True)
            Local $oWorkBook=_Excel_BookOpenText($oExcel,@TempDir & "\tmp.txt",Default,Default,Default,Default,$sDelimiter)
            Local $sWorksheet=StringRegExpReplace(StringRegExpReplace(@TempDir & "\tmp.txt", '([\\ \w:]*)\\', ''), '\..*', '')
            With $oExcel.Worksheets($sWorksheet)
                .Columns("A:Z").AutoFit     ; auto-adjust the column width
            EndWith
            _Excel_BookSaveAs($oWorkBook,$sExportFileName,$xlOpenXMLWorkbook,True) ; xlOpenXMLWordbook -> .xlsx, overwrite file if it exsists
            _Excel_Close($oExcel,False) ; closes the instance and all opened workbooks, quit without saving
            FileDelete(@TempDir & "\tmp.txt")

$sExportFileName represents the csv-file (which is is exported using _FileWriteFromArray() using ANSI encoding

This worked perfectly in 3.3.12.0, but now in 3.3.14.2 the special characters (german umlauts, like ö, ä) are broken (output i.e. kö).

When look at the excel output after _Excel_BookOpenText() the content is already shown up broken.

 

Has anybody a clue what happened and how i can fix this?

 

Best,

Thomas

 

[jchd]

This is UTF8 (Unicode) text format, not ANSI. There has been a change in default text encoding recently. UTF8 is preferred since it doesn't perform wrong or incomplete "conversion" between codepages.

[Thomymaster]

Hi

 

Ok how do i solve this now, do you have an idea?

 

-FileWriteFromArray() will produce ANSI encoded files (opened with Notepad++) unless i use FileOpen() with:

Local $hOutFile=FileOpen($sExportFileName,BitOR($FO_OVERWRITE,$FO_UTF8))

Then, $sExportFileName is UTF-8 encoded, but the code in my first post will render the content/special characters unusable again.

 

Best,

Thomas

Edited April 19, 2016 by Thomymaster

[jchd]

Can't test here, no MS Office installed.

[Thomymaster]

Hi

 

There must be something wrong with the included AutoIT functions.

If i product a UTF-8 encoded csv-file and manually import it into excel, the umlauts are there. Then i can save the file to a .xlsx.

So i assume that _Excel_BookOpenText()  breaks the umlauts.

Can anybody confirm this or am i making something wrong here?

[AutoBert]

@Thomymaster: post a small CSV you think unlaute are ok and _Excel_BookOpenText breaks them by importing the CSV.

[Skysnake]

I can test. Win10 + MSO. Please upload a text file?

[Thomymaster]

Hi

 

Here is the test csv-file that doesn't work. Its UTF-8 encoded

test.csv

[jchd]

test.csv

Your file is UTF8 + BOM encoded. Can you try without BOM like this one?

 

 

Edited April 22, 2016 by jchd

[Thomymaster]

Hi

 

Doesn't make any difference, if the file is encoded as "UTF-8 AS ANSI", the function calls still break the umlauts.

[Iczer]

Excel doesn't handle UTF-8 characters very well. It should be UTF-16LE.

See here

[AutoBert]

unfortunately a upgrade to Win10 has disabled my MS Office 2013 Home and Student. Now i have to wait till tomorow for reinstall. But i had a look in the excel.au3 and can't find anything causes the error with german umlaute. The error also occurs when open the CSV direct in Excel?  Also Deutsch (Deutschland) is the actual language setting for your keyboard while trying to open the CSV?

[water]

I'm glad it is not the Excel UDF

[Thomymaster]

Hi

 

Yes, german is the keyboard layout and the language settings. Thats total strange because this worked in the previous version auf AutoIT (3.3.12.0) only after the upgrade to 3.3.14.2 its broken.

 

Ill try with the

Local $hOutFile=FileOpen($sExportFileName,BitOR($FO_OVERWRITE,$FO_UTF16_LE ))

and report back

[AutoBert]

My Office is reinstalled, so i try tomorow morning my luck. But why not downgrading to 3.3.12.0?

Edited April 25, 2016 by AutoBert

[Thomymaster]

Hi

 

I hit a bug in 3.3.12.0 and thus had to upgrade:

 

Cheers

Thomy

[AutoBert]

I have tested this CSV:   Test5.csv  (UTF-16LE) with this script:

#include <excel.au3>

Global $sFileName = 'Test5.csv'
Global $sExcelName = StringReplace($sFileName, '.csv', '')

Global $aField1[2] = [1, $xlGeneralFormat]
Global $aField2[2] = [2, $xlTextFormat]
Global $aField3[2] = [3, $xlGeneralFormat]
Global $aField4[2] = [4, $xlTextFormat]
Global $aFieldInfo[4] = [$aField1, $aField2, $aField3, $aField4]

Global $oExcel = _Excel_Open(True)
Global $oWorkBook = _Excel_BookOpenText($oExcel, $sFileName, 1, $xlDelimited, $xlTextQualifierDoubleQuote, True, ', ', $aFieldInfo)
_Excel_BookSaveAs($oWorkBook, $sExcelName, $xlOpenXMLWorkbook, True) ; xlOpenXMLWordbook -> .xlsx, overwrite file if it exsists

and all Umlaute are well: Test5.xlsx Here the consoleoutput:

>"C:\Program Files\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:\Users\Bert\AutoIt3.My\Temp\excelcsv.au3" /UserParams    
+>14:20:39 Starting AutoIt3Wrapper v.14.801.2025.0 SciTE v.3.4.4.0   Keyboard:00000407  OS:WIN_81/  CPU:X64 OS:X64    Environment(Language:0407)
+>         SciTEDir => C:\Program Files\AutoIt3\SciTE   UserDir => C:\Users\Bert\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper   SCITE_USERHOME => C:\Users\Bert\AppData\Local\AutoIt v3\SciTE 
>Running AU3Check (3.3.14.2)  from:C:\Program Files\AutoIt3  input:C:\Users\Bert\AutoIt3.My\Temp\excelcsv.au3
+>14:20:39 AU3Check ended.rc:0
>Running:(3.3.14.2):C:\Program Files\AutoIt3\autoit3.exe "C:\Users\Bert\AutoIt3.My\Temp\excelcsv.au3"    
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
+>14:20:43 AutoIt3.exe ended.rc:0
+>14:20:43 AutoIt3Wrapper Finished.
>Exit code: 0    Time: 4.812

Running on win 10 x64 with a fresh installed Office2013 version:

When changing line #13 to

Global $oWorkBook = _Excel_BookOpenText($oExcel, $sFileName, 1, $xlDelimited, $xlTextQualifierNone, True, '|', $aFieldInfo)

it works with your prefered format, used in this CSV: Thomymaster.csv

also encoded in UTF16 Little Endian.

Edited April 26, 2016 by AutoBert
script and csv changed

[Thomymaster]

Hi

 

I finally fixed that, thanks a lot.

The bug was not in the Excel UDF, instead the key was to use UTF16LE as the encoding for the "temp.txt" file that will be read into Excel. Then it all worked fine with the german umlauts

 

Cheers,

Thomy