update a column in input file by taking value from Database in perl

Question

input file:

1,a,USA,,
2,b,UK,,
3,c,USA,,

i want to update the 4th column in the input file from taking values from one of the table.

my code looks like this:

my $number_dbh = DBI->connect("DBI:Oracle:$INST", $USER, $PASS ) or die "Couldn't
connect to datbase $INST";
my $num_smh;
print "connected \n ";
open FILE , "+>>$input_file" or die "can't open the input file";
print "echo \n";
while(my $line=<FILE>)
{
   my @line_a=split(/\,/,$line);
   $num_smh = $number_dbh->prepare("SELECT phone_no from book where number = $line_a[0]");
   $num_smh->execute() or die "Couldn't execute stmt, error : $DBI::errstr";
   my $number = $num_smh->fetchrow_array();
   $line_a[3]=$number;
}

Looks like you should be having newlines after every fourth character, no? Otherwise the code you have wont get you what you want — Gurunandan Bhat
– Gurunandan Bhat, Commented Jan 7, 2011 at 19:02

daxim · Accepted Answer · 2011-01-08 16:14:41Z

1

Looks like your data is in CSV format. You may want to use Parse::CSV.

edited Jan 8, 2011 at 16:14

daxim

39.3k4 gold badges72 silver badges135 bronze badges

answered Jan 7, 2011 at 16:17

Nylon Smile

9,5661 gold badge27 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rahul Singh Over a year ago

i think the problem is with file handling. can i do read and update on the same file.

Nylon Smile Over a year ago

There are two ways if you want to do it this way: easy way is to read and consume the whole file and then truncate it and put the new content. not too easy way is to have a byte count and know where to go to (re)place new content. I still think you are better off with something like Parse::CSV. It's simple and straight forward.

DVK Over a year ago

Concure. Just use Text::CSV (my preferred CSV parser), parse the input file with it and print a new output file with it as you parse; then replace new output into old input file after input file is closed.

CanSpice · Accepted Answer · 2011-01-07 22:44:08Z

+>> doesn't do what you think it does. In fact, in testing it doesn't seem to do anything at all. Further, +< does something very strange:

 % cat file.txt
1,a,USA,,
2,b,UK,,
3,c,USA,,
 % cat update.pl
#!perl

use strict;
use warnings;

open my $fh, '+<', 'file.txt' or die "$!";
while ( my $line = <$fh> ) {
    $line .= "hello\n";
    print $fh $line;
}
 % perl update.pl
 % cat file.txt
1,a,USA,,
1,a,USA,,
hello
,,
,,
hello
 %

+> appears to truncate the file.

Really, what you want to do is to write to a new file, then copy that file over the old one. Opening a file for simultaneous read/write looks like you'd be entering a world of hurt.

As an aside, you should use the three-argument form of open() (safer for "weird" filenames) and use lexical filehandles (they're not global, and when they go out of scope your file automatically closes for you).

Collectives™ on Stack Overflow

update a column in input file by taking value from Database in perl

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related