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

    Multi-Column?

    MKMS_GR _

      I've attempted several ways to extract data from the following and am getting frustrated. I need to be able to grab the information under each Page and there can be up to 7-8 lines under each. Any ideas? [font="courier"]ZONE DISPATCH INFORMATION

      GENERAL                                     PAGE#   1                                   PAGE#   2                                  

      ANY CODES UNDEFINED NOTIFY                ~IMMEDIATE FIRE DISPATCH                  ~IMMEDIATE POLICE DISPATCH               

      ALARM COMPANY                             PREMISE NOTIFY                                                                               

      JIMMY GRANT       407 677 3407            JIMMY GRANT       407 677 3407           

                                                JACKEY IVYLAND    407 745 1138            JACKEY IVYLAND    407 745 1138           

       

       

        1/22/07  11:17                                         SECURITY ALARM MONITORING                                      PAGE     2

                                                          CS DATABASE PRINTOUT  (21A)

       

      PAGE#   4                                   PAGE#   6                                   PAGE#   7                                  

      PREMISE VERIFY                            LOG ONLY                                  NOTIFY ALARM COMPANY                     

      JIMMY GRANT       407 677 3407                                                                               

      JACKEY IVYLAND    407 745 1138                                                                               

      PAGE#   8                                   PAGE# 100                                  

      PREMISE VERIFY                            I/P CBK 06/10/96                                                                   

      DISPATCH POLICE                                                                               

      JIMMY GRANT       407 677 3407                                                                               

      JACKEY IVYLAND    407 745 1138                                                                                /font[/quote]

        • Multi-Column?
          Grant Perkins

          Hi,

           

          OK I can extract the data OK but with a few extra bits that wiould need to be filtered out (due to an odd looking report top left and the "Page#" record 'header' not positioning consistently above the data blocks.

           

          I have assumed that the "Page#" rows will always be consistent in identifying the 'header' start of each column irrespective of how many rows of data were in each column in the record above.

           

          I treat each row within a "Page#" block as a detail for this exercise. There may be alternatives for multi-row fields if this is not suitable.

           

          0. Add a page header for Page 1 to make the report format work if using the sample!

           

          1. Create a PAGE HEADER template trapping on the "/  /" of the date. The data sample for this will be 3 lines (as far as I can tell.)

           

          2. Set up a Multi-column Region definition with start column 1, number of columns 3 and width 42.

           

          3. Create an APPEND TEMPLATE for column 'header' using the first "Page#" line which STARTS AT THE LEFT COLUMN OF THE PAGE. So effectively the second blocks of the records due to the explanatory info in the first section - which I assume is a one off situation for this exercise.

           

          Use PAGE# as the trap. Paint the field for the number where it will appear for the left most column only. Set the trap to be 'Floating'. The number field position from the other columns should now be visible as well if you are in the appropriate mode! OK that.

           

          4. Create a DETAIL Template of a single line for anything with any character at position 1 in the column. Paint the field to be as wide as it needs to be - I would guess 40 or 41 characters.

           

          OK that. Have a look at the table.

           

          You should see 2 columns so far. The Page# number column sand the Detail column.

           

          The Page# number column will have some blanks where the top left info has been included. The # will be blank and that can be used to filter out the columns (by non-inclusion.)

           

          Likewise in the "Detail" table column any "PAGE# xxx" values will appear FROM THE FIRST COLUMN ONLY - the header appear to be offset in cols 2 and 3 so are not picked up. I would guess that you can filter anyfields with "Page#" as the data in that column.

           

          So two simple filters combined into a compound filter for ease of development and testing should do the job.

           

          Not sure what you might need after that.

           

          If you want all the block data in a single record by Page number it may be possible but I am slightly cautious about possible complications in a full report when I look at the record for Page# 2!

           

          HTH. Let us know.

           

          Grant

          • Multi-Column?
            MKMS_GR _

            Grant;

             

            Thanks for the help.  Got a little closer but realized the sample I provided was probably not the best.  In the revised sample you'll note that there are headings of PAGE#  1,PAGE#  2 and so on.  What I need is the data below each of those sections.[quote][font="courier"]                                                    CS DATABASE PRINTOUT  (21A)

             

             

             

             

            GENERAL                                     PAGE#   1                                   PAGE#   2                                  

            ANY CODE UNDEFINED NOTIFY                 ~IMMEDIATE FIRE DISPATCH                  ~IMMEDIATE POLICE DISPATCH               

            ALARM COMPANY                             PREMISE VERIFY BOTH #'S                   NOTIFY THE FOLLOWING                     

                                                      PAUL WILLIAMS        (P)910 555 1498      PAUL WILLIAMS        (P)910 555 1498     

                                                      PAUL WILLIAMS     (CELL)910 555 4884      PAUL WILLIAMS     (CELL)910 555 4884     

                                                      LOU DIPIETRO            915 555 5825      LOU DIPIETRO            915 555 5825     

                                                      PAUL WILLIAMS           910 555 2174      PAUL WILLIAMS           910 555 2174     

                                                      WILLIAMS BEACH HOME     909 555 9228      WILLIAMS BEACH HOME     909 555 9228 

             

                 

             

            PAGE#   4                                   PAGE#   6                                   PAGE#   7                                  

            PREMISE VERIFY BOTH #'S                   LOG ONLY                                  NOTIFY ALARM COMPANY                     

            NOTIFY THE FOLLOWING                                                                               

            PAUL WILLIAMS        (P)910 555 1498                                                                               

            PAUL WILLIAMS     (CELL)910 555 4884                                                                               

            LOU DIPIETRO            915 555 5825                                                                               

            PAUL WILLIAMS           910 555 2174                                                                               

            WILLIAMS BEACH HOME     909 555 9228                                                                               

            PAGE#   8                                   PAGE#  10                                   PAGE# 100                                  

            PREMISE VERIFY BOTH #'S                   PREMISE VERIFY BOTH #'S                   |UPDATE PAGE                             

            DISPATCH POLICE                           DISPATCH FIRE RESCUE                      U/D ACCT 02/01/91                        

            PAUL WILLIAMS        (P)910 555 1498      PAUL WILLIAMS        (P)910 555 1498      UPD CBK 05/26/99                         

            PAUL WILLIAMS     (CELL)910 555 4884      PAUL WILLIAMS     (CELL)910 555 4884      UPD LAH 12/28/00 ZD                      

            LOU DIPIETRO            915 555 5825      LOU DIPIETRO            915 555 5825                                               

            PAUL WILLIAMS           910 555 2174      PAUL WILLIAMS           910 555 2174                                               

            WILLIAMS BEACH HOME     909 555 9228      WILLIAMS BEACH HOME     909 555 9228                                                  /font[/quote]Thanks again for the help

            -Greg

            • Multi-Column?
              Grant Perkins

              Hi Greg,

               

              Yep, that is exactly what you should get.

               

              Plus the PAGE HEADER deals with the report Pages (Not to be confused with the Page# RECORDS) where the printer would throw a page if you were printing.

               

              The only thing I don't know is how you plan to use the extraction.

               

              Based on the previous sample (because I have it readily available) and with the filters for the unwanted parts of the extraction applied I get this.

               

              [font="courier"]Page#     Detail

              1     ~IMMEDIATE FIRE DISPATCH

              1     PREMISE NOTIFY

              1     JIMMY GRANT       407 677 3407

              1     JACKEY IVYLAND    407 745 1138

              2     ~IMMEDIATE POLICE DISPATCH

              2     JIMMY GRANT       407 677 3407

              2     JACKEY IVYLAND    407 745 1138

              4     PREMISE VERIFY

              4     JIMMY GRANT       407 677 3407

              4     JACKEY IVYLAND    407 745 1138

              8     PREMISE VERIFY

              8     DISPATCH POLICE

              8     JIMMY GRANT       407 677 3407

              8     JACKEY IVYLAND    407 745 1138

              6     LOG ONLY

              100     I/P CBK 06/10/96

              7     NOTIFY ALARM COMPANY

                /font[/quote]Which could be summarised into a series of fields for each "Page#" records with a single "Detail" field if required (in maybe a couple of different ways).

               

              How do you need the data to look once you have finished with it?

               

              If you can't get the same as above in the Table send me a PM with your email address and I will send the files  - the model and my sample just in case the C&P activity to create it has altered something subtly.

               

              HTH.

               

               

              Grant