Why not just parse each workbook of the spreadsheet using Spreadsheet::ParseExcel? The first example shows how to do this. Modifying this example to work on a per column basis (rather than by row) would be trivial.

Update: My mistake, you're working with a csv file.

book.csv:

1,2 ,3
1,2, 3
1,2,43
1
12,1
apple, 1, 3
orange
7,onion, 8, 9
[download]

Sample code using Text::CSV:

#!/usr/bin/perl
use strict;
use warnings;
 
use Text::CSV;
use Data::Dumper;

my $csv = Text::CSV->new({ sep_char => ',' });
 
my $file = "book.csv";

open(my $csvdata, '<', $file) 
    or die "Could not open '$file' $!\n";

while (my $line = <$csvdata>) {
    chomp $line;

    if ($csv->parse($line)) {     
        my @fields = $csv->fields();
        print Dumper \@fields;
    }
}
[download]

What exactly are you trying to achieve?

if I want column 1 entries to go in one array , second column entry to another and so on . How can i define my array

use strict;
use warnings;
use Data::Dumper;

my $ncols = 5; # or obtained from user
my @bigfatarray = ();
push @bigfatarray, [] for [1 .. $ncols];
while (<DATA>) {
    my @cols = split (/,/, $_);
     for my $i (0 .. $ncols - 1) {
          push @{$bigfatarray[$i]}, $cols[$i];
      }
}
print Dumper (\@bigfatarray);
exit;
__DATA__
1,2,3,4,5
a,b,c,d,e
[download]

Obviously, you'll need to do more than just split your inputs but this should serve as an illustration.

if I want column 1 entries to go in one array , second column entry to another and so on . How can i define my array

• perldsc - Perl Data Structures Cookbook - ARRAYS OF ARRAYS

• perlreftut - Mark's very short tutorial about references this explains 90% of what you need to know about references. Absolutely essential reading!!
• perlref - Perl references and nested data structures this explains the rest. You can skip this until much later in your Perl coding!

If you have a header line in your data, you might want to consider to use a hash of arrays. Each array would contain one of your columns and could be adressed by the name given in the header.

use strict;
use warnings;
use Data::Dumper;

my $n = 4;    # number of columns, given as user input
my %hcolumns; # this is the hash of the columns you are looking for

my @header = split /,/,<DATA>; # pls use Text::CSV for any real work
chomp @header;
while(<DATA>){
        chomp;
        my @record = split /,/;    # pls use Text::CSV for any real wo
+rk
        for (0..$n-1) { 
                push @{ $hcolumns{$header[$_]} }, $record[$_];        
+ }
}
print Dumper \%hcolumns; # hash of columns

__DATA__
Name,FirstName,Instrument
Mangelsdorff,Albert,Trombone
Parker,Charlie,Trumpet
Coltrane,John,Tenor
Evans,Bill,Piano
[download]

The number of columns can still be provided by the user. In this example here, I have chosen a number larger that the available columns resulting in an empty column (and some warnings when running the script).

#!/usr/bin/perl 
use strict;
use warnings;

my $fileName = $ARGV[0];
my $numberOfColumns = 0;
my @line = ();
my $lineNumber = 0;
my $x = 0;
my $p = 0;
my @arrayOfArrays = ();
my $firstTime = 0;

open (FILE, "<", $fileName);
while (<FILE>)
    {
        chomp;
        @line = split (",", $_);
        if ($firstTime == 0)
            {
                $numberOfColumns = @line;
                $firstTime = 1;
            }
        for($x = 0; $x < $numberOfColumns; $x += 1)
            {
                $arrayOfArrays[$x][$lineNumber] = $line[$x];
            }

        $lineNumber +=1; 
    }

#to access your values: column one: $p=0, column 2: $p = 1 etc.
for ($p = 0; $p < $numberOfColumns; $p += 1)
    {
        for ($x= 0; $x<$lineNumber; $x += 1)
            {
                print STDERR "$arrayOfArrays[$p][$x]\n";
            }
    }
[download]

Or an array of arrays, but this would use more memory than using hashes as suggested above, which might be important if your files are very large:

I don't understand this, please could you explain:


1. Why would an array of arrays use more memory than a hash of arrays?
   I'd expect it to be the other way round.
2. would the "of arrays" data occupy the same amount of memory in both AoA and HoA in this example?
   (This seems reasonable as this is just the total data of all the rows.)
3. From (2) follows: Why does an array use more memory than a hash?

Thanks :)

Touché!

My mistake. I guess the hash of arrays would take up slightly more memory, because instead of using an array index to label individual arrays, it uses the column headers to label them. I imagine that if the number of columns is low, there wouldn't be a large difference?

Thanks for pointing that out!

-Michael