lsakizada

_Excel_BookSaveAs CSV file as XLS on german machine

10 posts in this topic

#1 ·  Posted (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 by lsakizada

Be Green Now or Never (BGNN)!

Share this post


Link to post
Share on other sites



Can you please post an example of the CSV file for us to play with?


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

#3 ·  Posted (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 by lsakizada

Be Green Now or Never (BGNN)!

Share this post


Link to post
Share on other sites

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):

post-7903-0-76606900-1427974219_thumb.jp


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

#5 ·  Posted (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 by lsakizada

Be Green Now or Never (BGNN)!

Share this post


Link to post
Share on other sites

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

#7 ·  Posted (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 by lsakizada

Be Green Now or Never (BGNN)!

Share this post


Link to post
Share on other sites

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 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 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 :huggles:


Be Green Now or Never (BGNN)!

Share this post


Link to post
Share on other sites

:)


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

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