7 Replies Latest reply: May 15, 2014 10:12 AM by elginreigner _ RSS

    Importing Multiple Excel Files

    Allison Mangini

      Can I import multiple Excel files for use in Monarch?  I have a yearly report that I am working on where the columns are the same for each month.  Each month's information is kept in a separate Excel file.  I would like to import them into one file so I can create a year end summary.  I am using version 10.5 (about to get 11 any day now).

       

      Does anyone know if Monarch can handle this for me and how to do it?  I have searched the help files and haven't found anything so far.

       

      Thanks!

        • Importing Multiple Excel Files
          Olly Bond

          Hello Allison,

           

          I'm afraid that the answer is "no" for Excel files, but it's "yes" for CSV (comma separated values) files.

           

          If you can save the Excel files as CSV format, then you can open multiple files in Monarch Pro (v9 and above, I think).

           

          You'll have to open them as File > Open Database (as opposed to File > Open Report).

           

          Hope this helps,

           

          Olly

            • Importing Multiple Excel Files
              Allison Mangini

              Thanks Olly for the suggestion.  I saved them all as .csv files and am still not able to import more than one file at a time.  I also tried opening one file and then opening another one but it only overrode that first one.

               

              Do I need to start a project in order to open more than one file?

               

              Thanks!

               

              Allison

                • Importing Multiple Excel Files
                  Olly Bond

                  Hi Allison,

                   

                  No need to start a project. If you have the 12 CSV files saved on your desktop, then run Monarch, choose File > Open Database, and then in the dialog for Data Source select Browse. Browse to the desktop, and then holding down the CTRL key click on the files you want to open together. You'll see in the dialog box that the filenames appear within quotes like:

                   

                  "Jan.csv" "Feb.csv" "Mar.csv" etc

                   

                  When you have all 12 selected, click Open, select the fields you want, and you should be in business. If you then save the project, the project file will remember the paths to all the 12 input files, so you can open them up easily in future.

                   

                  Best wishes,

                   

                  Olly

                    • Importing Multiple Excel Files
                      elginreigner _

                      Hi Allison,

                       

                      No need to start a project. If you have the 12 CSV files saved on your desktop, then run Monarch, choose File > Open Database, and then in the dialog for Data Source select Browse. Browse to the desktop, and then holding down the CTRL key click on the files you want to open together. You'll see in the dialog box that the filenames appear within quotes like:

                       

                      "Jan.csv" "Feb.csv" "Mar.csv" etc

                       

                      When you have all 12 selected, click Open, select the fields you want, and you should be in business. If you then save the project, the project file will remember the paths to all the 12 input files, so you can open them up easily in future.

                       

                      Best wishes,

                       

                      Olly[/QUOTE]

                       

                      Hmm, I did not know that, I always combined them via a DOS prompt.

                        • Importing Multiple Excel Files
                          KeyserSoze _

                          Allison,

                           

                          I would assume that the next order of business for you with this task would be to add a Period field to distinguish the values from one periodic file from those in another. Keep in mind that you can use the File() function to get the name of the data source into your Table view, using a calculated field. With another function or two you can isolate "Jan", "Feb", "Mar", etc., from the names as Olly supplied as an example.

                           

                          You could then build a summary showing the Period field as an across key field, showing values from Jan through Dec rather easily.

                           

                          Just something else to keep in mind that simplify other analysis work using the same data files.

                            • Importing Multiple Excel Files
                              Ziggy _

                              I believe my [URL="http://www.monarchforums.com/showthread.php?2712-Batch-converting-files-to-CSV"]post[/URL]  should help.  In my situation I was using Monarch to convert my report to a CSV, but I have also adapted the solution to Import into 1 excel file and you can even use this script to import into an Access database...

                               

                              *Credit goes to the Posters that helped me

                               

                               

                              Excel:

                               

                              @echo off

                              For %%i in ("C:\Users\ziggy\Documents\MonarchTemplates\Export\IP93\K1*.txt") Do Call :DoMonarch "%%i"

                              GOTO :EOF

                              :DoMonarch

                              ("C:\Program Files\Monarch\Program\Monarch.exe" /rpt:"%1" /mod:"C:\Users\ziggy\Documents\MonarchTemplates\Models\ModelFileName.xmod" /exp:"C:\Users\ziggy\Documents\MonarchTemplates\Export\TESTOUT\%n1..xls" /expfileopt:add /T )

                               

                              rem renames as backup 

                              ren %1 *.bak

                               

                               

                              GOTO :EOF[/CODE]

                               

                               

                              Access:

                               

                              @echo off

                               

                              rem  use wild cards around file name key word

                               

                              For %%i in ("C:\MonarchScriptData\ACDPTAGS.txt") Do Call :DoMonarch "%%i"

                              GOTO :EOF

                              :DoMonarch

                              ("C:\Program Files\Monarch\Program\Monarch.exe" /rpt:"%~1" /mod:"C:\Users\ziggy\Documents\MonarchTemplates\Models\YourModelName.mod" /exp:"C:\MonarchScriptData\AccessDB_Name.accdb" /expfileopt:add /exptable:"tblACDPTAGS" /exptableopt:append /T )

                               

                              rem renames as backup 

                              ren %1 *.bak

                               

                              GOTO :EOF

                              /CODE

                               

                               

                               

                              Copy the code, edit the path according to which folder contains the Monarch Model and the location of your Export folder as well as the folder for the source files.

                               

                              Save the file with a .cmd extension... I think you can use .Bat also , if you can’t file save as with the extension, then rename later

                               

                              Then all you do is double click the file and Monarch will run in the background and convert all the files and rename the imported ones ( if you want).

                               

                                • note some people don’t have their systems set to show file extensions, so if you don’t see any files on your system like this...

                               

                              (Where File is the name of the filenames) File.xlsx, File.xls ,File.doc  etc.... if this is an issue post back.

                                • Importing Multiple Excel Files
                                  elginreigner _

                                    • note some people don’t have their systems set to show file extensions, so if you don’t see any files on your system like this...

                                   

                                  /QUOTE

                                   

                                  If needed, its under the Control Panel, Folder Options,  View Tab, roughly 10 options down the list "Hide extensions for known file types". Uncheck this to alter file extensions.

                                   

                                  FYI, keep in mind if you uncheck this, while renaming a file, you can end up removing the extension. Make sure you retain the extension if you are not changing it.