topic Re: Perl Help needed: reading file into array and pulling certain fields? in Operating System - Linux

Perl Help needed: reading file into array and pulling certain fields?

Kristopher March_1 — Wed, 02 Nov 2005 14:31:24 GMT

I'm a beginner - Day 1.

I'm reading a .csv file into an array (not sure if this is correct approach). Then I'm trying to pull certain columns of data from that.

Here's the script I've been working on:

open (DATAFILE, "Inventory.csv");
@PND_inv = ;
$numitems = @PND_inv;
print "There are $numcmd commands.", "Here is your data: @PND_inv[0]\n";

What I realized was that items are on a single line and not columnized as I had thought. So I'm getting the entire first line and not the entire first column.

I know how to do this with cat and awk, but I need to this single script to work on both NT and UNIX platforms.
% cat filename.csv | awk -F , '{print $4, $5}' > /tmp/newfile.out

Where am I going wrong?

Re: Perl Help needed: reading file into array and pulling certain fields?

James R. Ferguson — Wed, 02 Nov 2005 14:44:20 GMT

Hi:

Just as you would do in 'awk' (or with 'IFS' in the shell) so must you do in perl -- setup your field separator.

You can do this with the perl switch '-F' in conjuction with '-a' to autosplit. For instance:

# echo "a,b,c,d" |perl -lanF"," -e 'print $F[2]' -

...would print "c" since it is the second (zero-relative) field that is delimited by ",".

Regards!

...JRF...

Re: Perl Help needed: reading file into array and pulling certain fields?

James R. Ferguson — Wed, 02 Nov 2005 14:52:19 GMT

Hi (again):

I should hasten to add that you can 'split()' fields into items in an array:

# perl -wle '@a=split(/,/,$ARGV[0]);print $a[2]' a,b,c,d

...also returns "c"...

Regards!

...JRF...

Re: Perl Help needed: reading file into array and pulling certain fields?

Kristopher March_1 — Wed, 02 Nov 2005 15:01:29 GMT

Thanks for the quick response. Although the one liner looks simple I'm still having trouble understanding exactly what's going on.

At what point are you reading the file in?

Re: Perl Help needed: reading file into array and pulling certain fields?

Torsten. — Wed, 02 Nov 2005 15:08:00 GMT

It is always a good idea to learn a new language! But likely you won't be an expert after day 1.

But here is the quick alternative:

Use "GNU utilities for Windows" and run your shell script on win as on unix.

Re: Perl Help needed: reading file into array and pulling certain fields?

James R. Ferguson — Wed, 02 Nov 2005 15:10:34 GMT

Hi (again):

In the example:

# perl -lanF"," -e 'print $F[2]' -

I was using the '-n' switch to read file(s) sepecified on the commandline. The '-' argument equates to STDIN.

Consider:

# cat /tmp/mydata
one,two,three
red fish,blue fish,one fish,two fish

# perl -lanF, -e 'print $F[2]' /tmp/mydata

...yields:

three
one fish

...since I asked to print the second field ($F[2] in the automatically split (-a) array built from reading /tmp/mydata. The '-n' creates a read loop just like you would with :

while (<>)

I used the '-l' to autogenerate linefeeds when printing.

Regards!

...JRF...

Re: Perl Help needed: reading file into array and pulling certain fields?

H.Merijn Brand (procura — Thu, 03 Nov 2005 02:32:34 GMT

Splitting on ',' for csv files is both easy and wrong. Not for simple csv, but consider these two csv lines:

1,,Blah,2,"Foo, Bar"

and

1;;Blah;2;Foo, Bar

the first is correct CSV, and the seconds is Micro$oft's way of interpreting the C in CSV (C translates to semicolon in M$' dictionaries, instead of Comma)

These problems are all solved for perl in the Text::CSV_XS module (or Text::CSV for the slower pure-perl version)

Now I have written a module to hide all the implementation specifics for spreadsheets (and CSV) and make a uniformal interface:

--8<---
use Spreadsheet::Read;
my $ref = ReadData ("Inventory.csv");

print "This CSV file contains ", $ref->[1]{maxcol}, " columns (fields) and ", $ref->[1]{maxrow}, " rows (lines).\n";
print "The third field on line 6 is: ", $ref->[1]{cell}[3][6], "\n";
-->8---

The module comes with a command line utility, so you can see the content of Excel (xls), OpenOffice (sxc, ods), and CSV (csv) pretty easy:

# xlscat Inventory.csv

# xlscat -?

will show the available options.
If you also have Perl/Tk installed, a new util 'ss2tk' will be included in the next release, and it will give you a multi-tabbed perl/tk read-only interface to these formats with search capabilities

And yes, Spreadsheet::Read and Text::CSV_XS work on both Linux and NT (and HP-UX, and AIX, and Solaris, and Cygwin, and .....)

Enjoy, Have FUN! H.Merijn

Re: Perl Help needed: reading file into array and pulling certain fields?

Muthukumar_5 — Thu, 03 Nov 2005 05:53:48 GMT

You can use cut utility to get better performance :) Any way, if you are going with perl then,

echo "a,b,c,d,e,f" | perl -aF, -ne 'print "@F[3,4]\n";'

If you want to update in same file then,

perl -aF, -ni -e 'print "print "@F[3,4]\n";'

-Muthu

Re: Perl Help needed: reading file into array and pulling certain fields?

James R. Ferguson — Thu, 03 Nov 2005 10:26:44 GMT

Hi (again):

Merijn: While I was aware of the inherent problems with CSV files, your pointer was (aw always) very germane. Thanks.

Muthu: I fail to see how 'cut' could provide better performance in repetive processing like this. Given that 'cut' isn't a shell built-in you would have to spawn a new process (for 'cut') for every line read.

...JRF...

Re: Perl Help needed: reading file into array and pulling certain fields?

Hein van den Heuvel — Thu, 03 Nov 2005 11:42:59 GMT

Kristopher,

Welcome to the ITRC forums. Be sure to glance over the 'rules' a little: http://forums1.itrc.hp.com/service/forums/helptips.do?#overview

First, may I applaud you for tackling a new problem with a new language. Be sure to read up on the many generic web resources on perl. (google is your friend)

Next, be sure to read Merijn's reply 5 times over. He (procura) is 'the man' in this space.

Finally, my own modest contribution.
It is still using a simplistic 'split /,/'... boo hiss... but it may help reduce the learning curve a little.
My solution expects a 'header' line. And is willing to deal with column numners as well as names.
It currently reads the whole file into arrays, but I find that often I don't need the data in arrays, just process the lines as they come by such as the other solutions imply. Code, Data, and sample usage below.

Hope this helps,
Hein.

------ csv.pl ----------

use strict 'vars';
my ($datafile, $tmp, $i, $rows, $columns, $x, $y);
my (@col, @dataline, @cell, %col);

$datafile = shift @ARGV; # pick up data file name from command line
$datafile = "Inventory.csv" unless $datafile; # Provide default
$x = shift @ARGV;
$y = shift @ARGV;

open (DATAFILE, "<$datafile") or die "Failed to open $datafile for input";
#
# Read column-header line
# create array of column-names to numbers and numbers to names
#
$_ = ;
chomp;
foreach $tmp (split /,/) {
@col[$columns] = $tmp;
$col{$tmp} = $columns++;
}
#
while () {
@dataline[$rows] = $_;
$i = 0;
foreach $tmp (split /,/) { $cell[$rows][$i++] = $tmp };
$rows++;
}
print "There were $columns columns read with $rows rows of datalines\n";
print "Columns are: ", join(", ",@col), "\n";
if (defined $y) {
if ($y =~ /\d+/) {
print "cell $x,$y = $cell[$x][$y ]\n";
}else {
print "cell $x,$y = $cell[$x][$col{$y}]\n";
}
} else {
print "no specific cell data requested\n";
}

------ C:\Temp>type tmp.dat -----------
a,b,c,d
aap,noot,mies,teun
12,34,56,78
test,,more test,done

------------- usage examples -------------

C:\Temp>perl csv.pl tmp.dat
There were 4 columns read with 3 rows of datalines
Columns are: a, b, c, d
no specific cell data requested

C:\Temp>perl csv.pl tmp.dat 0 0
There were 4 columns read with 3 rows of datalines
Columns are: a, b, c, d
cell 0,0 = aap

C:\Temp>perl csv.pl tmp.dat 0 d
There were 4 columns read with 3 rows of datalines
Columns are: a, b, c, d
cell 0,d = teun

C:\Temp>perl csv.pl tmp.dat 2 2
There were 4 columns read with 3 rows of datalines
Columns are: a, b, c, d
cell 2,2 = more test