How to read XLS file from another website?

PHP
1

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/files/Estuary%20Information/Buoyage%20and%20Beaconage/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:-

$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:-

$fpath = 'http://www.humber.com/admin/content/files/Estuary Information/Buoyage and Beaconage/buoyage.xls' ;
$data->read($fpath);

But it get the message:-

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

Check your host’s php.ini file for the allow_url_fopen setting.

3

I found that in the php.ini, but it is set to ‘On’ already.

;;;;;;;;;;;;;;;;;;
; 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

Hi Sam,

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):


$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

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

The problem seems to be the spaces in the URL, try changing this line:

$raw = file_get_contents(str_replace(" ", "%20", $url);
7

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.

8

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? :slight_smile:
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.