Re: parsing a large excel file
ANJAN PURKAYASTHA schreef: i have installed Spreadshee::ParseExcel to parse some large excel data files. Here is the problem I'm facing. I need to parse data from columns M to P and rows 10 to 43000. Now I know that there is a PrintArea method that can print an area of a worksheet specified in (start row, start col, end row, end col). However I'm having difficulty in specifying these parameters correctly for the PrintArea method. I'm also not sure what the output is going to look like. does anyone in this forum have any pointers? To me it is not clear what you want. First you state that you want to parse M10..P43000, then you state that you want to do something with the PrintArea. What is it that you try to do, two things, or one? Do you want to import the data from the first(!) range that is in the current PrintArea? Or is the PrintArea just something you found out about, and that you think you need but that you actually don't? -- Affijn, Ruud Gewoon is een tijger. -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/
RE: parsing a large excel file
-Original Message- From: ANJAN PURKAYASTHA [mailto:[EMAIL PROTECTED] Sent: 22 July 2008 02:41 To: beginners@perl.org Subject: parsing a large excel file to all, i have installed Spreadshee::ParseExcel to parse some large excel data files. Here is the problem I'm facing. I need to parse data from columns M to P and rows 10 to 43000. Now I know that there is a PrintArea method that can print an area of a worksheet specified in (start row, start col, end row, end col). However I'm having difficulty in specifying these parameters correctly for the PrintArea method. I'm also not sure what the output is going to look like. does anyone in this forum have any pointers? all advice will be appreciated. tia, anjan I have only used the Simple version previously to parse and entire row but the method Cell ( ROW, COL ) In the docs for the module you showed suggests you can get at the data directly. Stu 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. -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/
RE: parsing a large excel file
-Original Message-
From: Stewart Anderson
Sent: 22 July 2008 09:34
To: ANJAN PURKAYASTHA; beginners@perl.org
Cc: Stewart Anderson
Subject: RE: parsing a large excel file
-Original Message-
From: ANJAN PURKAYASTHA [mailto:[EMAIL PROTECTED]
Sent: 22 July 2008 02:41
To: beginners@perl.org
Subject: parsing a large excel file
to all,
i have installed Spreadshee::ParseExcel to parse some large excel
data
files.
Here is the problem I'm facing. I need to parse data from columns M
to P
and
rows 10 to 43000. Now I know that there is a PrintArea method that
can
print
an area of a worksheet specified in (start row, start col, end row,
end
col). However I'm having difficulty in specifying these parameters
correctly
for the PrintArea method. I'm also not sure what the output is going
to
look
like.
does anyone in this forum have any pointers?
all advice will be appreciated.
tia,
anjan
I have only used the Simple version previously to parse and entire
row
but the method Cell ( ROW, COL ) In the docs for the module you
showed suggests you can get at the data directly.
Stu
Maybe I spoke to soon, that method seem to return the iobject, not
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.
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
fetching to docs using wget
Hi,
I am trying to fetch various docs from server using wget. Below is the code
Files are downloading but of 0 size :(
Am i missing any thing
[EMAIL PROTECTED]:~/adave cat fetch.pl
open(S,./source.txt) || die Failed to open file 'source.txt' $!;
while(S)
{
if (/http/)
{
@a=split(/\//,$_);
$file=$a[-1];
$r=exec `/usr/bin/wget $_ ./$file `;
print $r;
print Printing the output of last $?;
exit;
}
}
close(S);
[EMAIL PROTECTED]:~/adave perl fetch.pl
--18:03:19-- http://localhost/training_files/docs/overview.ppt
= `overview.ppt'
Resolving localhost... x.x.x.x.
Connecting to localhost x.x.x.x... connected.
HTTP request sent, awaiting response... 200 OK
Length: 145,920 [application/vnd.ms-powerpoint]
100%[==]
145,920 81.61K/s
18:03:21 (81.43 KB/s) - `overview.ppt' saved [145920/145920]
0Printing the output of last [EMAIL PROTECTED]:~/adave ls -l
total 5
-rw-r--r-- 1 adave aix 254 2008-07-22 17:48 fetch.pl
-rw-r--r-- 1 adave aix 4074 2008-07-22 16:52 source.txt
-rw-r--r-- 1 adave aix0 2008-07-22 18:03 overview.ppt
Thanks,
a b
Re: fetching to docs using wget
a b wrote:
I am trying to fetch various docs from server using wget. Below is the code
Files are downloading but of 0 size :(
Am i missing any thing
[EMAIL PROTECTED]:~/adave cat fetch.pl
open(S,./source.txt) || die Failed to open file 'source.txt' $!;
while(S)
{
if (/http/)
{
@a=split(/\//,$_);
$file=$a[-1];
$r=exec `/usr/bin/wget $_ ./$file `;
print $r;
print Printing the output of last $?;
exit;
}
}
close(S);
[EMAIL PROTECTED]:~/adave perl fetch.pl
--18:03:19-- http://localhost/training_files/docs/overview.ppt
= `overview.ppt'
Resolving localhost... x.x.x.x.
Connecting to localhost x.x.x.x... connected.
HTTP request sent, awaiting response... 200 OK
Length: 145,920 [application/vnd.ms-powerpoint]
100%[==]
145,920 81.61K/s
18:03:21 (81.43 KB/s) - `overview.ppt' saved [145920/145920]
0Printing the output of last [EMAIL PROTECTED]:~/adave ls -l
total 5
-rw-r--r-- 1 adave aix 254 2008-07-22 17:48 fetch.pl
-rw-r--r-- 1 adave aix 4074 2008-07-22 16:52 source.txt
-rw-r--r-- 1 adave aix0 2008-07-22 18:03 overview.ppt
Thanks,
a b
The URLs from your file still have newlines at the end, so you are issuing two
commands:
/usr/bin/wget $_
./$file
As far as I know wget doesn't write its output to stdout anyway, so it should
work as it is without the output redirection.
Why not do the whole thing in native Perl anyway, as below.
HTH,
Rob
use strict;
use warnings;
use LWP::Simple;
open my $fh, '', './source.txt' or die Failed to open file 'source.txt' $!;
while ($fh) {
chomp;
next unless /^http:/;
my $file = (split m|/|)[-1];
my $resp = getstore($_, $file);
print $file - $resp\n;
}
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
Re: parsing a large excel file
thanks for your helpful responses.
Dr Ruud my aim was to parse the text area M10..P43000 and then print it
using PrintArea. Maybe I have misunderstood the function of PrintArea.
Stewart, many thanks for the suggestion. I will indeed try it out and see
what happens.
Anjan
On Tue, Jul 22, 2008 at 5:11 AM, Stewart Anderson
[EMAIL PROTECTED] wrote:
-Original Message-
From: Stewart Anderson
Sent: 22 July 2008 09:34
To: ANJAN PURKAYASTHA; beginners@perl.org
Cc: Stewart Anderson
Subject: RE: parsing a large excel file
-Original Message-
From: ANJAN PURKAYASTHA [mailto:[EMAIL PROTECTED]
Sent: 22 July 2008 02:41
To: beginners@perl.org
Subject: parsing a large excel file
to all,
i have installed Spreadshee::ParseExcel to parse some large excel
data
files.
Here is the problem I'm facing. I need to parse data from columns M
to P
and
rows 10 to 43000. Now I know that there is a PrintArea method that
can
print
an area of a worksheet specified in (start row, start col, end row,
end
col). However I'm having difficulty in specifying these parameters
correctly
for the PrintArea method. I'm also not sure what the output is going
to
look
like.
does anyone in this forum have any pointers?
all advice will be appreciated.
tia,
anjan
I have only used the Simple version previously to parse and entire
row
but the method Cell ( ROW, COL ) In the docs for the module you
showed suggests you can get at the data directly.
Stu
Maybe I spoke to soon, that method seem to return the iobject, not
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.
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
--
=
anjan purkayastha
bioinformatics analyst
whitehead institute for biomedical research
nine cambridge center
cambridge, ma 02142
purkayas [at] wi [dot] mit [dot] edu
703.740.6939
Re: DBI error
Panda-X wrote:
I've got this error, anything I can do ?
Can't locate auto/DBI/data_source.al in @INC
Code :
use DBI;
my @dataSource = DBI - data_source ( mysql ) ;
The method call is
DBI-data_sources('mysql');
and if I change the as this :
use DBI;
my $dbh = DBI - connect ( dbi:mysql, $adm, $pass ) ;
the data source should look like
dbi:drivername:database name
so
dbi:mysql:database name
in your case.
[snip]
HTH,
Rob
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
Re: parsing a large excel file
ANJAN PURKAYASTHA wrote:
i have installed Spreadshee::ParseExcel to parse some large excel data
files.
Here is the problem I'm facing. I need to parse data from columns M to P and
rows 10 to 43000. Now I know that there is a PrintArea method that can print
an area of a worksheet specified in (start row, start col, end row, end
col). However I'm having difficulty in specifying these parameters correctly
for the PrintArea method. I'm also not sure what the output is going to look
like.
I found both the code and the documentation of this module very difficult to
understand. However I have an answer for you.
If you read the documentation carefully it says Workbook class has these
properties: before the list that includes PrintArea. What the author means by
properties is hash elements, so you can access the array of print areas for the
workbook by
my $areas = $book-{PrintArea};
and the print areas for the first worksheet (did anyone else know an Excel
worksheet could have multiple print areas?!) can be displayed by
print @$_\n foreach @{$areas-[0]};
But it has a bug whereby the hash element sometimes doesn't appear at all. I
sometimes had to ad a second sheet and give that a print area to make the
attribute appear.
However, if you remember that when the documentation refers to a 'property' it
means a hash element it will make much more sense.
HTH,
Rob
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
Having trouble with cpan
Hi All, I am having trouble with CPAN to update my perl packages. Here is the error: [EMAIL PROTECTED] ~]# cpan cpan shell -- CPAN exploration and modules installation (v1.7602) ReadLine support enabled cpan install String::scanf CPAN: Storable loaded ok Going to read /home/barryb/.cpan/Metadata Database was generated on Thu, 12 Jun 2008 12:06:48 GMT Going to read /home/barryb/.cpan/sources/authors/01mailrc.txt.gz CPAN: Compress::Zlib loaded ok Can't call method value on an undefined value at /usr/lib/perl5/vendor_perl/5.8.8/IO/Uncompress/RawInflate.pm line 64. cpan Has anyone seen this or have an idea for how to solve it. Barry
Re: Howto Pull files from two diff machines
Hiya Guys You guys have been previously helping me with a script. Debug Please Was the title. It's working as far as we can tell. But I need to be able to ftp to two machines and pull down xferlog Therefore each xferlog would be downloaded as xferlog.host.month.log Then either joined as one and then processed ? Now sure , What would be the best way to go about that ? Any directions would be appreciated. I would post the code, but I am not sure you guys would need a whole code -Ty -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/
how to reinvent tar xvfz with perl?
I need to automate some stuff and decided to use perl. I have cygwin perl and activestate perl. Below is my attempt. It does not work because I don't have perlIO::gzip installed. When I tried /bin/Perl -MCPAN -e 'install PerlIO::gzip' The tests fail and it won't install. When I try activestate perl's ppm.bat I don't see an any modules when I search for gzip (except for one called CGI-WebGzip which did not look like what I wanted). Can someone help me reinvent tar xvfz? Thanks! Siegfried #!c:/perl/bin/perl # # $Log$ # # Begin commands to execute this file using Perl with bash # ./untar.pl ..\\Archive-Tar-1.38.tar.gz ..\\Archive-Tar-1.38.tar # End commands to execute this file using Perl with bash # use strict; use warnings; use Archive::Tar; use PerlIO::gzip; my $zipName = shift(@ARGV); open FOO, :gzip, $zipName or die $!; print while FOO; # And it will be uncompressed... close FOO; $zipName = ..\\Archive-Tar-1.38.tar; my $tar = Archive::Tar-new; print untar $zipName\n; $tar-read($zipName,1); $tar-extract(); -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/
Re: how to reinvent tar xvfz with perl?
Siegfried Heintze (Aditi) wrote: I need to automate some stuff and decided to use perl. I have cygwin perl and activestate perl. Below is my attempt. It does not work because I don't have perlIO::gzip installed. When I tried /bin/Perl -MCPAN -e 'install PerlIO::gzip' The tests fail and it won't install. When I try activestate perl's ppm.bat I don't see an any modules when I search for gzip (except for one called CGI-WebGzip which did not look like what I wanted). Can someone help me reinvent tar xvfz? Run ppm at the command prompt and do rep tcool http://ppm.tcool.org/server/ppmserver.cgi?urn:PPMServer and rep up tcool That will give you a new repository with many useful modules. And it has PerlIO::gzip. HTH, Rob -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/
Re: DBI error
Ah! Yes!! Thank you very much!!
Problem fixed now =)
2008/7/22 Rob Dixon [EMAIL PROTECTED]:
Panda-X wrote:
I've got this error, anything I can do ?
Can't locate auto/DBI/data_source.al in @INC
Code :
use DBI;
my @dataSource = DBI - data_source ( mysql ) ;
The method call is
DBI-data_sources('mysql');
and if I change the as this :
use DBI;
my $dbh = DBI - connect ( dbi:mysql, $adm, $pass ) ;
the data source should look like
dbi:drivername:database name
so
dbi:mysql:database name
in your case.
[snip]
HTH,
Rob
Re: Having trouble with cpan
On Tue, Jul 22, 2008 at 10:53 AM, Barry Benowitz [EMAIL PROTECTED] wrote: cpan install String::scanf Shouldn't that be String::Scanf? Capitalization can be important; if not here, certainly when you use the module. Can't call method value on an undefined value at /usr/lib/perl5/vendor_perl/5.8.8/IO/Uncompress/RawInflate.pm line 64. That seems quite odd. If you repeatedly get that error, I'm guessing that maybe something (the CPAN authors file?) didn't download correctly. I recommend you discard the contents of your CPAN sources directory, since that's just a download cache, and try again. The sources directory is found within your CPAN home directory, which is often named .cpan in your home directory. Of course, if you can't install that module in the automated way, you can try installing without the CPAN shell like us old timers used to do. Somebody around here probably still remembers how to run a Makefile.PL from the command line Or you can type perldoc perlmodinstall at the command line and read about it. Good luck with it! --Tom Phoenix Stonehenge Perl Training -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/
