[Solved] Trying to import data from website

[dannyboi93]

Hi,

So I'm trying to import data from here https://otce.finra.org/otce/marketStati ... p100Issues

and it's coming out like this

Additionally, it won't import the data directly, so I had to save the page as a html file and then import that.

Does anybody know how I would go about making the table on the website import to calc properly?

[robleyd]

I looked at the actual HTML - the "table" that you see is not created using HTML TABLE tags which would allow Calc to identify separate sets of data; rather it is laid out using CSS (Cascading Style Sheets). As a result, Calc has no way of identifying individual "cells" in the data, and presents it as one large collection of data in a cell.

It would be possible, for someone with some expertise, to parse the HTML into a suitable format for import into Calc using a shell script in Linux, or a programming language such as PERL, Python or similar.

[MrProgrammer]

Hi, and welcome to the forum.

Does anybody know how I would go about making the table on the website import to Calc properly?

The site is not designed to allow you to capture the data easily, perhaps intentionally. You can select the data on the page and copy it to the clipboard, then in Calc use Edit → Paste Special → Unformatted text. But the data is presented on multiple pages at the website, and you will need to import each one, not bad for two pages, but a significant nuisance for a dozen or more. The data is structured as one long list, not a table, and will be imported to the spreadsheet as one long list. The web page makes you think the data is a table due to the way it's formatted, but it is not. OpenOffice is not a web browser and is unable to copy the formatting which makes the data look like a table.

Once the data is imported to the spreadsheet I was able to rearrange it as shown on the Formatted sheet. But this process is highly dependent on the layout of that page remaining the same over time, and experience from the forum suggests that it will not. We have plenty of This used to work and now it doesn't posts from people trying to download financial stuff from the internet. Websites are constantly changing their data layouts. I doubt if this project is worth your time, since it will break soon enough.

202104141703.ods

(39.19 KiB) Downloaded 88 times

[Tutorial] Rearrange rectangular data values is a tool which can help you rearrange a list into a table.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
[Tutorial] Text to Columns