Batch script required for v10.5 (to convert and transfer .csv files)

Alex_20665

Someone's recently asked me to come up with an automated solution which basically looks at .csv files saved daily to a given folder and have these routinely exported out as either .xls or .xlsx formatted files to another folder.

 

Having recalled the batch script functionality in earlier Monarch versions (which can be added to a task scheduler), I'm wondering if might be a feasible option and, if so, what the appropriate command line might be?

 

At present, I've set up a model and project file based on an import of several known .csv files from a folder along with a project export which splits out files based on the first field, however, is there an option to shape a script to pick up a range of unknown .csv files from a given location? Or is there perhaps a better alternative mechanism I should consider?

 

All suggestions gratefully appreciated.

 

Alex

Altair Forum User

Before I get to the point, I want to say I have been retired for 4 years and away from Monarch and Excel, but......

 

What you desire can be done using v10.5, but I do not know if it can be done from the command line.

 

I have had occasion to do something very similar.  I used an Excel VBA script to accomplish the task, and it is usable from the command line.  I found the script at this website Excel VBA convert CSV to Excel If memory serves me, I made a couple of minor adjustments and changed the output to xlsx.  Hope that helps.

 

Here is the script I found:

Public Sub csvToxls()

    Dim FSO As Object

    Dim folder As Object

    Dim wb As Object

   

    csvPath = "C:\test\"

    xlsPath = "C:\test\xlsFolder"

   

    Set FSO = CreateObject("Scripting.FileSystemObject")

    Set csvFolder = FSO.GetFolder(csvPath)

   

    If FSO.FolderExists(xlsPath) = False Then

        FSO.createFolder (xlsPath)

    End If

   

    Set xlsFolder = FSO.GetFolder(xlsPath)

   

    With Application

        .DisplayAlerts = False

        .ScreenUpdating = False

    End With

       

    For Each wb In csvFolder.Files

        If LCase(Right(wb.Name, 3)) = "csv" Then

            Set activeWB = Workbooks.Open(wb)

            activeWB.SaveAs Filename:=xlsPath & "\" & Left(activeWB.Name, Len(activeWB.Name) - 3) & "xlsx", FileFormat:=xlOpenXMLWorkbook

            activeWB.Close True

        End If

    Next

   

    With Application

        .DisplayAlerts = True

        .ScreenUpdating = True

    End With

End Sub

 

 

Joe Berry