11 Replies Latest reply: May 15, 2014 10:10 AM by acs_Manohara _ RSS

    converting excel to csv using VB

    acs_Manohara _

      Hi I am trying to get the code to convert excel to csv in the forum i am not getting it. Can any one of you please provide me the code for opening excel in monarch using open database

        • converting excel to csv using VB
          adonis _

          not sure what you are trying to do exactly, normally i use monarch take it into MS Access and perform recons in MS Access, and then you can create the output using MS Access to make it .csv, .txt, .xls etc.

            • converting excel to csv using VB
              adonis _

              take a look at the following, hope it helps as well








              def convertXLS2CSV(aFile):

                  '''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[0] + ".csv"

                       outCSV = os.path.join(fileDir, newName)

                       workbook = excel.Workbooks.Open(aFile)

                       workbook.SaveAs(outCSV, FileFormat=24) # 24 represents xlCSVMSDOS



                       del excel


                       print "...Converted " + nameOnly + " to CSV"


                       print ">>>>>>> FAILED to convert " + aFile + " to CSV!"



                • converting excel to csv using VB
                  acs_Manohara _

                  HI I am trying to convert excel to csv using the predefindmonarch model. I need the VB code for Monarch to select monarch open database option and then select .xls file.


                  Or If you help me in writing the code to select a project in VB will also work for me.


                  Thanks in advance

                  - Mano

                    • converting excel to csv using VB
                      adonis _

                      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.

                      SET MTH=%DATE:~4,2%

                      SET DAY=%DATE:~7,2%

                      SET YR=%DATE:~10,4%


                      SET MYDATE=%YR%%MTH%%DAY%


                      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"


                      Rename "
                      Pegasus\MONARCH DOWNLOAD\Output Files\Match_Master_List.xls" "Match_Master_List_%MYDATE%.xls"



                      good luck.

                        • converting excel to csv using VB
                          elginreigner _

                          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.

                            • converting excel to csv using VB
                              adonis _

                              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.

                              • converting excel to csv using VB
                                Data Kruncher

                                Admittedly bare-bones but functional VBA code to use a predefined project file.


                                Sub DoMonarch()

                                    Dim objMonarch As Object

                                    Dim bSuccess As Boolean


                                    Set objMonarch = CreateObject("Monarch32")


                                    With objMonarch

                                        bSuccess = .SetProjectFile("C:\Folder\MyProject.xprj")

                                        bSuccess = .JetExportTable("C:\Folder\MyProject.csv", "Output", 0)



                                    End With

                                    Set objMonarch = Nothing

                                End Sub

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