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

Hi!

AFAIK, ReadData or new methods of Spreadsheet::Read module reads a the whole data into memory by parsing the source file with the proper filter. Then, you can access sheets, rows, columns, ...

I wrote a script to extract some info from many sheets from an ODS file. This file has to be downloaded from the net, but I want to skip this manually done step and to include that step into my script.

Then, I have two choices:

  1. Include some code to download the data in ODS format with LWP or such into a temp file and then use it.
  2. Make Spreadsheet::Read read the file directly from the web service.

As I'd like to try a direct access from this module to the service, and I gave a try using the HTTPS URL instead of a local file name, but it just gave me an empty object ready to receive sheets from other files, and no error.

The manual says it can open a file stream, but I cannot figure out how to set up that stream. Also, the used module Spreadsheet::ReadSXC does not mention streams in its doc, so I'm not sure that this will work.

Any idea, experience, permalink, hint, etc?

Thanks a lot!

  • Comment on Spreadsheet::Read Open File from the cloud

Replies are listed 'Best First'.
Re: Spreadsheet::Read Open File from the cloud
by Corion (Patriarch) on Sep 30, 2019 at 19:40 UTC

    You will have to perform the additional step of downloading the file first. This is easy using HTTP::Tiny or LWP::UserAgent:

    #!perl
use strict;
use warnings;
use HTTP::Tiny;
use Spreadsheet::Read;

# Fetch data and return a filehandle to that data
sub fetch_url {
    my( $url ) = @_;
    my $ua = HTTP::Tiny->new;
    my $res = $ua->get( $url );
    open my $fh, '<', \$res->{content};
    return $fh
}

my $fh = fetch_url('http://example.com/example.ods');
my $sheet = Spreadsheet::Read->new( $fh, parser => 'ods' );

    [download]
[reply]
[d/l]

      Thanks, it is a very nice piece of code, but it failed as I suspected, because referenced module Spreadsheet::ReadSXC does not support streams. I got this error:

      Sorry, references as input are not (yet) supported by Spreadsheet::ReadSXC at C:\Temp\read-ods.pl line 22.

      Anyway, I could save to a temp file (actually mirrored) and then used it as it previously did.

[reply]
[d/l]

        The underlying parser may require a path to an actual file (as opposed to a file handle). parser => 'ods' is such a parser. So create one!

[reply]
[d/l]
Re: Spreadsheet::Read Open File from the cloud
by shadowsong (Pilgrim) on Oct 01, 2019 at 14:08 UTC

    Hi vitoco

    I think you might have gotten the short end of the stick from the docs.

    The manual says it can open a file stream, but I cannot figure out how to set up that stream



    Not so fast... It it says:

    Processing Excel data from a stream or content is supported through a File::Temp temporary file or IO::Scalar when available.

    Looks like Corion has the right approach.

    Good luck,
    Shadow

[reply]

Back to Seekers of Perl Wisdom