1 Reply Latest reply: Nov 25, 2014 2:10 PM by Olly Bond RSS

    Reading an Excel file like a Report

    ARICE01 _

      I would like to use Modeler 12 to read and Excel file and have it interpret a series of column data as "Appended" data under each succeeding Column... For example:

       

      Source Excel file:

       

      Col 1 = Row Description (key)

      Col 2 = Row Description (attribute 1)

      Col 3 = Row Description (attribute 2)

      Col 4 = Data Element 1

      Col 5 = Data Element 2

      Col 6 = Data Element 3

      ...

       

      Desired Output file

      Col1 Col 2 Col 3 Col 4 (heading is DE Name) Col 4 Data

      Col1 Col 2 Col 3 Col 5 (heading is DE Name) Col 5 Data

      Col1 Col 2 Col 3 Col 6 (heading is DE Name) Col 6 Data

      ...

      I know this is possible with Report Mapping, but I do not know how to accomplish this an Excel file with 150 columns of data. I am sure a VBA script could do this, but I am not a good VBA programmer.

       

      I hope my problem has been explained well...

       

      Any advise would be helpful.

       

      Al

       

      Message was edited by: ARICE01 _

        • Re: Reading an Excel file like a Report
          Olly Bond

          Hello Al,

           

          This would be solved if the Multi-Column Region could be applied to database sources as well as to reports. You could then make an if statement - if (column()=1;ValueofColumn4;if Column()=2;ValueofColumn5...) and so on. Even better would be to be able to define Appends and Footers based on the layout of an Excel. Tableau have got an Excel plugin to unpivot some (fairly simple) cases of across-key layouts, but it's nowhere near the power of Monarch in being able to read any human-readable report.

           

          I've hinted at this issue to developers at Datawatch but we haven't got a clear way forward on it. Is it best to somehow use Excel's drivers to turn it into a PDF and parse it from there? Or stick to a two model approach - open the Excel in Monarch, export the table as fixed width text, and then model that (with Appends, Advanced Field Options, MCR etc) in a second model? Generally, I want to be able to handle anything in one model - because then Datawatch Designer, and Datawatch Server, can parse it easily on the fly without needing to write out and read in any in-between steps from the file system.

           

          I'm fighting to get the Custom Trap implemented in Modeler 12.5 - that will help massively with strange reports and OCR data - but I get the impression that Datawatch are pushing more of their development efforts more in the direction of dashboards than in taking Monarch forward. If you'd like me to connect you with the managers of Modeler and Automator to discuss your idea, please drop me a mail.

           

          Best wishes,

           

          Olly