_Excel_BookSaveAs CSV file as XLS on german machine

[lsakizada]

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

[water]

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

[lsakizada]

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

[water]

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

[lsakizada]

@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

[water]

I did not change a single line in the function. Only added the first 3 lines.

[lsakizada]

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

[water]

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

[lsakizada]

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

[water]