PERL Beginners - manipulating csv file fields through perl

Author

manipulating csv file fields through perl

Mihir Kamdar

2007-08-24, 4:01 am

Hi,

I have a csv file. I wanted to do some calculations on some of its fields,
like multiplying the 7th field with the 13th field and overwriting the 13th
field with the answer of my calculation.

Regarding this, can I do the calculations on the input file and overwrite it
after calculating, or I will have to open the output file and write into it?
I am asking this because I will have several files in the directory. So, I
will have to read the directory using readdir, and process each file. It is
better if I open the file in read-write mode, process it and overwrite the
file. Just wanted to know if it is safe?

Please guide on how to get started with this?

Also can I do something like below:-

while ($line=readline($IN_FILE))
{
my @cdr=split (/,/, $line) ;
$cdr[13] = $cdr[6]*5 ; ###Can I do something like this
}

Thanks,
Mihir

Jeff Pang

2007-08-24, 4:01 am

2007/8/24, Mihir Kamdar <kamdarmihir06@gmail.com>:

> $cdr[13] = $cdr[6]*5 ; ###Can I do something like this

sure,why can't?

Mihir Kamdar

2007-08-24, 4:01 am

On 8/24/07, Jeff Pang <rwwebs@gmail.com> wrote:
>
> 2007/8/24, Mihir Kamdar <kamdarmihir06@gmail.com>:
>
>
> sure,why can't?

Hi,

Please look at my code below and comment. I am trying to manipulate 13th
field of my record. But I am not getting the desired result in the output.
The output file is the same as the input file.

#!/usr/bin/perl

use warnings ;

my $file_path =
'/home/user71/RangerDatasource/Customization/TelekomMalaysia/Scripts/Tests/cprogs/files/ratetest';
my $write_path =
'/home/user71/RangerDatasource/Customization/TelekomMalaysia/Scripts/Tests/cprogs/files/rateop'
;
my %times ;
my $continue = 1;

$SIG{INT} = $SIG{TERM} = sub { $continue = 0 };

while ($continue) {
opendir my $dh, $file_path or die $!;
while (my $file = readdir $dh) {
my $fname = "$file_path/$file" ;
next unless -f $fname;
unless (exists $times{$file}){
my $line;
open (my $IN_FILE,"<","$file_path/$file") or die
$!." file not found" ;

while ($line=readline($IN_FILE))
{
my @cdr=split (/,/, $line) ;
$cdr[13] = $cdr[6]*5 ;
$hash{"@cdr[2,3,6,7]"}=$line;
}
close $IN_FILE ;

open (my $OUT_FILE,">","$write_path/$file.out") or
die $!;
while (my($key, $value) = each %hash)
{
print $OUT_FILE $value;
}
close $OUT_FILE;
}
}
closedir $dh ;
}

Thanks,
Mihir

Rob Coops

2007-08-24, 4:01 am

Your idea should work pretty well assuming that you are 100% sure that the
thing in field 7 really is a number, you might get strange results if field
7 is empty or somehting else then a number.

As for how to handle the files that is really up to you and the environment
you work in... if your files are masive things of several hundreds of Mb
each you might not want to place them completely in memory, if they are
changed by other processes you might want to use some form of a locking
scheme while handeling them. Etc, etc...

On 8/24/07, Mihir Kamdar <kamdarmihir06@gmail.com> wrote:
>
> Hi,
>
> I have a csv file. I wanted to do some calculations on some of its fields,
> like multiplying the 7th field with the 13th field and overwriting the
> 13th
> field with the answer of my calculation.
>
> Regarding this, can I do the calculations on the input file and overwrite
> it
> after calculating, or I will have to open the output file and write into
> it?
> I am asking this because I will have several files in the directory. So, I
> will have to read the directory using readdir, and process each file. It
> is
> better if I open the file in read-write mode, process it and overwrite the
> file. Just wanted to know if it is safe?
>
> Please guide on how to get started with this?
>
> Also can I do something like below:-
>
>
> while ($line=readline($IN_FILE))
> {
> my @cdr=split (/,/, $line) ;
> $cdr[13] = $cdr[6]*5 ; ###Can I do something like this
> }
>
> Thanks,
> Mihir
>

Jeff Pang

2007-08-24, 4:01 am

2007/8/24, Mihir Kamdar <kamdarmihir06@gmail.com>:
> $cdr[13] = $cdr[6]*5 ;
> $hash{"@cdr[2,3,6,7]"}=$line;

Hello,

I checked the codes,but I'm not sure what's the meanings of those 2 lines above.
I can't see the logic relation with the 2 lines.
Yes $cdr[13] = $cdr[6]*5 has changed the array element,but didn't
change the line content.Are you sure this?
Also as I've said, @hash{@arr} is different with $hash{"@arr"}.see below,

$ perl -Mstrict -Mwarnings -MData::Dumper -e 'my %hash ;@hash{qw/a b
c/} = 111;print Dumper \%hash'
$VAR1 = {
'c' => undef,
'a' => 111,
'b' => undef
};

$ perl -Mstrict -Mwarnings -MData::Dumper -e 'my %hash ;$hash{qw/a b
c/} = 111;print Dumper \%hash'
$VAR1 = {
'abc' => 111
};

Rob Coops

2007-08-24, 4:01 am

Of course it is....

Your %hash gets filled with a structure that looks like this:
{ KEY => VALUE }
{ "@cdr[2,3,6,7]"=> $line }

Then you take all the values and write them out to a file. Since you never
changed the $line variable you should get the same result in your out file
as you had in the in file. It is good to see perl still works.

Try the following just before the line: $hash{"@cdr[2,3,6,7]"}=$line;

$line = join(/,/,@cdr);

This way you fill the $line variable with the contents of the just modified
array this should get you the desired result (though there are more stylish
ways of writting it it will get you the result you are looking for)

Rob.

On 8/24/07, Mihir Kamdar <kamdarmihir06@gmail.com> wrote:
>
> On 8/24/07, Jeff Pang <rwwebs@gmail.com> wrote:
>
> Hi,
>
> Please look at my code below and comment. I am trying to manipulate 13th
> field of my record. But I am not getting the desired result in the output.
> The output file is the same as the input file.
>
> #!/usr/bin/perl
>
> use warnings ;
>
> my $file_path =
>
> '/home/user71/RangerDatasource/Customization/TelekomMalaysia/Scripts/Tests/cprogs/files/ratetest';
> my $write_path =
>
> '/home/user71/RangerDatasource/Customization/TelekomMalaysia/Scripts/Tests/cprogs/files/rateop'
> ;
> my %times ;
> my $continue = 1;
>
> $SIG{INT} = $SIG{TERM} = sub { $continue = 0 };
>
> while ($continue) {
> opendir my $dh, $file_path or die $!;
> while (my $file = readdir $dh) {
> my $fname = "$file_path/$file" ;
> next unless -f $fname;
> unless (exists $times{$file}){
> my $line;
> open (my $IN_FILE,"<","$file_path/$file") or die
> $!." file not found" ;
>
> while ($line=readline($IN_FILE))
> {
> my @cdr=split (/,/, $line) ;
> $cdr[13] = $cdr[6]*5 ;
> $hash{"@cdr[2,3,6,7]"}=$line;
> }
> close $IN_FILE ;
>
> open (my $OUT_FILE,">","$write_path/$file.out") or
> die $!;
> while (my($key, $value) = each %hash)
> {
> print $OUT_FILE $value;
> }
> close $OUT_FILE;
> }
> }
> closedir $dh ;
> }
>
>
>
> Thanks,
> Mihir
>

Jeff Pang

2007-08-24, 4:01 am

2007/8/24, Mihir Kamdar <kamdarmihir06@gmail.com>:

> But a few improvizations. $cdr[13] that I am trying to manipulate is a
> "amount" field and should be a floating point value...how can I print it as
> floating point in perl?
>

Perl isn't a strong type language,it doesn't mind which data type it used.
You can use printf() for printing a floating vlaue,like,
$ perl -e 'printf("%.2f",3)'
3.00

> Also I am reading from an input file and writing to an output file. Is it
> possible to read and write to the same file without any side-effects??
>

Yes,just open the file for reading and handling,store the results to
an array as you did,close the file.then re-open the file for
writing,and write the content in the array to the file.It's no
problem.

> @Jeff:- As you said that @hash{@arr} is different with $hash{"@arr"}, I am
> sorry but I am not sure what you are trying to point out through this.

@hash{@arr} means this hash has more than one keys (given the @arr has
3 elements,then this hash has 3 keys,each key is one of this array's
elements).
$hash{"@arr"} means the hash has only one key,it's the same as:
$key = join '',@arr;
$hash{$key} = ...;

Also @hash{@arr} is a hash slice,but $hash{"@arr"} isn't.

Mihir Kamdar

2007-08-24, 4:01 am

Hi,

Thanks...i got my mistake...it worked after the below as Rob suggested:-
my @cdr=split (/,/, $line) ;
$cdr[13] = $cdr[6]*5.0 ;
$line = join(",",@cdr);
$hash{"@cdr[2,3,6,7]"}=$line;

But a few improvizations. $cdr[13] that I am trying to manipulate is a
"amount" field and should be a floating point value...how can I print it as
floating point in perl?

Also I am reading from an input file and writing to an output file. Is it
possible to read and write to the same file without any side-effects??

@Jeff:- As you said that @hash{@arr} is different with $hash{"@arr"}, I am
sorry but I am not sure what you are trying to point out through this. Can
you please explain at a very basic level so that I can understand how it
works and how it affects my code?

Thanks,
Mihir

Mihir Kamdar

2007-08-24, 7:59 am

Perl isn't a strong type language,it doesn't mind which data type it used.
> You can use printf() for printing a floating vlaue,like,
> $ perl -e 'printf("%.2f",3)'
> 3.00

that's right, but with respect to this particular code of mine, the $cdr[13]
is the "amount" field which should be a float. I am manipulating it like
this:-

while ($line=readline($IN_FILE))
{
my @cdr=split (/,/, $line) ;
$cdr[13] = $cdr[6]*5.0 ;
$line = join(",",@cdr);
$hash{"@cdr[2,3,6,7]"}=$line;
}

and then writing the output like this:-

open (my $OUT_FILE,">","$write_path/$file.out") or die $!;
while (my($key, $value) = each %hash)
{
print $OUT_FILE $value;
}

Here if I want the $cdr[13] to be written as a float(ex. 15.00), then how do
I write it?

Thanks,
Mihir

Jeff Pang

2007-08-24, 7:59 am

2007/8/24, Mihir Kamdar <kamdarmihir06@gmail.com>:
>
> Here if I want the $cdr[13] to be written as a float(ex. 15.00), then how do
> I write it?
>

$cdr[13] = sprintf "%.2f", $cdr[6]*5.0 ;

Jenda Krynicky

2007-08-27, 7:23 pm

From: "Mihir Kamdar" <kamdarmihir06@gmail.com>
> I have a csv file. I wanted to do some calculations on some of its fields,
> like multiplying the 7th field with the 13th field and overwriting the 13th
> field with the answer of my calculation.
>
> Regarding this, can I do the calculations on the input file and overwrite it
> after calculating, or I will have to open the output file and write into it?
> I am asking this because I will have several files in the directory. So, I
> will have to read the directory using readdir, and process each file. It is
> better if I open the file in read-write mode, process it and overwrite the
> file. Just wanted to know if it is safe?

I think you should use either DBD::CSV or DBD::AnyData and SQL to
make your changes.

Something like
#!/usr/bin/perl
use strict;
use DBI;

my $dbh = DBI->connect("DBI:CSV:f_dir=/dir/with/the/csvs")
or die "Cannot connect: " . $DBI::errstr;

$dbh->{'csv_tables'}->{'SomeName'} = { 'file' =>
'SomeName20070827.csv'};
# tie the table name to the filename

my $sth = $dbh->prepare('UPDATE SomeName SET Foo = Foo * Bar');
$sth->execute();
# specify an execute the action

__END__

And that's it. Let the modules do the heavy work for you.

Jenda
P.S.: If you do not know SQL, learn it! You can be a productive
programmer without knowing anything about XML or whatever's the
current hype, you can't be one without SQL.
===== Jenda@Krynicky.cz === http://Jenda.Krynicky.cz =====
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery

Oryann9

2007-08-28, 7:01 pm

> Something like
> #!/usr/bin/perl
> use strict;
> use DBI;
>
> my $dbh =
> DBI->connect("DBI:CSV:f_dir=/dir/with/the/csvs")
> or die "Cannot connect: " . $DBI::errstr;
>
> $dbh->{'csv_tables'}->{'SomeName'} = { 'file' =>
> 'SomeName20070827.csv'};
> # tie the table name to the filename
>
> my $sth = $dbh->prepare('UPDATE SomeName SET Foo =
> Foo * Bar');
> $sth->execute();
> # specify an execute the action
>
> __END__

I read the CPAN module DBD::CSV and still had some
questions.

1)Does this create a "in memory" database with data
from the spreadsheet for manipulation?

2)This is really

! Does anyone have a working
example of inserting, deleting and substituting data
in cells?

In the doc it states:

$dbh->do("UPDATE $table SET id = 3 WHERE id = 1");

and

$dbh->do("DELETE FROM $table WHERE id > 1");

Would $table be the name of the csv file?

thank you
:)

________________________________________
________________________________________
____
Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us. http://surveylink.yahoo.com/gmrs/ya..._invite.asp?a=7

Lawrence Statton

2007-08-28, 7:01 pm

> I read the CPAN module DBD::CSV and still had some
> questions.
>
> 1)Does this create a "in memory" database with data
> from the spreadsheet for manipulation?

What did reading the source tell you?

>
> 2)This is really

! Does anyone have a working
> example of inserting, deleting and substituting data
> in cells?

Well, since it is a DBI driver, *any* DBI-aware program could use it,
within the limitations of the subset of SQL that it supports.

>
> In the doc it states:
>
> $dbh->do("UPDATE $table SET id = 3 WHERE id = 1");
>
> and
>
> $dbh->do("DELETE FROM $table WHERE id > 1");
>
> Would $table be the name of the csv file?
>

That is the default behavior, however ...

[color=darkred]

.... here you can see they are mapping a table SomeName to the file
"SomeName20070827.csv"

--
Lawrence Statton - lawrenabae@abaluon.abaom s/aba/c/g
Computer software consists of only two components: ones and
zeros, in roughly equal proportions. All that is required is to
place them into the correct order.

Oryann9

2007-08-28, 7:01 pm

--- Lawrence Statton <lawrence@cluon.com> wrote:
[color=darkred]
> data
>
> What did reading the source tell you?
>
> data
>
> Well, since it is a DBI driver, *any* DBI-aware
> program could use it,
> within the limitations of the subset of SQL that it
> supports.
>

Lawrence

Thank you for replying but since I am trying to learn
your response did not help much. :(
Any add'l help?

Anyway here is what I have tried:

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect
(
"DBI:CSV:f_dir=/cygdrive/c/temp",
"DBI:CSV:csv_sep_char=\\;"
)
or die "Cannot connect: " . $DBI::errstr;

$dbh->{'csv_tables'}->{'data'} = { 'file' =>
'UID_CHECK.csv'};
# tie the table name to the filename

my $sth = $dbh->prepare("SELECT * FROM data");
$sth->execute() or die "Cannot execute: " .
$sth->errstr();
$sth->finish();
$dbh->disconnect();

IN DEBUG MODE:

DB<1> n

Execution ERROR: No such column 'PL.1,'.

at /usr/lib/perl5/site_perl/5.8/SQL/Statement.pm line
2052

SQL::Statement::do_err('DBD::CSV::Statem
ent=HASH(0x1093cc54)',
'No such column 'PL.1,'') call
ed at /usr/lib/perl5/site_perl/5.8/SQL/Statement.pm
line 1665

SQL::Statement::verify_columns('DBD::CSV
::Statement=HASH(0x1093cc54)',
'DBI::st=HASH(0x10953cf0
)', 'SQL::Eval=HASH(0x1096386c)', 'ARRAY(0x10852618)')
called at /usr/lib/perl5/site_perl/5.8/SQL/State
ment.pm line 778

SQL::Statement::SELECT('DBD::CSV::Statem
ent=HASH(0x1093cc54)',
'DBI::st=HASH(0x10953cf0)', 'ARR
AY(0x10957424)') called at
/usr/lib/perl5/site_perl/5.8/SQL/Statement.pm line 196

SQL::Statement::execute('DBD::CSV::State
ment=HASH(0x1093cc54)',
'DBI::st=HASH(0x10953cf0)', 'AR
RAY(0x10957424)') called at
/usr/lib/perl5/site_perl/5.8/cygwin/DBD/File.pm line
441
eval {...} called at
/usr/lib/perl5/site_perl/5.8/cygwin/DBD/File.pm line
441

DBD::File::st::execute('DBI::st=HASH(0x1
0953cf0)')
called at csv_manip.plx line 19
main::(csv_manip.plx:20): $sth->finish();
DB<1>

________________________________________
________________________________________
____
Luggage? GPS? Comic books?
Check out fitting gifts for grads at Yahoo! Search
http://search.yahoo.com/search?fr=o...ion+gifts&cs=bz

Chas Owens

2007-08-28, 7:01 pm

On 8/28/07, oryann9 <oryann9@yahoo.com> wrote:
snip
> Thank you for replying but since I am trying to learn
> your response did not help much. :(
> Any add'l help?
snip

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect(
'DBI:CSV:f_dir=csvdb', #the directory csvdb in the current dir
'', #user
'', #pass
{
AutoCommit => 1, #commit after ever insert, update, delete
PrintError => 0, #don't print errors
RaiseError => 1, #because I want to die instead
ChopBlanks => 1, #turn "foo ", in the db into "foo"
FetchHashKeyName => 'NAME_lc', #use lower case keys
csv_sep_char => ';' #use ; instead of ,
}
) or die DBI->errstr;

unless (-f "csvdb/test") {
$dbh->do("create table test (id int, name char(10))");
}

$dbh->do("delete from test");

my $insert = $dbh->prepare("insert into test (id, name) values (?, ?)");

$insert->execute(1, "Alice");
$insert->execute(2, "Bob");
$insert->execute(3, "Charlie");
$insert->execute(4, "Dwight");

my $sth = $dbh->prepare("select * from test order by id");
$sth->execute;
while (my $row = $sth->fetchrow_hashref) {
print "$row->{id} is $row->{name}\n";
}
$sth->finish;

$dbh->disconnect;

Sponsored Links