Thomymaster

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

18 posts in this topic

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

 

Share this post


Link to post
Share on other sites



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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by Thomymaster

Share this post


Link to post
Share on other sites

Can't test here, no MS Office installed.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites

Hi

 

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

test.csv

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

test.csv

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

 

 

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Hi

 

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

Share this post


Link to post
Share on other sites

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

See here

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

I'm glad it is not the Excel UDF :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

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

Edited by AutoBert

Share this post


Link to post
Share on other sites

Hi

 

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

 

Cheers

Thomy

Share this post


Link to post
Share on other sites

#17 ·  Posted (edited)

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: 571f29ce9470e_55_EigenschaftenvonEXCEL.E

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 by AutoBert
script and csv changed

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now