Extract column of excel

[MynameisAchint]

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

[kcott]

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.

[MynameisAchint]

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

[Tux]

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

[MynameisAchint]

#!/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";
}

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

[hdb]

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

[vinoth.ree]

Check this perl module Spreadsheet::ParseExcel::Simple

---

All is well

[MynameisAchint]

#!/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";
}

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

[hdb]

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.

[Anonymous Monk]

Hi

Also have a look at Text::CSV_XS.

J.C.

[MynameisAchint]

Hey Has anybody got a script for it , it would be really useful for me

[Anonymous Monk]

Possibly there is one in here Examples at
• join - join two files according to a common key
• part - split up files according to column value
• Merge two files with similar column entries
• Opening multiple files ( csvpaste.pl )
• Open multiple file handles?
• X, Y Table structure
• How to extract the particular residues from PDB files(text csv split hash bioperl)
• Select only desired features from a text (text csv split hash bioperl)
• Sort on Table headers (text csv split hash bioperl)
• I need help joining tab-delimited files/tables!
• split then join based on common value in field
• Smart way to read a file vertically?
• reformatting tab delimited file
• edit a CSV and "in-place" replacement
• Re: Reversing a mysql table, transpose-tsv -- invert a tab-delimited table

• Text::CSV

  examples/csv2xls	Script to onvert CSV files to M$Excel
  examples/csv-check	Script to check a CSV file/stream
  examples/csvdiff	Script to shoff diff between sorted CSV files
  examples/parser-xs.pl	Parse CSV stream, be forgiving on bad lines

 [CSV hash ] CSV hash

Best way to match a hash with large CSV file

perl hash to CSV using Text::CSV_XS

Issue parsing CSV into hashes?

Veriable Length Array/Hash derived from CSV to populate an XML

extracting data from CSV files and making hash of hashes

Re^2: build hash from csv file

Encoding a hash in perl before saving it as a CSV file

hash from CSV-like structure

Read the csv file to a hash....

Parsing CSV into a hash

build hash from csv file

Converting a CSV list to a list of hashrefs naming the fields

Converting File Delimiters (csv to tsv to psv, use App::CCSV)