extracting single column

[Anonymous Monk]

Hello every one, i just have to obtain data from a single column in a file. i am not able to open it in excel how to extract that particular column alone using perl

[moritz]

Read the file line by line, break the lines up into columns, and print the one you're interested in.

perlintro and split might be of interest.

[Sewi]

Spreadsheet::ParseExcel might help if the file is a real Excel file and not a CSV.

[bcarroll]

Check out http://search.cpan.org/dist/excel2txt/
That script will convert each worksheet in an Excel spreadsheet to separate CSV text files. You can then parse each .csv and, split out the column you are looking for.

[gg48gg]

#!/bin/perl -w
use strict;
#for a csv file you can try something like this,
#but there are dozens of ways to do this.
#you should read a book called "Sam's learn perl in
#24 hours" (or similar title).

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

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

[Sewi]

There are few lines where I don't agree:


my $column_separator = ",";

Better:

my $column_separator = qr/,/;

...imaging someone wants to use "." as separator.

my $column_number = "3";

Don't write numbers as text, write them as numbers (cut away those " chars).

Loading the whole file into memory might not be that good idea, imagine it's a 10 GB file. Why not parse it line-by-line?


open(FILE,"<","$file");
while (<FILE>) {
 my @columns = split(/$column_separator/);
 print $columns[$column_number],"\n";
}
close FILE;

[Anonymous Monk]

Hey my column has around 900 entries , this code does not extract all of them , but only a part of them . so if you can modify this for me , I will be grateful