9 Replies Latest reply: Dec 9, 2014 10:01 AM by Michael Vasas RSS

    Transpose like Extract

    Michael Vasas

      Hi All,

       

      I am confused about how to approach an issue I am having.  I have many PDFs that have data set up in them with the following format:

       

      - - - - I T E M - - - -     - - - - - - - F R O M - - - - - - - -     - - - - - - - - T O - - - - - - - -

      Group no.                        01               NOFRILLS JOHN'S #0713          97               NO FRILLS LIONEL'S # 3948

      Division                           000             First division                              999              Last  division          

      Sub-division                     000             First sub-division                        999              Last  sub-division      

      Department                                        First department                         ZZZZZ         Last  department        

      Distribution                      0000000000 First distribution                          9999999999 Last  distribution      

      Reference                                          First reference                            ZZZZ           Last  reference         

      Extended function                               First function                              ZZZZZ         Last  function          

      Earning / deduction code  (919)            GST ON CLOTHING                 (919)             GST ON CLOTHING         

       

       

      What I am trying to accomplish is a kind of transpose that would be similar to what Excel can do.  I say transpose because all the ITEMS like Group no., Division, and etc are actually what we want as headers to be displayed across as the column headers. So, as an example:

      |                Group no.             |            Division               | ...

      |                01                        |            000                     | ...

      |   NOFRILLS JOHN'S #0713 |             First Division      | ...

      |                97                        |            999                     | ...

      |  NO FRILLS LIONEL'S 3948|             Last Division       | ...

       

      As it is, I am trapping whole lines and they are just spitting out in typical type rows.  Honestly, I can also take the approach of getting these rows out of Monarch and export them into Excel and then writing a Macro that will transpose but I'm sure that there has to be a way to do it all with the very powerful Monarch.

       

      Any suggestions are very appreciated.

        • Re: Transpose like Extract
          Eamon Donoghue

          Hi Michael,

          I'm not sure I understand your requirements fully without seeing a bit more of your data.  However, from what I see, it appears that you could accomplish your goal using Multi-Column Regions and a multi-line Detail template and removing duplicates using a filter.  In other words, if you take the eight lines in your example as your sample lines and create a Detail Template based on that sample.

           

          I'm actually rushing out the door in -5 minutes so I've only had a very quick look at this and I have created something similar to what you need.

           

          I'd be a bit concerned that the PDF format (as often happens) is inconsistent in terms of row-width.  But sometimes data copied and pasted from PDFs loses the 'shape' slightly in the process.

           

          I hope I've understood your question.  If not, please feel free to PM me including a bit more sample data (perhaps a sample PDF if the data isn't sensitive or you can generate some test data in the same PDF format) and I'll take another look.

           

          hth

           

          Best regards,

          Eamon Donoghue

          Monarch Experts.

            • Re: Transpose like Extract
              Michael Vasas

              Hi Eamon,

               

              I had originally tried using Multi-Column regions however I was having a hard time setting the spacing between each column as there are no consistent lengths.  I am learning though, and have a huge manual to work with but so far I haven't been able to tweak the column width.  I can increase or decrease the column width however all column widths will change.  Mayhap I'm missing something, I will keep at it. 

               

              Also, I think you understand my question as your first section theoretically answers it, now I just need to implement it.

               

              Thanks,

               

              Michael

               

              Monarch Expert in the Making!

                • Re: Transpose like Extract
                  Eamon Donoghue

                  Hi Michael,

                  I find that MCR can be a bit of trial and error.  The biggest challenge I see with the sample in your original post is the inconsistent column widths.  This is certainly not going to help with MCR.  But I can't be sure if the data appears like this in the Modeler Report window.  If the columns are consistent, it makes the job a lot easier as you can usually do some work with Calculated Fields in the Table window.  If the columns are as in your sample, you will probably have a lot of cleaning up to do .

                   

                  As per my earlier reply, if you want to send me a sample PDF, I (or one of my Monarch Experts colleagues) will happily take a look at it.

                   

                  Best regards,

                  Eamon Donoghue

                  Monarch Experts.

                    • Re: Transpose like Extract
                      Eamon Donoghue

                      Hi again Michael,

                      Just a thought - people often get caught out when using MCR in that they forget that the MCR shouldn't include the labels.  So in this case, the Left position of First Column should be around 25 or so (I cleaned up the data somewhat in Notepad++ before building the model, do I can't be sure of the original column positions.)

                       

                      hth

                       

                      Eamon.

                        • Re: Transpose like Extract
                          Michael Vasas

                          Hi Eamon,

                           

                          Sorry for the delay in my reply!  You are right, there are no consistent column widths which is making this a more difficult task.  I have been playing around with the idea of MCR however because of inconsistencies I do not feel it will work out so well.  I can see how my first post is confusing and wold like to upload an image of what I have (I am not aloud to upload the PDF) however the "insert Image" button is not active here for me. The problem is with how my team wants to view the data after Monarch, All the row fields like Group No., Division, and etc were wanted as column headers.  It's confusing, I know. 

                           

                          In saying this, I have convinced 2 of my team members to output this in a different way which may or may not be difficult to accomplish, I am working on it today.

                           

                          Thanks a lot for your time, I'm not going to mark this thread as solved quite yet, I will if/when I am aloud to change the output layout.

                            • Re: Transpose like Extract
                              Michael Vasas

                              Really, the best way that I'm understanding requirements, at the moment, are my team wanting me to reverse a pivot table like layout using Monarch.  I can program Excel quite well so I think my approach will be in two phases:

                              1. Scrape the PDFs using Monarch and output it to Excel
                              2. Reverse Pivot the data using VBA

                              Fun fun fun!

                              • Re: Transpose like Extract
                                Michael Vasas

                                Hi Eamond, sorry for mis-spelling your name in my previous post.

                                  • Re: Transpose like Extract
                                    Eamon Donoghue

                                    Hi Michael,

                                    Thanks for your reply and the additional info.  And btw, you didn't get my name wrong - eamond is my username.  Anyway, no harm done!

                                     

                                    If you take a look at the Webinar on Invisible Data from my Monarch Experts colleague Olly Bond, there's a hint about using MCR as a workaround to extract such data. You should find Olly's webinar here: http://www.datawatch.com/explore/on-demand-webcasts/    Apart from giving a lot of helpful advice on using Modeler 'metadata', it might give you some help in solving this problem.  Basically, the technique involves setting up a Multi-column region thereby forcing Modeler to present the data in Columns (which is what I think you need) rather than in Rows which is its normal approach.

                                     

                                    I fully understand the need to protect your data and would recommend this as best practice.  However, in this case, it would help to see any patterns or repeating characters in the data.  This would help in creating Calculated Fields to further process extracted data.  If you can get permission to send a sample to my private email address (eamon@monarchexperts.com), I would be happy to try to help you further.

                                     

                                    Good luck with this - it's a tricky problem but you seem to be on the right track.

                                     

                                    hth

                                     

                                    Best regards,

                                    Eamon

                        • Re: Transpose like Extract
                          Michael Vasas

                          Just an update.  I had the requirements revised a little to make this extraction a little easier, they have agreed to view the data exactly as it is on the PDF so no Transposing necessary.  I'd like to thank eamond and ollyinmunich for their help on this matter. 

                           

                          What a great forum this is!