help with a data file

Discussion in 'OT Technology' started by TigreTek, Aug 30, 2005.

  1. TigreTek

    TigreTek omega member OT Supporter

    Joined:
    Oct 8, 2001
    Messages:
    26,927
    Likes Received:
    3
    quick rundown

    i've got a data file (easily readable) that's somewhere in the neighborhood of 5000 records (each line is a new record)

    i need to correct last names with spaces in them to have hyphens instead. data is always formatted the same (meaning that each line is always eight chars for a record name, the next 30 chars are last name, first and any remaining chars are spaces, and a bunch of other crap)

    data looks something like this:

    XXXXXXXXsmith, joe (bunch of spaces equal to 30-#ofchars in "smith, joe") (the rest of the record)
    XXXXXXXXde la torres, joe (bunch of spaces equal to 30-#ofchars in "de la torres, joe") (the rest of the record)
    XXXXXXXXdirka-dirka, mohammad (bunch of spaces equal to 30-#ofchars in "dirka-dirka, mohammad") (the rest of the record)

    any ideas?
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Find two spaces next to each other, that's the start of the padding. Take the substring and subsitute spaces for hyphens.

    Or for whatever language you're using, use the trim function. You could probably do this in Excel easily enough
     
  3. TigreTek

    TigreTek omega member OT Supporter

    Joined:
    Oct 8, 2001
    Messages:
    26,927
    Likes Received:
    3
    this is exactly what one line looks like (data changed because i'd lose my job ;) )

    Code:
    0BENIPA00Jones, Pablo              123 NW 123 Terrace          Pembroke Pines     FL33028     9549999999          123456789 BENIPA001BENIPA001         92          Y     123456Y           1234           01         A1234567        BI    1 125       0         AAA123456789           00                  100                          A01   YNNNNNNN       987654321012A     N     N     N     N     N     N     N                    0            ZXZ12Z   Y                                                                                                                                           N N     N     N           N                                                                                                                                                       12.99                                                                                       N              NUSA           123.4                                                        9549999999    
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I'd use Excel then. When you import it, you can separate out the fields using fixed width. The name is obviously going to need further parsing using formulas.
     

Share This Page