Page 1 of 1

[Issue] Spreadsheet with data from internet

PostPosted: Wed Jun 19, 2013 10:03 pm
by MikeCPA
Is it possible to have a CALC spreadsheet that holds data and updates from a website on the internet?

Re: Spreadsheet with data from internet

PostPosted: Wed Jun 19, 2013 10:12 pm
by FJCC
See my post here. The part you want starts with "Assuming that one thing you want to do you is link to a data table on the internet,..."

Re: Spreadsheet with data from internet

PostPosted: Thu Jun 20, 2013 12:49 am
by MikeCPA
As a test, I followed your example using your instructions and the same link, "http://quickfacts.census.gov/qfd/states/01000.html," in a new file, and I get an error dialogue and the OK button remains grayed out:
Error loading document http://quickfacts.census.gov/qfd/states/01000.html:
Error reading from the internet
Server error message: .

Any ideas?

Re: Spreadsheet with data from internet

PostPosted: Thu Jun 20, 2013 3:53 am
by FJCC
I just tried it using versions 3.3 and 3.4.1 and it works in both. At what step exactly do you get the error message? Not that I'll know what to do about it, but perhaps someone else will.

Re: Spreadsheet with data from internet

PostPosted: Thu Jun 20, 2013 5:53 am
by MikeCPA
Open new spreadsheet file, go to INSERT/LINK TO EXTERNAL DATA, paste in your link into External Data URL field, http://quickfacts.census.gov/qfd/states/01000.html, hit enter and get the error (OK button never becomes available, always grayed out).

And I can get to the link URL with my browser, so my ISP connection is OK

Re: Spreadsheet with data from internet

PostPosted: Thu Jun 20, 2013 7:58 am
by Villeroy
census.gov.ods
http://quickfacts.census.gov/qfd/states/01000.html
(13.76 KiB) Downloaded 97 times


I copy the link http://quickfacts.census.gov/qfd/states/01000.html, hit Enter, wait a moment, choose "html tables", hit OK
On the next dialog I choose import language "English(US)" (because I expect numeric data in US notation) and check the option to interprete "special numbers" (such as m/d/y dates).
Works for me.

Re: Spreadsheet with data from internet

PostPosted: Thu Jun 20, 2013 8:53 am
by RoryOF
I suspect that somewhere in the setup for Calc/OpenOffice there are settings which permit/prevent such external links.

On an "out of the box" OpenOffice install I have tried Villeroy's file above and it opens showing Alabama data, but does not update (Calc gives message "The link could not be updated"). The link site will open correctly in a browser. I have no firewall preventing access on this machine.

If I attempt to insert "Link to External Data" in Calc by entering the link address, nothing further happens - the OK button remains greyed out, and there are no range options offered.

I'll be off line for some hours, but on return will willingly try any configuration changes suggested in an attempt to get the necessary settings tweaked correctly.

Re: Spreadsheet with data from internet

PostPosted: Thu Jun 20, 2013 10:40 am
by Charlie Young
I think it must be something particular to the census site interacting with ones local ISP. I can connect to the site with a browser, but I cannot "Link to External Data" through it (it gives me the same kind of server error message Mike indicated), and I cannot ping it. I do use "Link to External Data" with other sites and it works fine. MikeCPA should try some site he is specifically interested in.

The census site provides download links at the bottom. If I use the link for "Download these tables - delimited"

http://quickfacts.census.gov/cgi-bin/qfd/extract?01000

I can get the data through Insert > Sheet From File.

Re: Spreadsheet with data from internet

PostPosted: Thu Jun 20, 2013 10:53 am
by Villeroy
Charlie Young wrote:I think it must be something particular to the census site interacting with ones local ISP.

I see the same problem with the same ISP when doing the same on a Windows box. Other sites import well.

Re: Spreadsheet with data from internet

PostPosted: Thu Jun 20, 2013 11:11 am
by Charlie Young
Villeroy wrote:
Charlie Young wrote:I think it must be something particular to the census site interacting with ones local ISP.

I see the same problem with the same ISP when doing the same on a Windows box. Other sites import well.


Then it must be something particular to Windows with that site, yes. If I try https instead of http I get warnings about security certificates which I am not tempted to pursue further.

Re: Spreadsheet with data from internet

PostPosted: Thu Jun 20, 2013 1:11 pm
by RoryOF
I was using Xubuntu 12.04 and 12.10 and getting no OpenOffice access. Browser access (Firefox) is OK to that site.

Re: Spreadsheet with data from internet

PostPosted: Sat Jun 22, 2013 9:31 am
by lino
It seems that the problem is server denies access to some user agents (browsers are ok but all other access is denied).

If you insist on this data, you can try to wget the page to the local machine and read it from there. If so, you have to fake user agent (declare some browser) and refresh local copy from time to time.

For details check http://www.gnu.org/software/wget/

Re: Spreadsheet with data from internet

PostPosted: Sat Jun 22, 2013 9:44 am
by RoryOF
I've moved away from this problem (real life pressures) but I suspect it is not to do with the servers granting/denying access to some user agents, unless there is some "trap" on the Internet connection chain in use (which I think unlikely). I'll try in Brittany and Paris if I get connectivity (not a priority - I want to get on with my writing and don't need/want connectivity).

I think it is more likely some setting in OpenOffice to do with data base connectivity. I instance that it worked for Villeroy using Xubuntu but did not work for me using Xubuntu(s). My OpenOffice installs were "out of the box", whereas I am (almost) certain that Villeroy's have been tweaked for various database/spreadsheet connectivities. I do very little spreadsheet work - simple lists and additions, and no database work, so my OpenOffice(s) are not tuned for these.

I'm busy this coming week - off on holiday at week end (hurrah!) and will be away for two weeks, so won't get back to this for nearly a month. If someone is minded to experiment with the settings, I look forward to seeing a solution.

 Edit: On my return I can set up a spare machine to have an "out of the box" operating system install and an "out of the box" OpenOffice install, and apply any suggested tweaks until we get this problem sorted; it won't be the fastest machine in the world, but if it sorts out the problem, who cares! 


 Edit: Entering the test URL my OpenOffice(s) do not touch the Internet (no flash on local routers). This would suggest strongly that the problem is to do with OpenOffice settings. I normally don't set up an Internet Proxy or connection in OpenOffice /Tools /Options/ Internet, but even doing that makes no difference 

Re: Spreadsheet with data from internet

PostPosted: Sat Jun 22, 2013 6:57 pm
by RoryOF
I've had a little fiddle with this. Nothing from the Xubuntu version of OpenOffice (v4.0 dev), but v4.0 dev on XP SP2 blows up with an error in msvcr90.dll [it nearly works!]. As there are 12 msvcr90.dll files on the computer, and more of each of mscvr71/80/100, I think some tidying is in order (but not immediately).

Re: Spreadsheet with data from internet

PostPosted: Sat Jun 22, 2013 11:05 pm
by MikeCPA
This was a learning curve project in preparation for a somewhat ambitious (for me) statistical analysis project we have (had) in mind. It seems I'm not the only one stymied by this inexplicable behavior which might be explained in the next revision, if not fixed. It seems it either works, or does not, and no one knows why. Time permitting, I may take another stab at this, but perhaps a developer who knows the code might chime in here. Thanks everyone!

Re: Spreadsheet with data from internet

PostPosted: Sat Jun 22, 2013 11:20 pm
by RoryOF
It is a case of careful analysis of just what is (not) happening. No time to do that for at least three weeks. Both Villeroy and I are running on identical operating systems (Xubuntu); his works, mine doesn't, but at least we have two experienced computer users who can compare notes; it will be at least mid July before I get back to this. It is to be hoped that Villeroy will not be away then or there will be further delay.

Re: Spreadsheet with data from internet

PostPosted: Sun Jun 23, 2013 12:35 am
by MikeCPA
Thanks for the heads up, Rory.

Re: Spreadsheet with data from internet

PostPosted: Sun Jun 23, 2013 10:55 am
by lino
You were right, that's OO making troubles. Just for the record, once I managed to hit enter after entering the address instead of just moving focus and waiting, LO did the job.

Re: Spreadsheet with data from internet

PostPosted: Sun Jun 23, 2013 11:12 am
by RoryOF
My previous experience with this from the very little I have used it, suggests that it is sensitive to the installation. But in my current case I am using the dev version of AOO 4.0, so it is not fair to judge much from that. In comment 3 on bugzilla report
https://issues.apache.org/ooo/show_bug.cgi?id=107368
Hanya reports success using AOO 4.0 on Xubuntu 12.04. I didn't get it to work.

As for LibreOffice/OpenOffice - I don't want to get into a slanging match. Each to his own preference; my current major task is writing and I'm happy to use AOO 3.41 or 4.00, which I find quite stable for my purposes.