Batch script required for v10.5 (to convert and transfer .csv files)
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
Answers
-
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
0 -
There is a topic in the built-in help file for Monarch dealing with command lines:
Monarch supports a number of command line parameters that you can use to open files when you start a Monarch session or to automate an entire session and produce an export file.
Note: Monarch has recently introduced some powerful new changes into the command line functionality, adding some new parameters. For those users upgrading from Monarch version 6 and earlier, the parameters do not have to be in order, as was previously the case.
The syntax for a simple Monarch command line is:
Monarch /rpt:[Report File] | /mod:[Model File] /exp:[Export File] /Window
For example, to open a report and model and export from the table:
monarch /rpt:"c:\program files\monarch\reports\classic.prn" /mod:"c:\program files\monarch\models\lesson10.mod" /exp:"c:\program files\monarch\export\classic.xls" /T
To open multiple reports, set a runtime field called "Test" to "True" and export from the current summary:
monarch /rpt:"c:\program files\monarch\reports\classic.prn" /rpt:"c:\program files\monarch\reports\classfeb.prn" /mod:"c:\program files\monarch\models\runtime.mod" /set:"Test"="True" /exp:"c:\program files\monarch\export\classic.xls"
V10 Command Line Parameters
Here is a list and explanations of the V10 command line parameters.
Parameter Description
/rpt:[Report File]
Specifies the report file to use in this Monarch session. You can also specify rpt: more than once to open multiple reports.
/prj:[Project File]
Specifies the project file to use in this Monarch session.
When the /prj and /rpt switches are used in tandem, the reports specified in the command line override any reports indicated by the project, i.e., if the project already contains a report list, then it is discarded.
/mod:[Model File]
Specifies the model file to use in this Monarch session. Note: Omit this parameter if you specified a project file or portable report file as the first parameter.
/exp:[Export File]
Specifies the name of an export file or PRF file to create. Include this parameter if you want to automate a Monarch session. The export file extension determines the file type produced, either a portable report file (PRF) or an export file (DB, DBF, MDB, TXT, WKx, XLS, or any other extension to create a delimited text file).
/expfileopt:[option]
(V8 and later)
Sets the file option for the command line export. Possible values of option are overwrite, add, and skip. These correspond directly to the options on the Output File screen of the Export Wizard.
/set:[Field Name]=
[Field Value]
Sets a runtime parameter named [Field Name] to [Field Value] from the command line, instead of typing the values into a dialog box. If all runtime parameters are fulfilled from the command line, then the corresponding dialog is suppressed upon program startup.
/Window
Specifies the Monarch window to display or export data from. Use /R to indicate the Report window, /T to indicate the Table window and /S to indicate the Summary window.
When creating a portable report file, this parameter indicates the window to display when the portable report file is subsequently loaded by Monarch (if omitted, the Report window displays).
When exporting data to a file, this parameter indicates the window to export data from, either the Table window or the Summary window (if omitted, data is exported from the Table window).
If you omit the Export file parameter, Monarch will load the specified data source (report, project, or portable report) along with the specified model file, then it will display the specified window. If the window parameter is also omitted, the data source determines which window will be displayed. If the data source is a report, the Report window displays. If the data source is an external database, the Table window displays.
/I
When creating a portable report, this parameter specifies that a Tree View Index should be included in the portable report. If this parameter is omitted, the Tree View Index is not included.
/M
When creating a portable report, this parameter specifies that the model file should be included in the portable report. If this parameter is omitted, the model file is not included.
/E
When creating a portable report, this parameter specifies that the portable report should be encrypted. If omitted, the portable report is not encrypted.
Monarch does not allow you to specify an encryption password on the command line; instead you must establish a default password using the Change PRF Password dialog. If no default password has been established, you cannot use this parameter to encrypt a PRF file.
Monarch Pro Only
/datasource:[name]=
connection_string
Specifies the connection string for the named data source, where name is either maindb (for the main database) or the name of an external lookup. If the "name=" part is omitted then maindb is implied. For example, "/datasource:Lookup2=c:\test\MyData.mdb" would set "c:\test\MyData.mdb" as the source for the external lookup named "Lookup2", overriding whatever source was specified in the model. Note the "name=" part was not optional in V7.
/exptable:[Tablename]
(V8 and later)
Sets the name of the table to be used for the command line export
/exptableopt:[option]
(V8 and later)
Sets the table option for the command line export. Possible values of option are overwrite, append, and skip. These correspond directly to the options on the Table Info screen of the Export Wizard
/pwd:[name=]password
Specifies the password to use for the named data source, where name is either maindb (for the main database) or the name of an external lookup. If the "name=" part is omitted then maindb is implied. For example, "/pwd:fred" would set "fred" as the password to be used for the main import table. Note the "name=" part was not optional in V7.
/table:[name=]
tablename
Specifies the table to open for the named data source, where name is either maindb (for the main database) or the name of an external lookup. If the "name=" part is omitted then maindb is implied. For example, "/table:Lookup2=Employees" would set "Employees" as the table for the external lookup named "Lookup2", overriding whatever table was specified in the model. Note the "name=" part was not optional in V7.
/px:[jobname]
Runs project export jobname if it exists. Multiple jobs may be run by using multiple /px switches
/pxall
Specifies that all Project Exports within the project should be executed.
These switches may appear in any order and may appear multiple times (with different name portions). Note: As with all command line switches, if the name or value portion has embedded spaces then it must be enclosed in double-quotes.
While these switches are primarily used to override values from a project or model file, it is possible to use them to completely specify a main import table even in the absence of an associated database project. For example:
monarch /datasource:c:\Data\June.mdb /table:Sales /pwd:xyz /mod:c:\Models\Sales.mod /exp:c:\Exports\JuneSales.xls /s
This command would launch Monarch using data from table "Sales" in the Access database "c:\Data\June.mdb" (which requires password "xyz"). The data would be loaded and interpreted via model "c:\Models\Sales.mod", and the current summary (notice the "/s" switch) would then be exported to "c:\Exports\JuneSales.xls"
Command Line Examples:
"c:\program files\monarch\program\monarch.exe" /rpt:"c:\program files\monarch\reports\classic.prn" /mod:"c:\program files\monarch\models\lesson11.xmod" /expfileopt:add /exp:"c:\program files\monarch\export\classic.txt" /S
The preceding command would launch Monarch and append data to an existing file named classic.txt from the summary window.
"c:\program files\monarch\program\monarch.exe" /rpt:"c:\program files\monarch\reports\classic.prn" /mod:"c:\program files\monarch\models\lesson11.xmod" /expfileopt:overwrite /exp:"c:\program files\monarch\export\classic.xls" /T
The preceding command would launch Monarch and export to classic.xls from the table window, overwriting an existing file.
"c:\program files\monarch\program\monarch.exe" /rpt:"c:\program files\monarch\reports\classic.prn" /mod:"c:\program files\monarch\models\lesson11.xmod" /expfileopt:add /exptableopt:append /exptable: "Test" /exp:"c:\program files\monarch\export\classic.xls" /T
The preceding command (Monarch Pro only) would launch Monarch and append data from the table window to an existing named range or sheet called “Test” within an existing file called classic.xls.
"c:\program files\monarch\program\monarch.exe" /rpt:"c:\program files\monarch\reports\classic.prn" /mod:"c:\program files\monarch\models\lesson11.xmod" /expfileopt:add /exptableopt:overwrite /exptable: "Test" /exp:"c:\program files\monarch\export\classic.xls" /T
The preceding command (Monarch Pro only) would launch Monarch and export data from the table window, overwriting an existing named range or sheet called “Test” within an existing file called classic.xls.
V6 and Earlier Command Line Parameters
For informational purposes, here are the V6 and earlier command line parameters.
Monarch report model [exportfile] [/window] [/I] [/M] [/D] [/E]
The command line parameters must be specified in the order shown above. Command line parameters shown in brackets are optional; these parameters are used to automate data export and PRF creation operations. Long file names or paths using long folder names must be enclosed in quotes (e.g., "C:\Program Files\Monarch\Reports\Classic.prn").
A description of each command line parameter is shown in the following table
Parameter Description
Report
Specifies the report file to use in this Monarch session. You may specify a project file (prj) or a portable report file (prf) in place of the report file and model file.
Model
Specifies the model file to use in this Monarch session. Omit this parameter if you specified a project file or portable report file as the first parameter.
Exportfile
Specifies the name of an export file or PRF file to create. Include this parameter if you want to automate a Monarch session. The export file extension determines the file type produced, either a portable report file (PRF) or an export file (DB, DBF, MDB, TXT, WKx, XLS, or any other extension to create a delimited text file).
/window
Specifies the Monarch window to display or export data from. Use /R to indicate the Report window, /T to indicate the Table window and /S to indicate the Summary window.
When creating a portable report file, this parameter indicates the window to display when the portable report file is subsequently loaded by Monarch (if omitted, the Report window displays).
When exporting data to a file, this parameter indicates the window to export data from, either the Table window or the Summary window (if omitted, data is exported from the Table window).
If you omit the Export file parameter, Monarch will load the specified data source (report, project, or portable report) along with the specified model file, then it will display the specified window. If the window parameter is also omitted, the data source determines which window will be displayed. If the data source is a report, the Report window displays. If the data source is an external database, the Table window displays.
/I
When creating a portable report, this parameter specifies that a Tree View Index should be included in the portable report. If this parameter is omitted, the Tree View Index is not included.
/M
When creating a portable report, this parameter specifies that the model file should be included in the portable report. If this parameter is omitted, the model file is not included.
/E
When creating a portable report, this parameter specifies that the portable report should be encrypted. If omitted, the portable report is not encrypted.
Monarch does not allow you to specify an encryption password on the command line; instead you must establish a default password using the Change PRF Password dialog. If no default password has been established, you cannot use this parameter to encrypt a PRF file.
You may use either of the following methods to pass a command line to Monarch:
Select Start, Run, type the command line in the Open box, then choose OK. Use this method if you will need to use the command line only once.
Create an icon that includes the command line. Use this method if you want to run the same Monarch session in the future, such as when you receive a new instance of a report file.
Enter one or more command lines into a batch file (.bat or .cmd) and execute the batch file.
0
Altair Employee