4 Replies Latest reply: May 15, 2014 9:53 AM by Grant Perkins RSS

    Excel Sheet Import

    mcromer _

      I am new to Monarch Pro v9, but not to Monarch.  I am trying to use the table import on an Excel, and am having trouble figuring out how to get the header info from the top to append to line level.  I have been going through the lessons, and am having no luck.  I know how to handle this from the Report Screen, but that does not work with Excel, and was the reason for upgrading to Pro.  Here is a small sample to illustrate.

       

      Region : Midwest

      Time : Last 13 Wks

       

      Location    Sales $  Sales Qty

      Store 1        1000        300

        • Excel Sheet Import
          joey

          Unless anyone has a better idea (which I would love to hear), I suggest recording a VBA macro in Excel to copy the cells to new columns and add them to all rows.  Then delete any header or footer rows so that it is one table.  This VBA code could be moved to A VBA module in Access that could be executed on the current month's spreadsheet.

           

          With a minor amount of programing, this would be able to be included in a batch script that could call Monarch after cleaning up the spreadsheet a little. 

           

          Probably not the solution you were looking for, but it is how I've handled similar cases.

          • Excel Sheet Import
            Grant Perkins

            Originally posted by mcromer:

               I know how to handle this from the Report Screen, but that does not work with Excel, and was the reason for upgrading to Pro.  /b[/quote]Monarch Pro will read Excel directly as a database, which is what I assume you have found.

             

            That means that every row is treated as a complete record, as it would be in a database. The odd thing about Excel is that it is not really a database or report, though sometimes the worksheets can look like either of those or neither of those.

             

            There are some indirect options open to you, most of which could be scripted in a batch file (or using a programming language such as VBA) to turn them into a single process from a user point of view.

             

            For example you could export (or print) the Excel sheet to a print file or PDF and then use that as the source for Monarch analysis.

             

            Or you could read the Excel file and filter to retain only the 'report header' or 'page header' lines. Export that to an external lookup table (A Monarch Pro feature) and then use that as a lookup during a second pass through the Excel worksheet as a database, again filtering where necessary to retain only the rows you want and exclude the header rows as separate records but include the fields as data when required. You may need to generate a 'dummy' calculated field to effect the lookup link.

             

            There are no doubt other options to consider.

             

            The point is that your Excel worksheet is, as described, really a report held in Excel which means you as best to think of it as a report that is not directly accessible but can easily be made accessible as a report with one simple additional step in the process.

             

            HTH.

             

             

            Grant

            • Excel Sheet Import
              mcromer _

              Gentlemen -

               

              Thank you for your responses on this.  Grant, as you suggested I have been saving the excel file as a .prn, then using "report" and trapping the needed data.  I was hoping pro would take out that step.

               

              Thanks again!

              • Excel Sheet Import
                Grant Perkins

                Originally posted by mcromer:

                   I was hoping pro would take out that step.

                 

                /b[/quote]I think the nearest you would get would be to run an initial extract for just the report header and use it as a lookup table  - basically what I suggested previously.

                 

                Whether that is any better in any respect than what you do already I don't know. It would probably be a 50:50 decision with no real benefit either way.

                 

                 

                Grant