10 Replies Latest reply: May 15, 2014 10:08 AM by Shutson _ RSS

    name, address all in one line

    Shutson _

      Is there anyway to trap and extract this data correctly?  I am hitting all kinds of issues with it.  The data looks like:

       

      Joe P Smith,123 Valley Way,Yourcity,ST,12345-1234

      Sue M Doe,234 Summit St,Apt 4,Anycity,ST,13570

      John L Mays,23571 Church St,Allcity,ST,12378-1357

       

      So when I trap on the zipcode (to exclude other lines) I was using the ,NNNNN (numerics), floating trap.  That then picked up the third line's street address.  The second issue is the second address line in some addresses...Third issue is that  user needs the name in 3 fields but I can't seem to do a floating trap using blank...Any ideas out there?  And this is just the beginning of my issues with this report...

        • name, address all in one line
          Olly Bond

          Hello Shutson,

           

          No need to use the floating trap here. Trap with a non blank character in column 1 so you trap every line. Define a character field of maximum width (254 characters, I think) and add more fields to right if required.

           

          Then in the table, make a memo field which consists of +... Now you have all the data in a memo field, and can use the Lsplit function to break out the Name from the Address by splitting on the comma. Then you can use the Monarch's Name functions to break up the name and the Address Block feature to parse the address.

           

          HTH,

           

          Olly

            • name, address all in one line
              Shutson _

              Sorry, I was remiss in just presenting a small sample of the data.  Here is what the whole entry looks like:

               

              0123456789  Smith, Joe P  1/1/10  12/1/09  14.77

                   Joe P Smith,123 Valley Way,Yourcity,ST,12345-1234

                   JTWR  000-00-0000  08/27/91  Susan R Smith

                   JTWR  000-00-0000  11/21/52  Charles G Smith

               

              There are 30 or so entries like this.  All have the first two lines, some have 1 JTWR lines, some have 2.

               

              The last 3 lines are indented about 5 spaces.  It isn't showing that way here when I save it.

                • name, address all in one line
                  Olly Bond

                  Hello Shutson,

                   

                  Assuming the data looks like

                   

                  0123456789 Smith, Joe P 1/1/10 12/1/09 14.77

                       Joe P Smith,123 Valley Way,Yourcity,ST,12345-1234

                       JTWR 000-00-0000 08/27/91 Susan R Smith

                       JTWR 000-00-0000 11/21/52 Charles G Smith[/CODE]

                   

                  then just select a two line sample, trap on non-blank in column 1 of line 1, select your fields from line2, and use Lsplit(), Name() and Address Block as before.

                   

                  Best wishes,

                   

                  Olly

                    • name, address all in one line
                      Shutson _

                      Since I want every piece of data in this block, I used the 1st line as my detail template with separate fields, appended the second line as one field, and appended the third line, as separate fields.  My result is that the record only contains the data for the last of the third line-template.  In otherwords, I want both the data for the JTWR line with Susan Smith and the data in the line for Charles G Smith, but I'm getting just the data for Charles G Smith.  The other issue is that some of these blocks have one JTWR line, some have two, and some have none.  When it is there we need it.  Any thoughts?

                        • name, address all in one line
                          Olly Bond

                          Hello Shutson,

                           

                          Appending data below the detail will give you errors in the table - use Footer templates instead, but in this case even that isn't the right approach.

                           

                          Define a two line template on lines 1 and 2, and trap on line 1 as currently, then  select the fields from line 1 that you want. Now select a field for the whole of line 2, and double click on this to open the properties. Click on the advanced tab and then select "End field on" - "none of the above". You'll now have a field that expands to contain line 2, 3 and 4 where required.

                           

                          In the table, you can unpack this using the Textline() function to get the data you need into calculated fields.

                           

                          HTH,

                           

                          Olly

                            • name, address all in one line
                              Shutson _

                              The textline function looks like it would work great, however it isn't breaking where the carriage returns are showing when I open the file in Word.  what am I missing here?

                                • name, address all in one line
                                  Olly Bond

                                  Hello Shutson,

                                   

                                  Word isn't the most appropriate tool - Monarch works with text files, which should be easy to open in Notepad. Tabs, tables, soft returns etc can all show affect text tendering in Word. The underlying ASCII characters may be ignored by Monarch.

                                   

                                  HTH,

                                   

                                  Olly

                                    • name, address all in one line
                                      Shutson _

                                      When I have the second line (name and address) in 1 memo field, I used the NSplit function to extract the name but it doesn't remove it from the line so that when I try to extract the address I get the name as part of the address.  Also, it doesn't extract the address by adress line separated by commas - if I could just parse this line based on comma and get all names to line up, all cities, states, zips to line up that would be perfect...I tried both the address block and PSplit functions...any ideas?

                                        • name, address all in one line
                                          Olly Bond

                                          Hello Shutson,

                                           

                                          Let's assume the whole line is one big field called .

                                           

                                          You can get the name out easily, as it's everything before the first comma. Define a calculated field called as:

                                           

                                          lsplit(;2;",";1)[/CODE]

                                           

                                          Now if you want the break out surnames and initials and so on, use Nsplit(...

                                           

                                          Define another calculated field, of everything to the right of the first comma, called as:

                                           

                                          lsplit(;2;",";2)[/CODE]

                                           

                                          Now try defining an Address Block based on this field.

                                           

                                          Best wishes,

                                           

                                          Olly

                                            • name, address all in one line
                                              Shutson _

                                              Hooray!!  I managed to extract all the data that the user needs.  Olly, thanks so much for all your assistance and education!  The data was very inconsistent so I was not able to use as many "efficient" functions as I should have but using combinations of LSplit and other functions allowed me to extract the data successfully.  I've been using Monarch for about 17 years but never to this degree before!