[Issue] Spreadsheet with data from internet

Discuss the spreadsheet application
Post Reply
MikeCPA
Posts: 93
Joined: Sat Aug 30, 2008 5:26 am

[Issue] Spreadsheet with data from internet

Post by MikeCPA »

Is it possible to have a CALC spreadsheet that holds data and updates from a website on the internet?
Last edited by Hagar Delest on Tue Aug 06, 2013 9:06 pm, edited 1 time in total.
Reason: tagged [Issue] (link to a bug report).
OOo 3.4.X on Ms Windows XP + win2kpro
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Spreadsheet with data from internet

Post 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,..."
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
MikeCPA
Posts: 93
Joined: Sat Aug 30, 2008 5:26 am

Re: Spreadsheet with data from internet

Post 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?
OOo 3.4.X on Ms Windows XP + win2kpro
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Spreadsheet with data from internet

Post 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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
MikeCPA
Posts: 93
Joined: Sat Aug 30, 2008 5:26 am

Re: Spreadsheet with data from internet

Post 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
OOo 3.4.X on Ms Windows XP + win2kpro
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Spreadsheet with data from internet

Post by Villeroy »

census.gov.ods
http://quickfacts.census.gov/qfd/states/01000.html
(13.76 KiB) Downloaded 146 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.
Last edited by Villeroy on Thu Jun 20, 2013 11:28 am, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Spreadsheet with data from internet

Post 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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Spreadsheet with data from internet

Post 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.
Apache OpenOffice 4.1.1
Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Spreadsheet with data from internet

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Spreadsheet with data from internet

Post 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.
Apache OpenOffice 4.1.1
Windows XP
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Spreadsheet with data from internet

Post by RoryOF »

I was using Xubuntu 12.04 and 12.10 and getting no OpenOffice access. Browser access (Firefox) is OK to that site.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
lino
Volunteer
Posts: 312
Joined: Thu Mar 28, 2013 11:16 am
Location: http://goo.gl/maps/rsfPZ

Re: Spreadsheet with data from internet

Post 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/
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Spreadsheet with data from internet

Post 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 
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Spreadsheet with data from internet

Post 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).
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
MikeCPA
Posts: 93
Joined: Sat Aug 30, 2008 5:26 am

Re: Spreadsheet with data from internet

Post 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!
OOo 3.4.X on Ms Windows XP + win2kpro
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Spreadsheet with data from internet

Post 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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
MikeCPA
Posts: 93
Joined: Sat Aug 30, 2008 5:26 am

Re: Spreadsheet with data from internet

Post by MikeCPA »

Thanks for the heads up, Rory.
OOo 3.4.X on Ms Windows XP + win2kpro
User avatar
lino
Volunteer
Posts: 312
Joined: Thu Mar 28, 2013 11:16 am
Location: http://goo.gl/maps/rsfPZ

Re: Spreadsheet with data from internet

Post 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.
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Spreadsheet with data from internet

Post 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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Post Reply