take a look at the following, hope it helps as well
'''converts a MS Excel file to csv w/ the same name in the same directory'''
print "------ beginning to convert XLS to CSV -
import win32com.client, os
excel = win32com.client.Dispatch('Excel.Application')
fileDir, fileName = os.path.split(aFile)
nameOnly = os.path.splitext(fileName)
newName = nameOnly + ".csv"
outCSV = os.path.join(fileDir, newName)
workbook = excel.Workbooks.Open(aFile)
workbook.SaveAs(outCSV, FileFormat=24) # 24 represents xlCSVMSDOS
print "...Converted " + nameOnly + " to CSV"
print ">>>>>>> FAILED to convert " + aFile + " to CSV!"
What I would suggest is opening up the File by doing the following.
1) Put the excel file in location it is going to reside in.
2) right click, open with Monarch, if it is not one of the programs available, click on Browse and then find it, and then use it.
3) save the process once you hit next and import all the fields you need.
4) save the project as, and it will prompt you to also save the model,
5) once you make changes, make sure to save the project every time so that the changes afterwards will be saved.
6) You can create the PROJECT EXPORTS Wizard which to save a process perpetually as the EXPORT Wizard is usually done for a 1 time use basis.
When you use the PROJECTS EXPORTS it allows you to Export into text file, and you can specify the file .csv as the end result.
play with it, and let me know how it goes, as that is how I learned to do my tricks.
I would also suggest learning Batch Files or VB which i do not even use as that is overkill for me.
I normally use Batch Files to create my processes and output automated, which i can schedule using windows, or I can just semi-automate a process when the file is available just double click the batch file and it does the rest.
Example of a list of line items in a batch file using Monarch being called up: keep in mind you can also export data from output into MS Access which most of us do quite often. You can do that thru the wizard or you can do it as I do thru the Batch File Process.
ECHO "Generating Report Process..." This adds the date to the file after I rename it.
example of a path i use for one of my processes below
ECHO "Generating Match_Master_List..."
"C:\Program files\Monarch\Program\monarch.exe" /prj:"
MONARCH DOWNLOAD\Models\Match_Master_List.xprj" "/pxall"
Pegasus\MONARCH DOWNLOAD\Output Files\Match_Master_List.xls" "Match_Master_List_%MYDATE%.xls"
If you are simply converting any XLS file to a CSV file I would use the pure vbs script that was first provided. If you need to do calculations on the file or remove columns, I would use Monarch as it will be simpler to manage instead via a script to remove certain columns. There is no need to import into Access as Monarch can export in CSV.
I agree that you can put into CSV thru the PROJECTS EXPORT, as I mentioned, if you are doing reconciliations, Monarch is not as robust and easily to maneuver to perform recons etc, in some cases you have to create multiple modules or projects. It is better to input into MS Access, otherwise keep it simple and us Monarch when applicable.
Admittedly bare-bones but functional VBA code to use a predefined project file.
Dim objMonarch As Object
Dim bSuccess As Boolean
Set objMonarch = CreateObject("Monarch32")
bSuccess = .SetProjectFile("C:\Folder\MyProject.xprj")
bSuccess = .JetExportTable("C:\Folder\MyProject.csv", "Output", 0)
Set objMonarch = Nothing
/CODEYou could automate this just as easily as a batch file with Windows Scheduler by putting the code in the Workbook_Open event of the Excel file, and ensuring that Excel will automatically run macros with prompting ("trust" macros).
For real-wold work you'll probably want to add error/failure checking to make the code more robust, but I'll leave that to you.