Populating data from one file to another file

rycher has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks! I have two files. File #1 contains supervisor ID's and employee data while File #2 contains supervisor ID's and supervisor usernames. I would like to open both files, replace the supervisor ID's in File #1 with the supervisor usernames in File #2. How would I accomplish this?

Example:

File #1 --

"SimpsonH","Homer","Simpson","NULL","648","0","218 555","Nuclear Contr
+ol","Nuclear Operator","111"

File #2 --
"111","BurnsM"

File #1 after update --
"SimpsonH","Homer","Simpson","NULL","648","0","218 555","Nuclear Contr
+ol","Nuclear Operator","BurnsM"
[download]

Comment on Populating data from one file to another file Download Code

Replies are listed 'Best First'.
Re: Populating data from one file to another file by graff (Chancellor) on May 01, 2009 at 02:21 UTC
Read file 2 first: load it into a hash with the numeric ID's as the hash keys and the strings as the hash values. Then, as you read through file 1, use a regex to match and capture the digits at the end of each line. If there's a hash element with that digit string as the hash key, replace the digits at the end of the line with the corresponding hash value, and print the result to a new file (or to STDOUT, and redirect that to a file). Have you written any perl code for this yet? If not, now's the time to try. (updated to fix second paragraph)	[reply]
Re: Populating data from one file to another file by bobf (Monsignor) on May 01, 2009 at 02:45 UTC
Rather than manually parsing and merging the files, I would suggest looking at DBD::CSV or, at a minimum, any of the CSV parsing modules on CPAN. As an example, you could use Text::CSV to read file 1. For each line in file 1, extract the supervisor's ID, then use DBD::CSV and SQL to locate the supervisor's name in file 2. Write the output to a new file. Hope this gets you started...	[reply]
Re^2: Populating data from one file to another file by rycher (Acolyte) on May 01, 2009 at 05:05 UTC
Huzzah! Thank you for the advice... I found a way to make MySQL output the info with an INNER JOIN and an alias. It's quicker and more reliable than my coding anyway. Thanks!	[reply]
Re: Populating data from one file to another file by Bloodnok (Vicar) on May 01, 2009 at 13:09 UTC
Assuming ... File #1 always contains the same number of field per record You're using *NIX You only need to create an output file from the conjoined files Then `join`(1) and `sort`(1) are your friends e.g. `sort file1 > file1.sorted sort file2 \| join -1 10 -2 1 file1.sorted - \| tee file3` [download] e.g. : `$ echo "SimpsonH","Homer","Simpson","NULL","648","0","218 555","Nuclea +r Control","Nuclear Operator","111" > file1 $ echo "111","BurnsM" > file2 $ join -t, -1 10 -2 1 file1 file2 111,SimpsonH,Homer,Simpson,NULL,648,0,218 555,Nuclear Control,Nuclear +Operator,BurnsM` [download] as required A user level that continues to overstate my experience :-))	[reply] [d/l] [select]