[Issue] Spreadsheet with data from internet
[Issue] Spreadsheet with data from internet
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).
Reason: tagged [Issue] (link to a bug report).
OOo 3.4.X on Ms Windows XP + win2kpro
Re: Spreadsheet with data from internet
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Spreadsheet with data from internet
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?
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
Re: Spreadsheet with data from internet
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Spreadsheet with data from internet
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
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
Re: Spreadsheet with data from internet
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.
I copy the link 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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Spreadsheet with data from internet
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.
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
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: Spreadsheet with data from internet
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.
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
Windows XP
Re: Spreadsheet with data from internet
I see the same problem with the same ISP when doing the same on a Windows box. Other sites import well.Charlie Young wrote:I think it must be something particular to the census site interacting with ones local ISP.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: Spreadsheet with data from internet
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.Villeroy wrote:I see the same problem with the same ISP when doing the same on a Windows box. Other sites import well.Charlie Young wrote:I think it must be something particular to the census site interacting with ones local ISP.
Apache OpenOffice 4.1.1
Windows XP
Windows XP
Re: Spreadsheet with data from internet
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
- lino
- Volunteer
- Posts: 312
- Joined: Thu Mar 28, 2013 11:16 am
- Location: http://goo.gl/maps/rsfPZ
Re: Spreadsheet with data from internet
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/
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
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.
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
Re: Spreadsheet with data from internet
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
Re: Spreadsheet with data from internet
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
Re: Spreadsheet with data from internet
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
Re: Spreadsheet with data from internet
Thanks for the heads up, Rory.
OOo 3.4.X on Ms Windows XP + win2kpro
- lino
- Volunteer
- Posts: 312
- Joined: Thu Mar 28, 2013 11:16 am
- Location: http://goo.gl/maps/rsfPZ
Re: Spreadsheet with data from internet
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
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.
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