I have a working Excel macro script that currently processes data from one raw data text file, outputting the data to Excel via one process, and then via a second process outputting it to a .csv file (each of the two processes is executed via separate buttons from within the same Excel file). I have a second raw data text file, and I want to incorporate data from that file into the same output file that the existing script generates. So, I want to modify the script to process both raw data text files, and create one output file in Excel, which is again able to be exported to a .csv file via the second process. I need you to take my existing script and add the necessary code to do this.
Each record in the first raw data text file has a unique identifier associated with it. Think of this unique identifier as a username. This username is also present in each record in the second raw data file, however in the second raw data file there are some instances where there are multiple data records based on one username. So, the identifier/username is unique in the first raw data file, but not necessarily so in the second data file.
Here are 3 sample rows from the current output file based on the first raw data file, “|” is the field delimiter (this is saved as a .csv file):
00CCWD|Jones|Herbert|Jones Appraisal Group Inc|123 Pickfair Terr|Lake Mary|FL|32746|(800) 333-9393|(800) 333-7928||Seminole County|Florida|9
00D3J9|Carryway|Richard|Carryway and Company|2937 S. Stewie Ave, Suite C|Springfield|MO|65804|(800) 883-1777|(000) 000-0000||Greene County|Missouri|9
00DC5A|Wenze|Robin|Tucson Appraisal Masters, LLC|P.O. Box 359|Tucson|AZ|85751|(800) 433-0738|(800) 433-0737||Pima County|Arizona|9
The first field in the above is the unique identifier/username.
Now, here’s 4 sample rows from the second raw data file, matching the same usernames from above:
00CCWD FL FLRD1820 20101130 Y 3 *
00D3J9 MO MORA002723 20100630 Y 3 *
00DC5A AZ AZ21933 20100831 Y 3 *
00DC5A WA WA1702785 20100225 Y 3 *
The first two records above are for usernames that only have one record in the second raw data file. The third and fourth records above are for the same username.In the second raw data file, there may be several records based on the same username, and the script needs to be able to process all of them and incorporate the data into the final output file. The asterisk above simply identifies the end of a record in the raw data text file, and doesn’t need to be included in the output file.
Your job is to add the necessary code to my existing script in order to append the data from each record in the second raw data file to the record with the matching username in the current output file. For the usernames that have more than one record in the second raw data file, your script will need to append the multiple records from the second raw data file to the single record associated with that username in the output file. After doing this, you will also need to make sure that the .csv file generates correctly via the second process.
So, here is an example of how I would want the above records in the final output file to read:
00CCWD|Jones|Herbert|Jones Appraisal Group Inc|123 Pickfair Terr|Lake Mary|FL|32746|(800) 333-9393|(800) 333-7928||Seminole County|Florida|9|FL|FLRD1820|11/30/2010|Y|3|||||
00D3J9|Carryway|Richard|Carryway and Company|2937 S. Stewie Ave, Suite C|Springfield|MO|65804|(800) 883-1777|(000) 000-0000||Greene County|Missouri|9|MO|MORA002723|06/30/2010|Y|3|||||
00DC5A|Wenze|Robin|Tucson Appraisal Masters, LLC|P.O. Box 359|Tucson|AZ|85751|(800) 433-0738|(800) 433-0737||Pima County|Arizona|9|AZ|AZ21933|08/31/2010|Y|3|WA|WA1702785|02/25/2010|Y|3
The above is just my example, it is possible that there may be more than two records based on one username in the second raw data file. I don’t know the maximum number of records based on a single username, I’m guessing it’s probably around five, but theoretically could be as many as fifty. I can give you the entire second raw data file if you would like to check (although perhaps the script could just do an iterative process for as many records as there are based on a given username, with the ability to max out at fifty).
That’s it. I hope this is all clear, please ask if you have any questions. Thanks.