lsakizada 0 Posted April 2, 2015 (edited) Hi I have a csv file with coma delimiter columns that needs to be converted to '.xls' format On English locale its works perfectly (each data line separated by comma get its own column cell in the excel sheet) The problem is that on German locale it is not working. The xls contain single column only in the excel sheet. The UDF function _Excel_BookSaveAs does not save it as expected. How to resolve that issue? Here is the function that I am using: Func CreateXLSFromCSV($sFilePath) Local $sDrive = "", $sDir = "", $sFileName = "", $sExtension = "" Local $Parame = "" Local $aPathSplit, $sWorkbook Local $oAppl $oAppl = _Excel_Open(False) If @error Then MsgBox($MB_SYSTEMMODAL, "CreateXLSFromCSV ", "Error openning Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oAppl) Return SetError(1, 1, "") EndIf $oWorkbook = _Excel_BookOpenText($oAppl, $sFilePath) If @error Then MsgBox($MB_SYSTEMMODAL, "CreateXLSFromCSV", "Error opening text " & @ScriptDir & "\" & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oAppl) Return SetError(2, 2, "") EndIf $sWorkbook = StringTrimRight($sFilePath, 4) & ".xls" _Excel_BookSaveAs($oWorkbook, $sWorkbook, 56, True) ;56 ;_Excel_BookSaveAsEx($oWorkbook, $sWorkbook, 56, True) If @error Then MsgBox($MB_SYSTEMMODAL, "CreateXLSFromCSV", "Error saving workbook to '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oAppl) Return SetError(3, 3, "") EndIf _Excel_Close($oAppl) Return $sWorkbook EndFunc ;==>CreateXLSFromCSV Edited April 2, 2015 by lsakizada Be Green Now or Never (BGNN)! Share this post Link to post Share on other sites
water 2,365 Posted April 2, 2015 Can you please post an example of the CSV file for us to play with? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
lsakizada 0 Posted April 2, 2015 (edited) Can you please post an example of the CSV file for us to play with? I do not know how to include file in this forum but I put the text in the code box. Edit: I am running Excel 2007 Date,SensorID,FG33,FG100,MC332,MC333,BG2000,CF0,CF99 '2015-04-01 07:28:59,1,,96,83,83,100,100, '2015-04-01 07:28:59,4,,96,83,83,100,100, '2015-04-01 07:29:47,1,,,,,0,, '2015-04-01 07:29:47,4,,,,,0,, '2015-04-01 07:30:47,3,,94,84,83,99,, '2015-04-01 07:30:48,1,,96,83,83,100,100, '2015-04-01 07:30:48,4,,96,83,83,100,100, '2015-04-01 07:31:48,1,,94,82,84,99,, '2015-04-01 07:31:48,4,,94,82,84,99,, '2015-04-01 07:32:48,1,,96,83,83,100,100, '2015-04-01 07:32:48,4,,96,83,83,100,100, '2015-04-01 07:33:48,1,,94,84,83,99,, '2015-04-01 07:33:48,4,,94,84,83,99,, '2015-04-01 07:34:48,1,,94,84,83,99,100, '2015-04-01 07:34:48,4,,94,84,83,99,100, '2015-04-01 07:35:49,1,,94,82,84,99,, '2015-04-01 07:35:49,4,,94,82,84,99,, '2015-04-01 07:36:49,1,,96,83,83,100,, '2015-04-01 07:36:49,4,,96,83,83,100,, '2015-04-01 07:37:50,1,,94,84,83,99,, '2015-04-01 07:37:50,4,,94,84,83,99,, '2015-04-01 07:38:50,1,,94,84,83,99,100, '2015-04-01 07:38:50,4,,94,84,83,99,100, '2015-04-01 07:39:50,1,,94,82,84,99,, '2015-04-01 07:39:50,4,,94,82,84,99,, '2015-04-01 07:40:50,1,,94,82,83,109,, '2015-04-01 07:40:50,4,,94,82,83,109,, '2015-04-01 07:41:50,2,,94,84,83,99,, '2015-04-01 07:41:51,1,,94,84,83,99,100, '2015-04-01 07:41:51,4,,94,84,83,99,100, '2015-04-01 07:42:51,3,,94,84,83,99,, '2015-04-01 07:42:51,1,,96,83,83,100,, '2015-04-01 07:42:52,4,,96,83,83,100,, Edited April 2, 2015 by lsakizada Be Green Now or Never (BGNN)! Share this post Link to post Share on other sites
water 2,365 Posted April 2, 2015 I modified your script to make it runnable: #include <Excel.au3> CreateXLSFromCSV("C:\temp\CSV.TXT") Exit Func CreateXLSFromCSV($sFilePath) Local $sDrive = "", $sDir = "", $sFileName = "", $sExtension = "" Local $Parame = "" Local $aPathSplit, $sWorkbook Local $oAppl $oAppl = _Excel_Open(False) If @error Then MsgBox($MB_SYSTEMMODAL, "CreateXLSFromCSV ", "Error openning Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oAppl) Return SetError(1, 1, "") EndIf $oWorkbook = _Excel_BookOpenText($oAppl, $sFilePath) If @error Then MsgBox($MB_SYSTEMMODAL, "CreateXLSFromCSV", "Error opening text " & @ScriptDir & "\" & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oAppl) Return SetError(2, 2, "") EndIf $sWorkbook = StringTrimRight($sFilePath, 4) & ".xls" _Excel_BookSaveAs($oWorkbook, $sWorkbook, 56, True) ;56 ;_Excel_BookSaveAsEx($oWorkbook, $sWorkbook, 56, True) If @error Then MsgBox($MB_SYSTEMMODAL, "CreateXLSFromCSV", "Error saving workbook to '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oAppl) Return SetError(3, 3, "") EndIf _Excel_Close($oAppl) Return $sWorkbook EndFunc ;==>CreateXLSFromCSV This returns (I'm running Excel 2010): My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
lsakizada 0 Posted April 2, 2015 (edited) @Water, there are no changes in the function code right? just passing a text file. its works for me too thanks for the help Edited April 2, 2015 by lsakizada Be Green Now or Never (BGNN)! Share this post Link to post Share on other sites
water 2,365 Posted April 2, 2015 I did not change a single line in the function. Only added the first 3 lines. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
lsakizada 0 Posted April 2, 2015 (edited) I did not change a single line in the function. Only added the first 3 lines. I read the Excel.SaveAs method in MSDN and it can get another parameter named Local see https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas.aspx The UDF function_Excel_BookSaveAs does not accept that as parameter. I tried to extend the function but it did not works for me I gues that I made some mistake somewhere I think it will be useful to support local by using native proprieties of excel. Edited April 2, 2015 by lsakizada Be Green Now or Never (BGNN)! Share this post Link to post Share on other sites
water 2,365 Posted April 2, 2015 For the time being you could use a customized version of the function: Func _Excel_BookSaveAsEX($oWorkbook, $sFilePath, $iFormat = Default, $bOverWrite = Default, $sPassword = Default, $sWritePassword = Default, $bReadOnlyRecommended = Default, $bLocal = Default) If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 0, 0) If $iFormat = Default Then $iFormat = $xlWorkbookDefault Else If Not IsNumber($iFormat) Then Return SetError(2, 0, 0) EndIf If $bOverWrite = Default Then $bOverWrite = False If $bReadOnlyRecommended = Default Then $bReadOnlyRecommended = False If FileExists($sFilePath) Then If Not $bOverWrite Then Return SetError(3, 0, 0) Local $iResult = FileDelete($sFilePath) If $iResult = 0 Then Return SetError(4, 0, 0) EndIf $oWorkbook.SaveAs($sFilePath, $iFormat, $sPassword, $sWritePassword, $bReadOnlyRecommended, Default, Default, Default, Default, Default, Default, $bLocal) If @error Then Return SetError(5, @error, 0) Return 1 EndFunc ;==>_Excel_BookSaveAsEX My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
lsakizada 0 Posted April 5, 2015 Hi water . sorry for the late response. Thanks for the function. somehow it is still not working in my code. but the other was working for me. I can not test it much and see were it is fails but seems that its ok. thanks alot Be Green Now or Never (BGNN)! Share this post Link to post Share on other sites
water 2,365 Posted April 5, 2015 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites