Results 1 to 3 of 3

Help with parsing & formatting a paragraph of text please

Threaded View

  1. #1
    Registered User
    Join Date
    04-28-2010
    Location
    Wisconsin
    MS-Off Ver
    Excel 2007
    Posts
    2

    Help with parsing & formatting a paragraph of text please

    Hello,

    I am trying to format and parse individual customer records from a large chunk of text. This is for my business and I have been searching for a way to do this so that I can save my 25 employees a great deal of time. Below I have listed the raw text EXACTLY as I receive it. This example contains 32 and 31 individual records respectively from 2 separate clients. The number of records for each client can differ but usually is between 30 and 35 records each. It looks to me like each record is separated by a "\r\n" minus the quotes. Normally, this chunk of raw data would contain information records for about 15-20 individual clients. Below is raw data for 2 clients:

    hashObj["10001222167111010001:183998:1"] = "Addr: 1212 CEDAR AVE\r\nFirst #: 555-555-1212\r\nJob ID: 183998\r\nHome #: 555-555-1213\r\nWork #: \r\nJob Cmt: PC#(555) 555-1212/AUTH USER: JOHN DOE/INST 2PRT MTA/INST 2PRT LINES/ LEC:PHONE COMPANY\r\nTech: 0999\r\nResCd: [500][203]\r\nResDesc: [TELEPHONY DEVICE][CDU NEW INSTALL]\r\nCpTime: 04/28/2010 09:30\r\nDS: COMPLETE\r\nTS: 8-10A\r\nType: VB\r\nType Desc: CB PORT INSTALL\r\nAddr2: BUSINESS ACCOUNT\r\nCity: SPRINGFIELD, IL 65321-1503\r\nName: JOHNS BUSINESS,*\r\nMap **: \r\nHead End: 4C\r\nNode: 4OO25\r\nDelq: $0.00\r\nCOD: \r\nDrop Tag1: \r\nDrop Tag2: \r\nDrop Tag3: \r\nDays Delq: 0\r\nDelq Status: \r\nReaCd: [SB]\r\nReaDesc: [DIRECT SALES]\r\nSpecial: N\r\nTC Ind: \r\nCur Bal: $0.00"
    jobObj["10001222167111010001:183998:1"] = "183998 [CP : 8-10A : VB]"
    hashObj["10001248253811020001:349713:1"] = "Addr: 635 19TH PL S\r\nFirst #: 555-555-6161\r\nJob ID: 349713\r\nHome #: 555-555-6162\r\nWork #: \r\nJob Cmt: (MOOS)NO DIAL TONE, INTERNET OUT\r\nTech: 0999\r\nResCd: [OW1][FE1][605]\r\nResDesc: [AERIAL DROP RPR][TC FEE WAIVED][CONTRACTFLDTECH]\r\nCpTime: 04/28/2010 11:55\r\nDS: COMPLETE\r\nTS: 10-12\r\nType: VT\r\nType Desc: TEL TROUBLE CALL\r\nCity: SPRINGFIELD, IL 63421-6301\r\nName: DOE,JANE\r\nMap **: \r\nHead End: 4C\r\nNode: 4OL26\r\nDelq: $0.00\r\nCOD: \r\nDrop Tag1: V401708\r\nDrop Tag2: \r\nDrop Tag3: \r\nDays Delq: 0\r\nDelq Status: \r\nReaCd: [TA]\r\nReaDesc: [NO DIAL TONE]\r\nSpecial: Y\r\nTC Ind: \r\nCur Bal: $157.48"
    jobObj["10001248253811020001:349713:1"] = "349713 [CP : 10-12 : VT]"



    and this is how I would like that raw data to be displayed in Excel:

    Addr: 1212 CEDAR AVE
    First #: 555-555-1212
    Job ID: 183998
    Home #: 555-555-1213
    Work #:
    Job Cmt: PC#(555) 555-1212/AUTH USER: JOHN DOE/INST 2PRT MTA/INST 2PRT LINES/ LEC:PHONE COMPANY
    Tech: 0999
    ResCd: [500][203]
    ResDesc: [TELEPHONY DEVICE][CDU NEW INSTALL]
    CpTime: 04/28/2010 09:30
    DS: COMPLETE
    TS: 8-10A
    Type: VB
    Type Desc: CB PORT INSTALL
    Addr2: BUSINESS ACCOUNT
    City: SPRINGFIELD, IL 65321-1503
    Name: JOHNS BUSINESS,*
    Map **:
    Head End: 4C
    Node: 4OO25
    Delq: $0.00
    COD:
    Drop Tag1:
    Drop Tag2:
    Drop Tag3:
    Days Delq: 0
    Delq Status:
    ReaCd: [SB]
    ReaDesc: [DIRECT SALES]
    Special: N
    TC Ind:
    Cur Bal: $0.00


    Addr: 635 19TH PL S
    First #: 555-555-6162
    Job ID: 349713
    Home #: 555-555-6163
    Work #:
    Job Cmt: (MOOS)NO DIAL TONE, INTERNET OUT
    Tech: 0999
    ResCd: [OW1][FE1][605]
    ResDesc: [AERIAL DROP RPR][TC FEE WAIVED][CONTRACTFLDTECH]
    CpTime: 04/28/2010 11:55
    DS: COMPLETE
    TS: 10-12
    Type: VT
    Type Desc: TEL TROUBLE CALL
    City: SPRINGFIELD, IL 65321-1503
    Name: DOE,JANE
    Map **:
    Head End: 4C
    Node: 4OL26
    Delq: $0.00
    COD:
    Drop Tag1: V401708
    Drop Tag2:
    Drop Tag3:
    Days Delq: 0
    Delq Status:
    ReaCd: [TA]
    ReaDesc: [NO DIAL TONE]
    Special: Y
    TC Ind:
    Cur Bal: $157.48


    Many of the record fields are blank as you can see above but they are still there. It seems like it shouldn't be too hard to extract certain pieces of information from that raw data but I just cannot seem to get it right when I try to code for it. Many thanks in advance for any help as this would save my employees at least an hour a day!!!
    Last edited by Remiel; 04-29-2010 at 12:21 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1