Importing data into Calc from the web?

LuciliaePrincess · August 24, 2019, 11:29pm

Hello,

I have managed to do this with both Microsoft Excel and OpenOffice, but since I have migrated to LibreOffice I have been unable to get it to work. I am trying to import a webpage into calc. I have tried using the Link to External Data, Insert Sheet from File, and Open functions, but none seem to be able to import the data. I had a little trouble when trying to get OpenOffice to work, and was told the webpage I am trying to import is a CSV type and that Link to External Data does not work properly with CSV data and was told to use the Insert Sheet from File, which worked perfectly.

https://secure.runescape.com/m=hiscore_oldschool_hardcore_ironman/index_lite.ws?player=luciliaegirl

There is a link to the webpage I am trying to import. Every time I try to import it I just get an “Error reading data from the Internet. Server error message: 502 Bad Gateway.”. I would really appreciate any help on getting this working, as I am stumped as of right now.

ve3oat · August 24, 2019, 11:55pm

The data in the link you gave us seems to start out as three columns of numbers per row, but toward the end it becomes only two columns per row. You will have to figure out what is missing, or ignore that part of the data.

It is actually not a lot of data. Have you considered copying and pasting it into a text editor and formatting it manually into a proper csv file? It is probably easier than you think.

x1,x2,x3
x4,x5,x6
x7,         etc

Then save it with a .csv file extension instead of the default .txt. Copy that into Calc to use in the spreadsheet.

LuciliaePrincess · August 25, 2019, 12:07am

The data is from a game, the first number being the rank, then the level, then the xp, and towards the end it goes only to a level and a rank and gets rid of the xp, which is why there is only 2. Back on OpenOffice I was able to insert the data right into a sheet and the comma would separate the data into cells, and the space would break it and start a new row, then pull that data into another sheet, and since the data is always changing, it would update every time I open up the document. It is possible for me to just copy it into a text document of sorts, but I am looking to have it auto update so I do not have to constantly manually update the data myself.

ve3oat · August 25, 2019, 12:52am

I understand. The manual editting is just inserting Returns (CRLF) in the appropriate places but has to be done carefully and would be a continuing chore.

A script or macro might be what you want, but I don’t know enough about them to be able to help you. I hope someone else can. Good luck.

This might help Macro Guides | LibreOffice Documentation - Your documentation for LibreOffice
Scroll down to the bottom. It says OpenOffice but the methods are the same in LibreOffice.

AdmFubar · August 25, 2019, 4:00am

A simple search and replace will work.
I am assuming by “import” your are copying and pasting from the web page into a LO document?
Paste into a Writer document, in search and replace search for a space character, and replace with \n for new line. make sure that the regular expressions box is selected.
if the format looks correct save as a plain text, and then from your operating system rename the extension from .txt to .csv. It should import into Calc just fine.