6 Replies Latest reply: May 15, 2014 10:01 AM by bgoderwis _ RSS

    Import Excel sheet from command line

    bgoderwis _

      I am using V9.0 Pro.  I have checked the Help and this forum, but I'm still having some trouble.  I would like to use the command line to import one particular worksheet from an Excel file that has multiple sheets and export to a csv file.  I have tried the /datasource and /table options, but I must not have the syntax right.  My batch file runs, but the csv file contains only the column headers and no data.  Thanks.

        • Import Excel sheet from command line
          Data Kruncher

          Do you want the end result to be a single csv file with all of the content from the various worksheets in the xls file?

           

          If so, I would build separate models and projects for each sheet (as a model can only use one sheet) and include a project export to export the data to your csv file. Build the first project to overwrite the existing csv, then set the remainder up to add data to the existing csv.

           

          Then you can build a batch file to process all of your project files such as:

          start /wait "C:\Program Files\Monarch\Program\Monarch.exe" /prj:"C:\Monarch Development\Sheet1CreateCSV.xprj" /pxall

          start /wait "C:\Program Files\Monarch\Program\Monarch.exe" /prj:"C:\Monarch Development\Sheet2AppendToCSV1.xprj" /pxall

          start /wait "C:\Program Files\Monarch\Program\Monarch.exe" /prj:"C:\Monarch Development\Sheet3AppendToCSV2.xprj" /pxall

          start /wait "C:\Program Files\Monarch\Program\Monarch.exe" /prj:"C:\Monarch Development\Shhet4AppendToCSV3.xprj" /pxall

          /code

           

          Would that accomplish what you want?

            • Import Excel sheet from command line
              bgoderwis _

              Thanks for the quick response.  I really like your overwrite/append idea - I hadn't thought of that.  For this project (no pun intended), I have 49 separate spreadsheets.  The number of files will vary from time to time.  I created a model, project file, and batch file.  Everything works fine for the file I used to create the project.  However, for some reason I cannot figure out, when I change my batch file to include the /datasource "override", the exported csv file contains only column headers.  If one of the spreadsheets is named ABC.xls and the worksheet I need is named XYZ, what should my command line look like?

                • Import Excel sheet from command line
                  Data Kruncher

                  OK, I've fought with this one for a little while now, and at this point I'd be happy to be told this can in fact be done but right now I'm not so sure that it can.

                   

                  No matter what I try I can't build a datasource connection to an xls file.

                   

                  Works just fine as a project, but then you're stuck with the worksheet defined when project file was saved.

                   

                  I wonder if it would be possible to dynamically build project files based on the worksheet names, with a method similar to this [URL="http://www.monarchforums.com/showthread.php?p=4468#post4468"]old idea[/URL] (took some digging to find that one). But that approach is likely a thing of the past with today's XML project files.

                   

                  Maybe not though... the Monarch Utility program accepts command line parameters for the search and replacement terms, and the input and output file names. You could run the search and replace on the intital xprj as the input file (after all it's just another text file), if you were careful and knew that the unique worksheet name only exists in the xprj as worksheet name and not part of some other text string.

                   

                  Then you could build a little VBA script to open your initial XLS file, and get all of the worksheet names, then for each of those names run them through the xprj search and replace, creating new xprj files as needed. Even build yourself a little batch file to process all of the new xprj files at the same time.

                   

                  Or maybe I'm going off the deep end again first thing in the morning, and there's a simpler way some kind forum member can provide.

                    • Import Excel sheet from command line
                      bgoderwis _

                      I appreciate you looking into this.  In a way, I'm glad you couldn't get the datasource to work, so it's not something I'm doing wrong.  On the other hand, it's strange that it doesn't work.

                       

                      I did come up with a work around.  I built my model and project using ABC.xls as my input file.  Then in my batch file, I used the "for" construct to process every *.xls file in a particular directory.  As part of the "for" construct, I have the file name as a variable.  Inside the "for" loop, I use the variable in a copy statement to copy that file to ABC.xls, then run Monarch.  Since I wanted the file name as a field in my output file, I also passed the file name variable as a runtime variable to Monarch.

                       

                      Thanks again for the help.

                        • Import Excel sheet from command line
                          Nick Osdale-Popa

                          Excellent... that was going to be my suggestion... if you can't have a variable source in your model, make your source dynamic by renaming each XLS file to the source name.

                           

                          This is actually the easy way of doing it then trying to parse the XML of the model to change the source name.

                           

                          If you don't mind, could you post your batch file so that others may benefit from the solution?

                            • Import Excel sheet from command line
                              bgoderwis _

                              As requested:

                               

                              @echo on

                              ::  ***************************************************************************

                              ::  *                                                                         *

                              ::  *  ABC.BAT - Create a csv file from a folder of Excel files.              *

                              ::  *                                                                         *

                              ::  *  Written by: Bob G. 05-28-2008                                          *

                              ::  *                                                                         *

                              ::  *  Revisions:                                                             *

                              ::  *                                                                         *

                              ::  ***************************************************************************

                               

                              ::  ***************************************************************************

                              ::  * Start up  

                              ::  ***************************************************************************

                              set monarch="C:\program files\monarch\program\monarch.exe"

                              cd /d J:\ABC

                              if exist ABC.xls del ABC.xls

                              if exist ABC.csv del ABC.csv

                               

                              ::  ***************************************************************************

                              ::  * Create reports

                              ::  ***************************************************************************

                              for /F "usebackq tokens=*" %%i in (`dir /on /b *.xls`) do (

                                 copy /Y "%%i" ABC.xls

                                 %monarch% /prj:ABC.xprj /set:"Filename"="%%i" /exp:ABC.csv /expfileopt:add /T

                              )

                              :: To create separate csv files, substitute this Monarch line for the one above.

                              :: %monarch% /prj:ABC.xprj /set:"Filename"="%%i" /exp:"%%~ni.csv" /expfileopt:overwrite /T

                              /code