Loading ...
Sorry, an error occurred while loading the content.

21871Re: [Clip] Re: add commas in address string

Expand Messages
  • Art Kocsis
    Jul 2 6:40 PM
    • 0 Attachment
      At 07/01/2011 06:06, Mike Breiding wrote:
      > Postal code: I need to replace the space in front of the first number
      with a comma
      > State abbreviation: I need to replace the space in front of the first
      letter with a comma
      > City : I need to replace the space in front of the first letter with a comma

      At 07/01/2011 11:21, Mike Breiding wrote:
      >I would still like to get this right as I have another list of addresses
      >See: http://epicroadtrips.com/parks_list/
      >It was extracted from PDF and is a bit of a mess.

      Mike,

      Whenever I have a one-off project such as this that does not lend itself to
      a simple clip I find it is much more efficient to just manually convert the
      file to a fixed field format by making use of NTB's block edit functions.
      Your parks_list file would only take about 5-10 minutes to convert. (In
      fact, writing this response took much longer than it would have taken to
      convert the entire file!<g>)

      In essence:

      - First I convert (via simple clips), all tabs to spaces and
      all \R to \r\n (RegEx replace statement)
      - Next, starting from the left, I insert (via S&R), a large number
      of spaces in front of each desired field
      - Next, I find the longest entry in the left most field and set the cursor
      a few spaces beyond that col position on the last row and SHFT-Click
      at the beginning of the first row.
      -Then right click and select "Cut Block" then "Left Align", move the cursor
      to the start of line 1 and right click "Paste Block". (The Left Align
      is a little trick to quickly align the next field for the entire file
      as it is already selected.)
      - Repeat for the next left-most variable length field.

      After all fields have been converted, it is now simple to insert any field
      separator desired, re-arrange, extract or delete fields, etc. In your case,
      change all "| " to ", " and delete all multiple spaces.

      For small numbers of lines, instead of the block cut & paste, I set the
      cursor just beyond the longest entry but on the first line and use a simple
      clip to do multiple CNTL-DEL, DOWN-ARROWs (about 5 or 10 per invocation).

      ^!Keyboard CTRL+DELETE
      ^!Keyboard Down
      ^!Keyboard CTRL+DELETE
      ^!Keyboard Down
      ^!Keyboard CTRL+DELETE
      ^!Keyboard Down
      etc

      I put all three clips (tabs, new lines, Del&Down) on my toolbar for one
      click operation and include the right click functions in my Shortcut Menu
      (View | Options | Shortcut Menu)

      An example in gory detail follows:
      (Alternately, Since there are a lot of long lines, download the example
      file from:
      http://www.box.net/shared/998tzn0qhdi23hbvgtku)

      Comments on the RegEx pattern used:
      Even though the \K removes the preceding pattern from the capture,
      the "(...)" still counts as a subpattern.
      The leading \R is a work around for the mishandling of "^" and
      requires an initial empty line
      It would REALLY be nice if the "^" operator worked as per spec
      instead of being ignored by NTB! Then ^(.*?\|){4}\K(.*?$) would
      be the preferred pattern and not require an initial empty line.


      Sample extract from http://epicroadtrips.com/parks_list/ (long lines)
      --------------------------------------------------------
      BANDELIER|NM|Jason Lott|15 Entrance Road|Los
      Alamos|NM|87544-9508|505-672-3861|505-672-9714
      BIG BEND|NP|Bill Wellman|P.O. Box 129|Big
      Bend|TX|79834-0129|432-477-2251|432-477-1172
      BIG HOLE|NB|Stephen Black|P.O. Box 237; 16425 Highway 43
      West|Wisdom|MT|59761-0137|406-689-3155|406-689-3151
      BIG SOUTH FORK|NR&RA|Stennis Young|4564 Leatherwood
      Road|Oneida|TN|37841|423-569-9778|423-569-5505
      BLUE RIDGE PARKWAY|PW|Phil Francis|199 Hemphill Knob
      Road|Asheville|NC|28803|828-271-4779|828-271-4117
      HERBERT HOOVER|NHS|Cheryl Schreier|P.O. Box 607; 110 Parkside Drive|West
      Branch|IA|52358-0607|319-643-2541|319-643-5367

      After spaces insertion before address field (after 3 "|"s)
      using: "\R(.*?\|){3}\K(.*?$)" >> " $2"
      BANDELIER|NM|Jason Lott| 15 Entrance Road|Los
      Alamos|NM|87544-9508|505-672-3861|505-672-9714
      BIG BEND|NP|Bill Wellman| P.O. Box 129|Big
      Bend|TX|79834-0129|432-477-2251|432-477-1172
      BIG HOLE|NB|Stephen Black| P.O. Box 237;
      16425 Highway 43 West|Wisdom|MT|59761-0137|406-689-3155|406-689-3151
      BIG SOUTH FORK|NR&RA|Stennis Young| 4564
      Leatherwood Road|Oneida|TN|37841|423-569-9778|423-569-5505
      BLUE RIDGE PARKWAY|PW|Phil Francis| 199
      Hemphill Knob Road|Asheville|NC|28803|828-271-4779|828-271-4117
      HERBERT HOOVER|NHS|Cheryl Schreier| P.O. Box
      607; 110 Parkside Drive|West Branch|IA|52358-0607|319-643-2541|319-643-5367

      After Cut Block, Left Align & Paste Block:
      BANDELIER|NM|Jason Lott| 15 Entrance Road|Los
      Alamos|NM|87544-9508|505-672-3861|505-672-9714
      BIG BEND|NP|Bill Wellman| P.O. Box 129|Big
      Bend|TX|79834-0129|432-477-2251|432-477-1172
      BIG HOLE|NB|Stephen Black| P.O. Box 237; 16425 Highway 43
      West|Wisdom|MT|59761-0137|406-689-3155|406-689-3151
      BIG SOUTH FORK|NR&RA|Stennis Young| 4564 Leatherwood
      Road|Oneida|TN|37841|423-569-9778|423-569-5505
      BLUE RIDGE PARKWAY|PW|Phil Francis| 199 Hemphill Knob
      Road|Asheville|NC|28803|828-271-4779|828-271-4117
      HERBERT HOOVER|NHS|Cheryl Schreier| P.O. Box 607; 110 Parkside Drive|West
      Branch|IA|52358-0607|319-643-2541|319-643-5367


      Repeating for spaces insertion before city field (after 4 "|"s)
      using: "\R(.*?\|){4}\K(.*?$)" >> " $2"
      BANDELIER|NM|Jason Lott| 15 Entrance
      Road| Los
      Alamos|NM|87544-9508|505-672-3861|505-672-9714
      BIG BEND|NP|Bill Wellman| P.O. Box
      129| Big
      Bend|TX|79834-0129|432-477-2251|432-477-1172
      BIG HOLE|NB|Stephen Black| P.O. Box 237; 16425 Highway 43
      West|
      Wisdom|MT|59761-0137|406-689-3155|406-689-3151
      BIG SOUTH FORK|NR&RA|Stennis Young| 4564 Leatherwood
      Road| Oneida|TN|37841|423-569-9778|423-569-5505
      BLUE RIDGE PARKWAY|PW|Phil Francis| 199 Hemphill Knob
      Road|
      Asheville|NC|28803|828-271-4779|828-271-4117
      HERBERT HOOVER|NHS|Cheryl Schreier| P.O. Box 607; 110 Parkside
      Drive| West
      Branch|IA|52358-0607|319-643-2541|319-643-5367

      After Cut Block, Left Align & Paste Block:
      BANDELIER|NM|Jason Lott| 15 Entrance
      Road| Los Alamos|NM|87544-9508|505-672-3861|505-672-9714
      BIG BEND|NP|Bill Wellman| P.O. Box
      129| Big Bend|TX|79834-0129|432-477-2251|432-477-1172
      BIG HOLE|NB|Stephen Black| P.O. Box 237; 16425 Highway 43
      West| Wisdom|MT|59761-0137|406-689-3155|406-689-3151
      BIG SOUTH FORK|NR&RA|Stennis Young| 4564 Leatherwood
      Road| Oneida|TN|37841|423-569-9778|423-569-5505
      BLUE RIDGE PARKWAY|PW|Phil Francis| 199 Hemphill Knob
      Road| Asheville|NC|28803|828-271-4779|828-271-4117
      HERBERT HOOVER|NHS|Cheryl Schreier| P.O. Box 607; 110 Parkside
      Drive| West Branch|IA|52358-0607|319-643-2541|319-643-5367
    • Show all 25 messages in this topic