asked on

Merge two files and create new file based on last and first name

Basically, I have two files(MasterFile, PhoneFile) that I need to match on last name and/or first name if the lastname is the same, thus creating a NewFile.

There is also a chance that there might be a record that's in the PhoneFile , but not in the MasterFile that record would have to be also added to the NewFile.

In the example below there are 6 records that are not in the MasterFile, but they exist in the PhoneFile.

MasterFile:

4808 G N01NE 6510 ACKERMAN TOM 2505 11
540814 E01E 1561 ACKMAN JIM 2544 61
2142 Y S141 1432* ACOMPANADO GREG 0505
0201 Y E04D 3308* ACOMPANADO JENNIFER 1205
466584 V01SE 3651 ACOSTA BOB 2541 F
2483 Y N11NE 3303* ADAMS KARL 1304
238851 N05SW 1422 ADAVAL ANILESH 2511 68
212124 N13SW 6104 ADAVI VIJAYA 2545 VV
0111 B 1W350 F4281 ADDIE TOM 2508 UU
360220 NPLNW 8013 ADDISON LEO 2502 68
4234 1 OFFS * ADDLUR RAJU TERM0005 2511 68
313826 1W200 F8010 ADE RYAN 2526 46
063451 OFSN5 * ADHIKARY UMASANKAR 2511 68
4081EE N06NE 1400* ADITYA NAVEEN TERM0506 2511 68
013134 NPLNW 2004 AFFELDT MARY-ANN 2502 DI
210633 N00WC 3350 AFONG BILL 2540 63
0585 5 E15E 4684* AGACKI SHIRLEY 0031
110341 V02NC 5806 AGARWAL AMIT 2540 83
0333 L 1E200 F1601 AHANGAR BOB 2510 WW
080020 N08SW 1031* AHMED FATEH TERM1205 2540 63
431343 OFSN5 * AHUJA AKSHAYE 2511 68
384132 V01NC 4405 AICHELE AARON 2541 F
483018 N01WC 1413 AILANI SRINIVAS 2542 65
540024 OFSN5 * AJWANI VIPUL 2511 68
3118ZZ 1E230 F1482 AKHTER BILL 2508 YY
4511BB 1W360 F8843 AKULA RAVI 2508 04
441025 N00SE 1003 AKULICZ KATHERINE 2500
008510 V01SE 1003 ALAGAPPAN KEN 2541 F
460220 OFSN5 * ALAHARI VINOD-KUMAR 2502 68
218143 N05NW 4433 ALBEE ROSE 2548 82
2048DD N01NW 5614 ALBINGER STEVE 2505 80
061116 N08WC 2003 ALBRECHT BETTY 2518 80

PhoneFile:

4808 ACKERMAN TOM 796-8646 (205)
4665 ACOSTA BOB 807-0773 (205)
2121 ADAVI VIJAYA 529-2746 (205)
0711 C ADDIE TOM 473-4392 (312) C/379-4454 P/225-7514
2796 AFONG BILL 762-0621 (205)
1791 AGARWAL AMIT 324-3246 (205)
3049 AGARWAL ASHISH 765-0475 (205)
5094 AGG MICHAEL 839-3852 (205)
2499 AGRAWAL SANDEEP 289-9451 (205) CELL/737-3216
0333 AHANGAR BOB 321-3530 (205)
4839 AILANI SRINIVAS 326-4232 (248) C/205-412-7511 P/205-201-0073
3718 AKHTER BILL 435-0525 (205)
3281 f AKHOURI ANVAYA 300-0512 (205)
2020 AKKALA MADHU 281-0088 (205) CELL/801-6646
4511 AKULA RAVI 415-5014 (205) PGR/205-222-3680
0085 ALAGAPPAN KEN 162-5120 (205)
2181 ALBEE ROSE 662-3006 (312) PGR/312-301-1102
0141 * ALBERTS TOM 064-4151 (205)
2048 ALBINGER STEVE 141-0800 (205)
0611 ALBRECHT BETTY 244-6003 (312)

NewFile:

4808 G N01NE 6510 ACKERMAN TOM 2505 11 4808 ACKERMAN TOM 796-8646 (205)
540814 E01E 1561 ACKMAN JIM 2544 61
2142 Y S141 1432* ACOMPANADO GREG 0505
0201 Y E04D 3308* ACOMPANADO JENNIFER 1205
466584 V01SE 3651 ACOSTA BOB 2541 F 4665 ACOSTA BOB 807-0773 (205)
2483 Y N11NE 3303* ADAMS KARL 1304
238851 N05SW 1422 ADAVAL ANILESH 2511 68
212124 N13SW 6104 ADAVI VIJAYA 2545 VV 2121 ADAVI VIJAYA 529-2746 (205)
0111 B 1W350 F4281 ADDIE TOM 2508 UU 0711 C ADDIE TOM 473-4392 (312) C/379-4454 P/225-7514
360220 NPLNW 8013 ADDISON LEO 2502 68
4234 1 OFFS * ADDLUR RAJU TERM0005 2511 68
313826 1W200 F8010 ADE RYAN 2526 46
063451 OFSN5 * ADHIKARY UMASANKAR 2511 68
4081EE N06NE 1400* ADITYA NAVEEN TERM0506 2511 68
013134 NPLNW 2004 AFFELDT MARY-ANN 2502 DI
210633 N00WC 3350 AFONG BILL 2540 63 2796 AFONG BILL 762-0621 (205)
0585 5 E15E 4684* AGACKI SHIRLEY 0031
110341 V02NC 5806 AGARWAL AMIT 2540 83 1791 AGARWAL AMIT 324-3246 (205)
3049 AGARWAL ASHISH 765-0475 (205)
5094 AGG MICHAEL 839-3852 (205)
2499 AGRAWAL SANDEEP 289-9451 (205) CELL/737-3216
0333 L 1E200 F1601 AHANGAR BOB 2510 WW 0333 AHANGAR BOB 321-3530 (205)
080020 N08SW 1031* AHMED FATEH TERM1205 2540 63
431343 OFSN5 * AHUJA AKSHAYE 2511 68
384132 V01NC 4405 AICHELE AARON 2541 F
483018 N01WC 1413 AILANI SRINIVAS 2542 65 4839 AILANI SRINIVAS 326-4232 (248) C/205-412-7511 P/205-201-0073
540024 OFSN5 * AJWANI VIPUL 2511 68
3118ZZ 1E230 F1482 AKHTER BILL 2508 YY 3718 AKHTER BILL 435-0525 (205)
3281 f AKHOURI ANVAYA 300-0512 (205)
2020 AKKALA MADHU 281-0088 (205) CELL/801-6646
4511BB 1W360 F8843 AKULA RAVI 2508 04 4511 AKULA RAVI 415-5014 (205) PGR/205-222-3680
441025 N00SE 1003 AKULICZ KATHERINE 2500
008510 V01SE 1003 ALAGAPPAN KEN 2541 F 0085 ALAGAPPAN KEN 162-5120 (205)
460220 OFSN5 * ALAHARI VINOD-KUMAR 2502 68
218143 N05NW 4433 ALBEE ROSE 2548 82 2181 ALBEE ROSE 662-3006 (312) PGR/312-301-1102
2048DD N01NW 5614 ALBINGER STEVE 2505 80 2048 ALBINGER STEVE 141-0800 (205)
0141 * ALBERTS TOM 064-4151 (205)
061116 N08WC 2003 ALBRECHT BETTY 2518 80 0611 ALBRECHT BETTY 244-6003 (312)

Thanks

Jeff Certain

Is this a one-time thing, or a routine that is going to have to be executed repeatedly?

Richard Kreidl

ASKER

It probably will be done once a week....

Jeff Certain

Okay... let's spec the problem a little better then. Assuming that each file is a .txt file? Also, what delimiter do you want in the output? Or are these fixed-width? If fixed-width, please define the schemas for us.

Richard Kreidl

ASKER

Both files are txt files.

We could use a Pipe(|) symbol in the output.

Both files are fixed width using spaces as the delimiter, I'm not sure what you mean by schemas??

Jeff Certain

What is stored in each column, and how wide are they?

Richard Kreidl

ASKER

Snapshot of MasterFile. The lastname starts in column 23 and is separated by the firstname with 2 spaces in between them.

4808 G N07NE 6519 ACKERMAN DAN 2505 71
540874 E07E 1561 ACKMAN JASON 2544 67

Snapshot of the PhoneFile. The lastname starts in column 8 and is separated by the firstname with 1 space in between them.
4665 ACKERMAN DAN 807-0773 (312)
2121 ACKMAN JASON 529-2746 (312)

I hope this helps...

ASKER CERTIFIED SOLUTION

Richard Kreidl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial