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

    Data Extraction -

    lakshmi_pili _

      :confused:   

       

      We just purchased Monarch V6.01, and have been reading the learning guide.  I am really confused.  I am trying to extract data, and it does not seem to be working.  I have managed it to be able to select most of the data, but the problem I am having is when the person's last name contains a suffix, in which case it takes the first part of it as the last name, and the suffix as the first name.  I don't know how to get around that one.  The other problem is when the person does not have a middle name.  It does not select those two detail lines at all.  If I have two fields which are numeric, but there is no space between them, how do I make them as separate fields?  e.g. 0331002029108:  The first six character are supposed to be one portion of it, and the second part is another field.  Any suggestions. 

       

        [font="courier"] 1A    59600543                                         

       

      1B             IBM                                                      

      2B              CHARLOTTE COUNTY SHERIFF'S OFFICE              7474 UTILITIESROAD                  PUNTA GORDA          FL 2417 33982  

      2E                                             S                                                                               

      1S 100-40-2566                            KOMAR   RAYMOND A                                          12   

      2S2           0331002029108         13                                          B                                                

      1S 100008401                              DAVENPORT           JOHN            G                                             12   

      2S2           0331002180842         13                                          B                                                

      1S 100008094                              LUCAS               DAVID           A                                             12   

      2S2           0331001635618         13                                          B                                                

      1S 100004084                              PENLAND             ANTHONY         H                                             12   

      2S2           0331001560810         13                                          B                                                

      1S 100008893                              BOYSEN              THOMAS                                                        12   

      2S2           0331001383780         13                                          B                                                

      1S 100009639                              TOOLAN JR           FRANK           J                                             12   

      2S2           0331001158168         13                                          B                                                

      1S 100005443                              HAUSE               JOHN            T                                             12   

      2S2           0331001374166         13                                          B                                                

      1S 100000879                              ANGELINI III        JOSEPH                                                        12   

      2S2           0331001648350         13                                          B                                                

      1S 100003963                              ROBINSON            SHERMAN         A                                             12   

      2S2           0331001467734         13                                          B                                                

      1S 100000936                              GANDY               MICHAEL         W                                             12   

      2S2           0331001879347         13                                          B                                                

      1S 100003277                              SBARBORI JR         PETER           E                                             12   

      2S2           0331001462196         13                                          B                                                

      1S 100000518                              SAVAGE              MICHAEL         F                                             12   

      2S2           0331001249497         13                                          B                                                

      1S 100008068                              CROWL               RANDY           L                                             12   

      2S2           0331001324548         13                                          B                                                

      1S 100003981                              BENNETT             KEITH           M                                             12   

      2S2           0331000823896         13                                          B                                                

      1T00005350000467397903                                                                               

      1F0000535

      /font[/quote]Thank you.

       

      [size="1"][ June 17, 2003, 08:54 PM: Message edited by: Mike Urbonas ][/size]

        • Data Extraction -
          Grant Perkins

          Lakshmi,

           

          Your data layout may have lost something in the post but I have cut and pasted it into a text file and get 2 lines. The first line starts with something like

           

          1S 100009639 .... 

           

          Actually the first line starts 1S 100-

           

          the dashes look like data anomaly.

           

          If I trap on that line using the "1S " (Numeric/Alpha/Space)and put 2 lines into the template I think I get a full record.

           

          If I let Monarch (5.02 for me at my current location) select the fields I get separate fields for Surname  (with suffix)[/b], first name and initial(s). If you want to separate the suffix we can come back to that. You can of course specify the position and size of the fields you want if necessary.

           

          The long numeric string is presented as a string of one field. Delete that field and create 2 separate fields that fit the lengths you require. Remember that you are looking at a data file 'dump' not a proper report format so there will be some things you will have to set up as they should be not as they appear.

           

          Separating the suffix.

           

          If you need to split the surname and suffix you probably need to create 2 calculated fields, putting the name into one of them and the suffix into the other. Have a look at the LSPLIT function. It should be self explanatory BUT, if you have problems, let us know and we can provide some specific help for that point.

           

          Good luck. It is really very easy BUT can look a little confusing to start with. Don't give up, an hour or so 'learning' will make all the difference. If you find you are struggling I can make a starter model for you but it would be best to work with a 'real' file (or part of one) in case there are any odd factors to deal with which are lost in the posting. Send me a private message and I will supply an email address for direct contact if you think it would help you.

           

          Best regards,

           

          Grant

           

           

          Originally posted by lakshmi_pili:

            :confused:   

           

          We just purchased Monarch V6.01, and have been reading the learning guide.  I am really confused.  I am trying to extract data, and it does not seem to be working.  I have managed it to be able to select most of the data, but the problem I am having is when the person's last name contains a suffix, in which case it takes the first part of it as the last name, and the suffix as the first name.  I don't know how to get around that one.  The other problem is when the person does not have a middle name.  It does not select those two detail lines at all.  If I have two fields which are numeric, but there is no space between them, how do I make them as separate fields?  e.g. 0331002029108:  The first six character are supposed to be one portion of it, and the second part is another field.  Any suggestions. 

           

             /size[quote]quote:[/size] 1A    59600543                                         

           

          1B             IBM                                                      

          2B              CHARLOTTE COUNTY SHERIFF'S OFFICE              7474 UTILITIESROAD                  PUNTA GORDA          FL 2417 33982  

          2E                                             S                                                                               

          1S 100-40-2566                            KOMAR   RAYMOND A                                          12   

          2S2           0331002029108         13                                          B                                                

          1S 100008401                              DAVENPORT           JOHN            G                                             12   

          2S2           0331002180842         13                                          B                                                

          1S 100008094                              LUCAS               DAVID           A                                             12   

          2S2           0331001635618         13                                          B                                                

          1S 100004084                              PENLAND             ANTHONY         H                                             12   

          2S2           0331001560810         13                                          B                                                

          1S 100008893                              BOYSEN              THOMAS                                                        12   

          2S2           0331001383780         13                                          B                                                

          1S 100009639                              TOOLAN JR           FRANK           J                                             12   

          2S2           0331001158168         13                                          B                                                

          1S 100005443                              HAUSE               JOHN            T                                             12   

          2S2           0331001374166         13                                          B                                                

          1S 100000879                              ANGELINI III        JOSEPH                                                        12   

          2S2           0331001648350         13                                          B                                                

          1S 100003963                              ROBINSON            SHERMAN         A                                             12   

          2S2           0331001467734         13                                          B                                                

          1S 100000936                              GANDY               MICHAEL         W                                             12   

          2S2           0331001879347         13                                          B                                                

          1S 100003277                              SBARBORI JR         PETER           E                                             12   

          2S2           0331001462196         13                                          B                                                

          1S 100000518                              SAVAGE              MICHAEL         F                                             12   

          2S2           0331001249497         13                                          B                                                

          1S 100008068                              CROWL               RANDY           L                                             12   

          2S2           0331001324548         13                                          B                                                

          1S 100003981                              BENNETT             KEITH           M                                             12   

          2S2           0331000823896         13                                          B                                                

          1T00005350000467397903                                                                               

          1F0000535

          /quoteThank you. /b[/size][/QUOTE]

          • Data Extraction -
            Steve Caiels

            Hi,

             

            Are you grabbing the name as several fields?  If you are, then you can create a calculated field to add them back together in the correct order.

             

            So, you can get a table like this,

            [font="courier"]

            Part1            Part2      Part3

            Komar            Raymond    A

            Toolan Jr        Frank      J

            Boysen           Thomas

            Angeleni III     Joseph[/font][/quote]Then create a calculated field called fullname using an expression of

             

            Part2 + “ “ + part3 +” “ + part1

             

            This will give

            Raymond A Komar

            Frank J Toolan Jr

            Thomas Boysen

            Joseph Angelina III

             

             

            Finally, create a series of calculated fields using the Nsplit function.  This splits the name into 5 parts and should sort out prefixes (Dr, Sir etc) suffixes, initials

             

            You'd need up to 5 calculated filed with expressions of Nsplit(fullname,1), Nsplit(fullname,2) etc

             

             

            Regards

            Steve

             

            [size="1"][ June 23, 2003, 05:35 AM: Message edited by: Steve Caiels ][/size]

            • Data Extraction -
              Grant Perkins

              Lakshmi,

               

              Further to my last post I have played a little more and spotted that the first few lines of the report sample have an odd appearance. The detail line trap I suggested in my previous post includes a couple of the odd lines at the top of the report. A better trap would seem to be for lines beginning "1S 1" for example. This would not select the lines at the top of the report which seem to be related to something else.

               

              Generically this would be a trap using (Numeric/Alpha/Space/Numeric)and put 2 lines into the template.

               

              Steve's response indicates yet more possible ways to satisfy your ultimate requirements. Which way is best for you for this task just depends on what you need to achieve as the final output.

               

              One of the many great things about Monarch is that there are so many ways to approach a problem and complete a challenge!

               

              Have fun.

               

              Grant