RE: parsing a large excel file
- From: stewart.anderson@xxxxxxxxxxxxx (Stewart Anderson)
- Date: Tue, 22 Jul 2008 10:11:56 +0100
-----Original Message-----data
From: Stewart Anderson
Sent: 22 July 2008 09:34
To: ANJAN PURKAYASTHA; beginners@xxxxxxxx
Cc: Stewart Anderson
Subject: RE: parsing a large excel file
-----Original Message-----
From: ANJAN PURKAYASTHA [mailto:anjan.purkayastha@xxxxxxxxx]
Sent: 22 July 2008 02:41
To: beginners@xxxxxxxx
Subject: parsing a large excel file
to all,
i have installed Spreadshee::ParseExcel to parse some large excel
to Pfiles.
Here is the problem I'm facing. I need to parse data from columns M
canand
rows 10 to 43000. Now I know that there is a PrintArea method that
end
an area of a worksheet specified in (start row, start col, end row,
tocol). However I'm having difficulty in specifying these parameters
correctly
for the PrintArea method. I'm also not sure what the output is going
rowlookI have only used the Simple version previously to parse and entire
like.
does anyone in this forum have any pointers?
all advice will be appreciated.
tia,
anjan
but the method Cell ( ROW, COL ) In the docs for the module youMaybe I spoke to soon, that method seem to return the iobject, not
showed suggests you can get at the data directly.
Stu
100% sure what that gives you without trying it.
But the sample script in the doc looks like a good place to start,
just set your row min/max and col min/max and you should be close to
getting something.
However, the sample program in the docs work as is. It should be
easy to adapt it for your needs. Try this as a start. The first bit is
pretty much out of the box and the last bit just shows you can extract
what row/column you want.
The data is at the end, load it into excel.
#! /usr/bin/perl
use warnings;
use strict ;
use Data::Dumper;
use Spreadsheet::ParseExcel;
my $excel =
Spreadsheet::ParseExcel::Workbook->Parse('c:\temp\filetest.xls');
foreach my $sheet (@{$excel->{Worksheet}}) {
printf("Sheet: %s\n", $sheet->{Name});
$sheet->{MaxRow} ||= $sheet->{MinRow};
foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
$sheet->{MaxCol} ||= $sheet->{MinCol};
foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
my $cell = $sheet->{Cells}[$row][$col];
if ($cell) {
printf("( %s , %s ) => %s\n", $row, $col,
$cell->{Val});
}
}
}
}
print "Extract specific row/cell row 2, col b \n" ;
foreach my $sheet (@{$excel->{Worksheet}}) {
my $row = 2;
my $col = 2;
my $cell = $sheet->{Cells}[$row][$col];
printf("( %s , %s ) => %s\n", $row, $col, $cell->{Val});
}
Data to load in excel
1a,1b,1c,1d,1e,1f
2a,2b,3c,4d,2e,2f
3a,3b,3c,3d,3e,3f
4a,4b,4c,4d,4e,4f
Information in this email including any attachments may be privileged, confidential and is intended exclusively for the addressee. The views expressed may not be official policy, but the personal views of the originator. If you have received it in error, please notify the sender by return e-mail and delete it from your system. You should not reproduce, distribute, store, retransmit, use or disclose its contents to anyone. Please note we reserve the right to monitor all e-mail communication through our internal and external networks. SKY and the SKY marks are trade marks of British Sky Broadcasting Group plc and are used under licence. British Sky Broadcasting Limited (Registration No. 2906991), Sky Interactive Limited (Registration No. 3554332), Sky-In-Home Service Limited (Registration No. 2067075) and Sky Subscribers Services Limited (Registration No. 2340150) are direct or indirect subsidiaries of British Sky Broadcasting Group plc (Registration No. 2247735). All of the companies mentioned in this paragraph are incorporated in England and Wales and share the same registered office at Grant Way, Isleworth, Middlesex TW7 5QD.
.
- Follow-Ups:
- Re: parsing a large excel file
- From: Anjan Purkayastha
- Re: parsing a large excel file
- References:
- parsing a large excel file
- From: Anjan Purkayastha
- RE: parsing a large excel file
- From: Stewart Anderson
- parsing a large excel file
- Prev by Date: RE: parsing a large excel file
- Next by Date: fetching to docs using wget
- Previous by thread: RE: parsing a large excel file
- Next by thread: Re: parsing a large excel file
- Index(es):
Relevant Pages
|
