Hello Few Perl monks, I have very urgent requirement to perform below changes.

I have one master file a csv file delimited by commas and a directory which has several files 8 to be precise.

Master file has fields such as employee ID and name, surname of employee and other fields as well.

Similarly other csv files in directory have employee ID's and name and surnames in it. what I need to do is using master file as source search employee ID's in all files in that directory and replace name and surname in the directory.

for ex.

Master file
employee ID, name, surname, other fields
1,nitin, abc,...
2, aa, bb,..
3,cc,dd,..
4, ee, ff
[download]

directory files

employee ID, name, surname, other fields
1,nitin1, abc1,...
2, aa1, bb1,..
3,cc1,dd1,..
4, ee11, ff1
[download]

I need to replace this with the data in master file for all files in directory. positions of the fields in target directory are same as I have described. I am fairly new to perl and just making my way through it. any help is greatly appreciated. sample master file

Employee Number~Category Number~Forename~Surname~Initials~NI Number~Ti
+tle~Gender~Marital Status~Date of Birth~Home Address Line 1~Home Addr
+ess Line 2~Home Address Line 3~Home Town/City~Home County~Home Post C
+ode~Employee Contribution %~Employer Contribution %~Annual Salary~Dat
+e of Joining Scheme~Date of Joining Company~Exchanged %~Scheme Joiner
D51231~ORDM~Charles~Smith~C T~NP307342C~Mr~Male~S~15/10/1971~45 Board 
+Walk~Springer Lane~Mundesley~Norfolk~~NR28 3JK~0.00~8.00~72800.00~01/
+09/2014~04/08/2014~2.00~N
F78321~SMG1~Janet~Patterson~J M~AA112233A~Ms~Female~~21/07/1975~12 She
+ringham Way~Cromer~~~~NR21 9BG~0.00~31.00~320000.00~01/09/2014~25/08/
+2014~0.00~N
[download]

Sample files which is to be replaced by master file

Employee Number~Category Number~Forename~Surname~Initials~NI Number~Ti
+tle~Gender~Marital Status~Date of Birth~Home Address Line 1~Home Addr
+ess Line 2~Home Address Line 3~Home Town/City~Home County~Home Post C
+ode~Employee Contribution %~Employer Contribution %~Annual Salary~Dat
+e of Joining Scheme~Date of Joining Company~Exchanged %~Scheme Joiner
D51231~ORDM~Nitin~Rane~C T~NP307342C~Mr~Male~S~15/10/1971~45 Board Wal
+k~Springer Lane~Mundesley~Norfolk~~NR28 3JK~0.00~8.00~72800.00~01/09/
+2014~04/08/2014~2.00~N
F78321~SMG1~AAA~BBB~J M~AA112233A~Ms~Female~~21/07/1975~12 Sheringham 
+Way~Cromer~~~~NR21 9BG~0.00~31.00~320000.00~01/09/2014~25/08/2014~0.0
+0~N
[download]

Here D51231 F78321 are employee nos and in master file D51231 Forename = CHARLES surname = SMITH I need to do this to sample file where D51231 Forename = Nitin surname = Rane Output should be D51231 Forename = CHARLES surname = SMITH. what I was hoping with below code is when I give directory name all files in that directory are accessible to me as tables so that I can simply use simple SQL to update source file.

my $dbh = DBI->connect('dbi:CSV:', "", "",
             {
                 f_dir => "$_ENV::LogsFilesDIR/",
                 f_ext => ".txt",
                 eol => "\n",
                 sep_char  => "~",
                 RaiseError => 1,
             }) or die $DBI::errstr;

print $ceridian_raw_files;
if ($ceridian_raw_files =~  m/^$_ENV::new_entrants/i)
{
print "inside dbi $ceridian_raw_files\n";
my $sth = $dbh->prepare("select forename  from  $ceridian_raw_files");

$sth->execute();
my $res = $sth->fetchall_arrayref();

use Data::Dumper;
print  $res;
[download]

However in my case Im not able to even initiate the object $sth. any suggestions? I tried this method with some comments from below members. apologies for any ignorance on my part as name suggests Im newbie in Perl world and relying on help from fellow perl monks :) Hello Monks Issue is now resolved. thanks for your help if anyone is intrested then below is the code

 my $dbh = DBI->connect("dbi:CSV:f_dir=$_ENV::LogsFilesDIR;csv_sep_cha
+r=\\~") or die $DBI::errstr;
$dbh->{'csv_tables'}->{'prospects'} = { 'file' => 'cimp_new_entrant.tx
+t'};
 $dbh->do("UPDATE prospects SET Forename = 'Nitin'");
my $sth = $dbh->prepare("select Forename from prospects");
$sth->execute();
while (@row = $sth->fetchrow_array) {
  print @row;
   }
   $sth->finish( );
   $dbh->disconnect( );
[download]