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!!!
Bookmarks