lsakizada Posted April 2, 2015 Share 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)! Link to comment Share on other sites More sharing options...
water Posted April 2, 2015 Share 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 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...
lsakizada Posted April 2, 2015 Author Share 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)! Link to comment Share on other sites More sharing options...
water Posted April 2, 2015 Share 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 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...
lsakizada Posted April 2, 2015 Author Share 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)! Link to comment Share on other sites More sharing options...
water Posted April 2, 2015 Share 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 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...
lsakizada Posted April 2, 2015 Author Share 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)! Link to comment Share on other sites More sharing options...
water Posted April 2, 2015 Share 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 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...
lsakizada Posted April 5, 2015 Author Share 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)! Link to comment Share on other sites More sharing options...
water Posted April 5, 2015 Share Posted April 5, 2015 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...
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