SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2013
    Location
    UK
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How to read XLS file from another website?

    I'm working on a script for reading in the positions (Lat-Long) of a load of sea-marks and saving them as a GPX file.
    The data is published as an XLS file on the ABP Humber website and free to download for everyone. The URL for the file is:-
    http://www.humber.com/admin/content/...ge/buoyage.xls
    To read the XLS file into PHP I used this 'xlsreadphp' script that I found.
    I have my script (which includes the xlsreadphp script) workling, and it writes a GPX file that can be loaded into a GPS, Mobile Nav App, Google Earth or whatever you want to view the data in.
    The thing is it works if I download the XLS file to my computer, then upload it to my webserver, so my script reads it localy. So the line in the script that points to the file is:-
    PHP Code:
    $data->read('buoyage.xls'); 
    That works fine, but what would be better is if It read the file directly from ABP Humber's site, so I don't have to first download, then upload the file manually before running the script.
    So I tried:-
    PHP Code:
    $fpath 'http://www.humber.com/admin/content/files/Estuary Information/Buoyage and Beaconage/buoyage.xls' ;
    $data->read($fpath); 
    But it get the message:-
    Code:
    The filename http://www.humber.com/admin/content/files/Estuary Information/Buoyage and Beaconage/buoyage.xls is not readable
    Why is that?
    Maybe there is a way to make the script download the file to it's local folder first, is that the way?

  2. #2
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    16,460
    Mentioned
    160 Post(s)
    Tagged
    1 Thread(s)
    Check your host's php.ini file for the allow_url_fopen setting.

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2013
    Location
    UK
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    Check your host's php.ini file for the allow_url_fopen setting.
    I found that in the php.ini, but it is set to 'On' already.
    PHP Code:
    ;;;;;;;;;;;;;;;;;;
    Fopen wrappers ;
    ;;;;;;;;;;;;;;;;;;

    Whether to allow the treatment of URLs (like http:// or ftp://) as files.
    http://php.net/allow-url-fopen
    allow_url_fopen On 

  4. #4
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,258
    Mentioned
    32 Post(s)
    Tagged
    5 Thread(s)
    Hi Sam,

    Quote Originally Posted by SamA74 View Post
    PHP Code:
    $fpath 'http://www.humber.com/admin/content/files/Estuary Information/Buoyage and Beaconage/buoyage.xls' ;
    $data->read($fpath); 
    But it get the message:-
    Code:
    The filename http://www.humber.com/admin/content/files/Estuary Information/Buoyage and Beaconage/buoyage.xls is not readable
    Why is that?
    Maybe there is a way to make the script download the file to it's local folder first, is that the way?
    The reason for the error is that the xlsreadphp script does an is_readable() check before trying to open the file, and that function doesn't work with HTTP URLs. As you suggest, downloading the file to your server before opening would solve the problem. You could do something like this (assuming that temp is a writable directory on your server):

    PHP Code:
    $url 'http://www.humber.com/admin/content/files/Estuary Information/Buoyage and Beaconage/buoyage.xls' ;
    $dest 'temp/buoyage.xls';

    $raw file_get_contents($url);
    file_put_contents($dest$raw);

    $data->read($dest); 

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2013
    Location
    UK
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another step closer, I thought downloading a local copy would help, but did not know how.
    I tried your code, the file 'buoyage.xls' appears in the temp folder, but I still get the message 'The filename buoyage.xls is not readable' when I run the script.
    I was a bit puzzled by this, but when I look at the xls file it is zero bytes in size.
    Any clues?

  6. #6
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,258
    Mentioned
    32 Post(s)
    Tagged
    5 Thread(s)
    The problem seems to be the spaces in the URL, try changing this line:
    PHP Code:
    $raw file_get_contents(str_replace(" ""%20"$url); 

  7. #7
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    Or just urlencode() the string entirely.

    Standard disclaimer: Do not use this code on a site to which you do not have permission to use the data. A quick look at the site mentioned above indicates that they allow some exerpt-pulling for personal use, but no commercialization, and require acknowledgement of authorship to be displayed.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  8. #8
    SitePoint Enthusiast
    Join Date
    Sep 2013
    Location
    UK
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, that works now, many thanks.

    Yes, it's so easy to overlook the legal stuff. I thought since the data is publicly downloadable it would be OK, though I have had a look at the site Terms of use now. It does say:-
    You must not modify the paper or digital copies of any materials you have printed off or downloaded in any way, and you must not use any illustrations, photographs, video or audio sequences or any graphics separately from any accompanying text.
    You could say I was modifying downloaded data, but in reality the script will just do what I previously had to do manualy within a mapping program, checking which buoys have been moved, and updating the coords. As you can imagine, this is very tedious work for a human when you consider there are about 130 of them and changes are frequent.
    At the end of the day, they publish this data for a reason, so people can safley navigate the estuary, they want people to know and use this info. And to make use of it generaly involves inputting the data to a chart or device. This is just a smarter way of doing it, since I never heard of a GPS or plotter that reads MS Office files, would you trust it?
    For now the script is only experimental and for personal use. I did think of sharing the data in it's more useful form, but maybe that would open up another legal can of worms, given the nature of it's use.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •