MynameisAchint has asked for the wisdom of the Perl Monks concerning the following question:

Hey , I need to extract specific columns of an excel sheet in csv format , there are about 1000 entries in a column and I want to store them in an array . I am new to perl so I seek your help . I am working on windows Thanks in advance

Replies are listed 'Best First'.
Re: Extract column of excel
by kcott (Archbishop) on Jun 04, 2013 at 06:26 UTC

    G'day MynameisAchint,

    Welcome to the monastery.

    While I realise this is your first posting here, unfortunately it has some problems. Please read "How do I post a question effectively?". Do note that PerlMonks is not a code writing service; if you want to post a specification and then sit back while someone else writes your code, you'll need to look elsewhere.

    It's unclear from your post whether you're using Excel purely as a reader for CSV files or if Excel has a more integral role. If the former, you can bypass Excel altogether and use Text::CSV; if the latter, you'll need to identify whether you're working with the older (*.xls) or newer (*.xlsx) format and then use (probably) either Spreadsheet::ParseExcel or Spreadsheet::XLSX.

    -- Ken

[reply]
[d/l]
[select]
      Hey Ya this is my first post so there are mistakes. I don't want anyone to write the code for me , I have written the code posted too ( though not very properly ) . The issue I am having is that it takes the entries not from the starting of the coloumn of csv but from somewhere in between and till the end . I am guessing that it takes only specified dimensions of the sheet . So I just want your help in ensuring that it takes all the entries of a column I am working with csv format
[reply]
Re: Extract column of excel
by Tux (Canon) on Jun 04, 2013 at 09:40 UTC

    So deling with CSV in a spreadsheet way. Install Text::CSV_XS and Spreadsheet::Read, and then:

    use Spreadsheet::Read;

my $ss = ReadData ("file.csv");

# Spreadsheet::Read indexes from 1. 0 is the control record/column
my @sheet  = $ss->[1]; # CSV has just 1 sheet
my @column = $sheet->{cell}[4]; # 4th column, unformatted

    [download]

    Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
      #!/bin/perl -w
use strict;

#variables you can change to suit your needs
my $column_separator = ",";
my $column_number = "1";
$column_number--;
my $file="Working_On.csv";
open(FILE,"<","$file");
my @lines=<FILE>;
close FILE;
  
foreach my $line (@lines){
 my @columns = split(/$column_separator/,"$line");
 print $columns[$column_number],"\n";
}

      [download]
      This is my code , but it does not read the entire csv file but reads from a particular cell of csv . I am working on one sheet only but the entries in column are about 1000. So can you suggest a way that the entire csv file is read at once
[reply]
[d/l]

        Your code works fine! I have only added a chomp but that does not really make a difference... 

        #!/bin/perl -w 
use strict; 
#variables you can change to suit your needs 
my $column_separator = ","; 
my $column_number = "2";
$column_number--; 
my @lines=<DATA>; 
foreach my $line (@lines){ 
        chomp($line);
        my @columns = split(/$column_separator/,"$line"); 
        print $columns[$column_number],"\n"; 
}

__DATA__
1,2,3,4
A,B,C,D

        [download]
[reply]
[d/l]
[select]
Re: Extract column of excel
by vinoth.ree (Monsignor) on Jun 04, 2013 at 05:33 UTC
[reply]
      #!/bin/perl -w
use strict;

#variables you can change to suit your needs
my $column_separator = ",";
my $column_number = "8";
$column_number--;
my $file="Working_On.csv";
open(FILE,"<","$file");
my @lines=<FILE>;
close FILE;

foreach my $line (@lines){
 my @columns = split(/$column_separator/,"$line");
 print $columns[$column_number],"\n";
}

      [download]
      This code is working but the problem is that it does not take the entire column it truncates the column , if anyone can look why the code does not take the entire column . thanks
[reply]
[d/l]

        While barely readable your code suggests that you are processing a CSV file, not an Excel file. This is quite a different question.

        Can you please specify in which way the column is truncated? Are you not getting all rows or are entries somehow garbled? Again, quite different approaches would be required.

[reply]
Re: Extract column of excel
by Anonymous Monk on Jun 04, 2013 at 05:54 UTC

    Hi

    Also have a look at Text::CSV_XS.

    J.C.

[reply]
      Hey Has anybody got a script for it , it would be really useful for me
[reply]
[reply]
[d/l]

Back to Seekers of Perl Wisdom