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

I've pulled an Excel spreadsheet off a website using the following snippet: 

my $EXCEL = new Spreadsheet::ParseExcel;
my $URL = "http://www.sysco.com/supplier/DUNS%20Listing.xls";
my $ua = LWP::UserAgent->new();
my $req = HTTP::Request->new(GET => $URL);
my $response = $ua->request($req);
if ( $response->is_error() )
   { printf " %s\n", $response->status_line; }
else
   {
   my $CONTENT = $response->content();<br>
   my $BYTES = length $CONTENT;<br>
   print "$URL successfully retrieved! [$BYTES] bytes...\n";
   }

[download]
Which seems to work just fine, I get 9000+ bytes back, and $CONTENT is definitely binary.

Now, the problem.
I'm trying to parse $CONTENT, using the Spreadsheet::Excel module, as I need to get to a couple of the columns in the xls file.

How do I do this? I've never dealt with writing binary files, and can't seem to trick the $EXCEL->Parse('some_xls_file') statement into using $CONTENT.

What's the best way to accomplish this?
Any help is appreciated.

Edit, BazB: added code tags, reformatted slightly.

Replies are listed 'Best First'.
Re: HTTP::Response and Spreadsheet::ParseExcel
by dragonchild (Archbishop) on Oct 15, 2003 at 20:08 UTC
    Write it to a tempfile using File::Temp and go from there?

    ------
    We are the carpenters and bricklayers of the Information Age.

    The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

    ... strings and arrays will suffice. As they are easily available as native data types in any sane language, ... - blokhead, speaking on evolutionary algorithms

    Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

[reply]
Re: HTTP::Response and Spreadsheet::ParseExcel
by snax (Hermit) on Oct 15, 2003 at 20:28 UTC
    Look at the source; it seems to me that if you feed Spreadsheet::ParseExcel a reference to a scalar it assumes you've already slurped in the file. So just pass in a ref: \$CONTENT and see if that flies.
[reply]
[d/l]
[select]
      Passing ParseExcel(\$CONTENT) results in an out of memory error...

      How do I read a binary and write it to file? syswrite doesn't seem to fly, using :

      open FH, "<$temp" || die "Can't open $temp : $!\n";
      syswrite (FH, $CONTENT, length $CONTENT);

      (Remember, $CONTENT is the binary xls...)
      Argggh!

[reply]

        You are opening $temp for reading, not writing. Should be: 

        open FH, ">$temp" or die "Can't create $temp - $!\n";

        [download]
        -- vek --
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom