14 Replies Latest reply: Jul 6, 2016 5:15 AM by Grant Perkins RSS

    Need help Capturing data

    Ravi Paw

      Hi All, I was wondering is someone can help me. I have the following report and I am struggling to capture some data. The data under ProZone I am trying to capture. However I would like to capture the digits within the square brackets only. For example I can use the "Detail template" to capture the number 33. But then struggle to capture in individual templates the numbers 36, 43, 46, 49 and 52 (can these be captured in an append template?).


      I will hazard a guess and suggest somethink like enabling Multi Column Region followed by enable MCR mode may work, but correct me if I am wrong, if so, can anybody aid in this as I am relatively new to DW. Thanks, Ravi


      ITEM#1        [KFree]                                                                               Co-ordinate                 

         Units[mg/L        ] Dec.P[2]                                                                         Y=[1.000]X+[0.000]       

                                                                                                           Mon.     Span[3.000] ZeroP[1



         Type[Logit 2                 ]                                                                    Read     Main[53]-[54]      

         1.[0.400   ]                                                                                               Sub [35]-[36       

         2.[1.490   ]                                                                                      ABS      [-3.000]-[3.000]   

         3.[3.480   ]                                                                                      Lin.     [0 ] EPLmt[2.000 ] 

         4.[8.940   ]                                                                                                                  

         5.[12.920  ]                                                                                                                  

         6.[17.880  ]                                                                                      Factor: Blank Cor.[1      ] 



         Method-                                                                                           ProZone                     

         Type   [END]                                                                                         Read   1[33]-[36]       

         Color [600]-[]                                                                                                     2[43]-[46]       


                                                                                                                       Limit    2[83   ]-[High ] 

         N-Range Serum                                                                                                3[82   ]-[High ] 

         M  [     ]-[          ]                                                                              delta OD[0.039   ]       

         F  [     ]-[          ]                                                                                                      

        • Re: Need help Capturing data
          Chris Porthouse

          Greetings Ravi.


          I am going to make a couple of assumptions, so let me know if anything is not correct.

          1) You are on at least v13 of Monarch.

          2) The sample you provided is text format and the data is relatively consistent in the layout


          You can make a multi-line detail template that traps on the first line looking for ProZone.

          I would then suggest creating a memo field type and grabbing the first line below ProZone and giving a name like RawData.  Click on the edit field button above your trap line:

          Click on the Advanced tab and select the radio button to end field on Blank field values: 1

          This should bring all of your raw data into one field.  You can then use a combination of TextLine(), RegexExtract() to break down each row.  For instance, to get the first line:



          Read   1[33]-[36]

          I then created a couple of more formula fields to grab the different values:


          returns: 33



          returns 36


          You still need to clean this up a little bit, but hopefully this gets you moving in the right direction.  Make sure to click on each of the above image to see it completely.

          • Re: Need help Capturing data
            Mario Ruiz

            Hi Ravi,


            This is how I would do it.  I would select the whole report without trapping:  All.  Once I have everything trapped in one column I would append the word ProZone.  I would name this field ProZone.


            This word will appear next to the ProZone Area.


            Then  I would open a new field called All_Prozone



            If(all<>" ",all+" "+Prozone," ")


            I would split that column into 2 with the substring function I would name it ProzoneFirstNumbers




            I would create a field called ProzoneFirstNumbersFeed By doing the following extraction




            Now you can extract this column and create two columns from each extraction




            If(Prozone<>" ",Extract([ProzoneFirstNumbers_Feed],"","]")," ")





            If(Prozone<>" ",Extract([ProzoneFirstNumbers_Feed],"-[","]")," ")




            • Re: Need help Capturing data
              Grant Perkins



              Is the output file you are working with really formatted as erratically as the forum presents it?


              Output files can be very randomly confusing in their presentation in a forum for a number of reasons.


              When one is trying to extract data from a file a regular layout is incredibly useful for making life easy (most of the time) but if the layout is never regular a different approach from the start makes a lot of sense.


              Your sample sort of suggests that the format has lost something when the sample was posted. Is that the case?





                • Re: Need help Capturing data
                  Ravi Paw

                  Hi Grant, Thanks for your email. Yes, that is correct the format and layout shown in the screenshot above is what it usually is and looks like.




                    • Re: Need help Capturing data
                      Grant Perkins

                      Hi Ravi,




                      The appearance in Chris's screen shot is different.


                      I don't think MCR adds anything to the party here.


                      Do you have use of Monarch 13 or an earlier version?


                      If you are certain that the "Method" section is clearly your "Detail" data I would think a multi-line template trapped on ProZone would work. If ProZone moves around a lot on the line it might be better to consider trapping on "Method" and then making a filed that will always capture the method description and filtering using that after extraction. Doing that would mean that the same model may have potential use for other method types simply by adding suitable filters rather than changing the trap definition and needing create and maintain multiple models. Note that the powerful REGEX based option has only been available since V13.1.1 .


                      If the report always has the same number of lines in the Method section you can set the data selection for the Detail record to be that number of lines and then map those fields that are always in consistent locations (it there are any).


                      For those data areas that are not consistently located there are other options.


                      For example for fields that are not consistent located VERTICALLY - i.e. they are not always on the same line of the data block - the Preceding String option in the field definition (see Chris's screen shot) can be very powerful.


                      TYPE, COLOUR, READ and LIMIT would be examples in your target data area.


                      For the READ data a multiline field is likely to be appropriate. The sample shows 3 lines but might there be less or more lines depending on the results of the analysis? If so Monarch can deal with that.


                      Once the lines have been extracted from the report and added to the table the text data can be manipulated using both the TEXTLINE function as indicated by Chris and some combination of the LSPLIT and RSPLIT functions plus some of the other text manipulation functions mentioned by Chris and Mario and maybe a few not yet discussed. (Monarch has many ways to address text manipulation requirements and you can pick and choose what suits your needs best.)


                      If you have the V13.1.1 or later release and are into Regular Expressions you can also use the features described by Chris.







                        • Re: Need help Capturing data
                          Chris Porthouse

                          Hi Grant,


                          Just as an FYI, for my sample, I actually copied the data right from his post and pasted into notepad and the formatting came out as you see it in my screenshot.

                            • Re: Need help Capturing data
                              Grant Perkins

                              Hi Chris,


                              Ah, much as I feared ..... !


                              Thanks for the information.


                              The challenge I have found over the years is trying to work out the best approach to what appears to be erratically formatted reports.


                              With traditional "greenbar" structure reports one can usually make a decent guess as to what a strangely presented posted version should look like and make allowances for how the browser might be dealing with things. A cut and paste into Notepad often helps  - bit not always.


                              Less structured "technical reports" , like Ravi's example, can be more difficult to assess.


                              The thing is that is there is actually a good and consistent structure to work with the model requirements may be greatly simplified and that is always a good thing for getting quick and consistent results and future re-familiarisation for maintenance down the road (should it be required.) Also for making one model THE master for several jobs should that be likely.


                              I always hope to the leave the more challenging approaches to model definition work to the demands of nasty PDF files when they appear.


                              Looking at your screen capture the left side of the report in the report window looks like it is pretty well controlled and of a consistent format. The right side, however, gets messy in a way that is not so apparent in the original post. If the lines are in fact neatly aligned vertically getting at the required data values will be easier. In fact if they are consistently vertically and horizontally positioned record after record, as one might expect from a text file bit not necessarily a PDF file,  the model should be quite simple once the structure is "seen" in conjunction with the tools available to enable Monarch to extract only what is required.


                              I'll try a cut and paste and see what I get.




                                • Re: Need help Capturing data
                                  Grant Perkins

                                  Just tried the Cut and Paste and then played with different fonts in notepad and got a wide selection of results.


                                  I suspect it would a good idea to work with an original file or maybe a selection of files rather  than rely on what applications and browsers do to the content.





                                  Do you have a sample file (or more than one if things are not always presented consistently report by report) that we could experiment with?





                                    • Re: Need help Capturing data
                                      Ravi Paw

                                      Hi Grant,


                                      I am afraid I haven’t. See the thing is the report I pasted was from a PDF which was generated from a Scientific analyzer, so the report above that I initially posted is always consistent. There are other reports from the analyzer which are also consistent but the rows, columns and data are presented in a “easy” layout so to speak just like a table one would construct in word. This data I have managed to capture.


                                      Thank you


                                        • Re: Need help Capturing data
                                          Grant Perkins

                                          Hi Ravi,


                                          Ah, a PDF original. I suspected that - the text has a "from a PDF" look!


                                          A PDF original would be fine to experiment with.


                                          The spacing in that style of report can be an interesting challenge. Monarch has some controls to help with that, as you probably know, but PDF writer programs often do interesting things  - especially if they are seeking style of presentation over substance since they developer is not expecting people to want to change the content back to digital data.


                                          Is the PDF a text based document or an "image" that you have to OCR to recover the text?



                            • Re: Need help Capturing data
                              Ravi Paw

                              Hi All -OK so here is an update. I have managed to have a go at the data capturing from the guides posted by Chris and Grant and found both to be very useful.


                              1- I first captured the data using detail template and named this "RawData". This is then presented in one cell.



                              2- In formula fields I broke down the cell (RawData) as follows to get ProZone 1 - 6

                              Formula field Function: textline(RawData,1)



                              3- Then further broke down ProZone 1 - 6 to capture the individual number that I require.

                              Formula field Function: trim(Substr([ProZone1],14,2))



                              I am sure there maybe an option to obtain the result in step 3 from step 1, without having to do step 2 but I found this way to be more methodical.


                              Any thoughts are welcome.


                                • Re: Need help Capturing data
                                  Grant Perkins

                                  Hi Ravi,


                                  Looks good.


                                  There are other ways to achieve the same thing some of which can be especially useful if the position of the data values you require is inconsistently placed in the extracted field or fields or variable data lengths. However if you find that your input file is always consistent enough in format and content (i.e. always 2 digit values) It would be easiest to stick with what you have.


                                  You logical 3 step development process makes it easy to see what is happening at each stage of the operation. The intermediate fields can be hidden if required.


                                  If you would prefer to see things "tidied up" you can do so but bear in mind future maintenance and adaptation where "readability" may trump "power formulae"!


                                  To get to step 3 in 2 steps you can replace the [ProZone1] field (etc.) in your 3rd stage definition with the formula from stage 2 that created ProZone1. Put it in () rather than [].


                                  If you choose to do that I would be tempted to create  a comment area in the formula window that records the original separate formulas for ease of understanding when you (or someone else) goes back to at some future point and starts to wonder what it was all about!


                                  The simple and commonly used formulas are usually quite obvious but the "nested" ones usually benefit from some descriptive notes or structured presentation depending in the nature of the "nesting".