Thomymaster Posted April 18, 2016 Share Posted April 18, 2016 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 Link to comment Share on other sites More sharing options...
jchd Posted April 18, 2016 Share Posted April 18, 2016 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
Thomymaster Posted April 18, 2016 Author Share Posted April 18, 2016 (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 April 19, 2016 by Thomymaster Link to comment Share on other sites More sharing options...
jchd Posted April 18, 2016 Share Posted April 18, 2016 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
Thomymaster Posted April 19, 2016 Author Share Posted April 19, 2016 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? Link to comment Share on other sites More sharing options...
AutoBert Posted April 19, 2016 Share Posted April 19, 2016 @Thomymaster: post a small CSV you think unlaute are ok and _Excel_BookOpenText breaks them by importing the CSV. Link to comment Share on other sites More sharing options...
Skysnake Posted April 20, 2016 Share Posted April 20, 2016 I can test. Win10 + MSO. Please upload a text file? Skysnake Why is the snake in the sky? Link to comment Share on other sites More sharing options...
Thomymaster Posted April 22, 2016 Author Share Posted April 22, 2016 Hi Here is the test csv-file that doesn't work. Its UTF-8 encoded test.csv Link to comment Share on other sites More sharing options...
jchd Posted April 22, 2016 Share Posted April 22, 2016 (edited) test.csv Your file is UTF8 + BOM encoded. Can you try without BOM like this one? Edited April 22, 2016 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
Thomymaster Posted April 24, 2016 Author Share Posted April 24, 2016 Hi Doesn't make any difference, if the file is encoded as "UTF-8 AS ANSI", the function calls still break the umlauts. Link to comment Share on other sites More sharing options...
Iczer Posted April 24, 2016 Share Posted April 24, 2016 Excel doesn't handle UTF-8 characters very well. It should be UTF-16LE. See here Link to comment Share on other sites More sharing options...
AutoBert Posted April 24, 2016 Share Posted April 24, 2016 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? Link to comment Share on other sites More sharing options...
water Posted April 24, 2016 Share Posted April 24, 2016 I'm glad it is not the Excel UDF My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Thomymaster Posted April 25, 2016 Author Share Posted April 25, 2016 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 Link to comment Share on other sites More sharing options...
AutoBert Posted April 25, 2016 Share Posted April 25, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
Thomymaster Posted April 25, 2016 Author Share Posted April 25, 2016 Hi I hit a bug in 3.3.12.0 and thus had to upgrade: Cheers Thomy Link to comment Share on other sites More sharing options...
AutoBert Posted April 26, 2016 Share Posted April 26, 2016 (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: 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 Link to comment Share on other sites More sharing options...
Thomymaster Posted April 27, 2016 Author Share Posted April 27, 2016 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now