4 Replies Latest reply: May 15, 2014 10:02 AM by paulmcelhinney _ RSS

    Create model for Excel input with multiple worksheets

    paulmcelhinney _

      Hi All,

       

      I am new to Monarch and I have been looking into importing data from excel documents to DB2 (excel documents get uploaded through the web application).

       

      I have learned about opening a report file and creating a model, and then creating a project and process with Datapump that I can call from my web application.

       

      The only thing I am having problems with is how to open the excel document and create the model for extracting data from it. (I need the model for datapump to use) When I open it as a database it opens in table format and I cannot define a model to use to extract the data from the excel file in future.

       

      Will I need to save the excel worksheets as prn files through my application first or is there another way??

       

      Any help or information is greatly appreciated.

        • Create model for Excel input with multiple worksheets
          Bill Watson

          When you use the Open Database option and get to the table view, you can save the model as normal to keep your import settings. You can't define fields like you would a text report as the import wizard automatically picks up column headings etc from your source. You can of course edit these names in the table view, and hide columns you don't want - basically anything you would do in the table view of a text report.

           

          To always pick up the same file, use the save project option. Monarch projects store information on the import files to use and which exports are required.

           

          The only limitation with this is you can only have one excel file open at a time using this method. You can of course use external lookups to add additional data from excel files still but the Open Database allows you only one datasource.

           

          You can use scripting to automate multiple loads using this method.

           

          Your other option would be as you suggested, saving your excel files out to text(prn) and then opening them all at once and applying your model.

           

          HTH

            • Create model for Excel input with multiple worksheets
              paulmcelhinney _

              Thanks for the quick reply...

               

              I see you can hide columns etc that you dont need like you said, unfortunately my excel sheet is not formatted in strict table format, it is similar to the Employ.prn sample file that is included in Monarch.

               

              I have some header information in each group of records that I will also need, for example an account number and date for each group of records.

               

              I have done a sample using a prn file where you can create "append" templates onto your main template that can append this type of master/child data into your table view.

               

              Do you know if there is any way you can define this using excel as input file?

               

              Thanks again for your help..

                • Create model for Excel input with multiple worksheets
                  Bill Watson

                  The only way I could think of it working using the database option would be to have the header details duplicated as additional fields for each record within excel prior to opening in monarch.

                   

                  I think though your simplest option is to build a process to refresh the data to a prn file and then open as a standard text report.

                   

                  This also gets around some of the issues we have with using the database option - field widths are set to the maximum for the sample dataset used, so if you have any fields which might allow longer values, you can find these are often truncated. Additionally the formatting can be an issue as well as file locking.

                    • Create model for Excel input with multiple worksheets
                      paulmcelhinney _

                      Ok I think this is along the right track alright.

                       

                      What exactly do you mean by "[B]build a process to refresh the data to a prn file[/B]", I was trying this and I need to create a model for a process to use to convert the excel to prn and this would mean I need to open excel document as a database right?

                       

                      When I do this I can get it to convert the excel to prn but when I open the generated PRN in Monarch to create a new model to actually extract the proper data it is in comma separated format rather than the nice format you get when saving directly from excel to PRN..

                       

                      Sorry to keep asking but I am new to this and I'm trying to get my head around it all..

                       

                      Thanks again for your help with this..