File match/merge and notes field extraction.
Need to read 2 files and match their contents and then create a new "matched" file. Also, need to extract Notes from a field and create another file with a reference back to the "matched" records via some id or index #.
## Deliverables
1) Take "NAR Magazine List 2012-new" file and search thru the "GM Final Export" (GM) to find matches by Company Name which is first column in both files. When matched, write the fields from the "NAR Magazine List 2012-new" file (NAR) to a "matched" file with additional fields from the GM file. I would like all columns from both files in the new "matched" file prefixed with "NAR"-column name and "GM"-column name.
2) Upon a match, need to parse out the Notes field into 4 fields columns (i.e. Who / Date / Time /Notes
). Then, create a new file of "matched record notes" making sure to create an index so that I can match the records in the "matched" file with the right Notes records in the "matched record notes" file.
So in the example below:
<<<<<<<<<<<< begin example >>>>>>>>>>>>>>>>
*** BODIERNO *** July 21, 2010 at 2:53pm Ben sayss no one is interested and he's really not either going to pass.
<<<<<<<<<<<< end example >>>>>>>>>>>>>>>>
Who=*** BODIERNO ***
Date=July 21, 2010
Time=2:53pm
Notes=Ben sayss no one is interested and he's really not either going to pass.
3) Please NOTE some of the Note information may be formatted as below - a little different but containing the info needed for each of the 4 fields.
*** Barb Odierno 6/8/2006 9:21:42 AM *** Had to cancel as Ang out for rest of wk w/Strep [login to view URL]: Scheduled => Canceled
4) Please also NOTE that there will be many note records in the NOTES field and they have to be parsed out as seen below via "***"
*** CHALLIDA *** December 13, 2007 at 3:58pm AJ- rude*** KELLYF *** October 19, 2006 at 12:51pm 2005
So in effect the string above would wind up looking like 2 records with the columns as mentioned above, i.e. Who / Date / Time /Notes
*** CHALLIDA *** December 13, 2007 at 3:58pm AJ- rude
*** KELLYF *** October 19, 2006 at 12:51pm 2005
5) Very important to make sure that the "matched" file has a reference to the "matched record notes" (index or id#) file so I can reference the records properly
6) Additionally, I need to parse out the CONTACT column in the GM file to 2 separate columns, i.e. Last Name and First Name don't need middle names or suffixes like Jr or Sr or II, etc.