Copy HTML Table and Paste while maintaining Table Layout

Cborn1 · December 13, 2023, 5:22am

Portfolio Positions - Copy.xlsx (89.5 KB)

I have a website that I use that ouputs data in an HTML table, I highlight the data to select it and use Control-C to copy it. In Calc I select the first cell, A1, on the spreadsheet
and use Control-V to paste the data. All of the data pastes into cell A1 only. If I perform the same process in Excel 365, the data pastes into cells A1, B1, C1, etc. Using “Transpose” does not function. Pasting into Notepad and then copying and pasting into Calc has no effect. This issue occurs with Microsoft Edge, Chrome, Firefox and Opera. If I export the data in a CSV format from the website, the data shows up perfectly in the columns. I am at a loss as how to replicate the data layout with copy and paste.

mariosv · December 13, 2023, 10:15pm

Test with the options in paste special [Shift+Ctrl+V]

Cborn1 · December 18, 2023, 4:15am

I tried your suggestion. It did not work. The results are the same as before.

Grantler · December 18, 2023, 10:14am

Let us know the original source of your text/file for taking a copy.

Inserting “contents” as unformatted often solves problems like yours. There are many import rules to apply.

Wanderer · December 18, 2023, 6:34pm

I tried with a simple table, and it worked. But today a lot of “tables” are not encoded as tables, but may actually be styled

-sequences or columns in a grid-layout, not recognized by the import filter. So the first step should be inspecting the source of the website, then maybe create a special web-scraper for the site, if necessary.

Cborn1 · December 19, 2023, 3:27am

https://www.barchart.com/options/most-active/stocks?viewName=main&orderBy=optionsTotalVolume&orderDir=desc

This is one for example.

Cborn1 · December 19, 2023, 3:29am

That could be the issue as I have tried using a generic web-scraper and get an “access denied” error message.

This is the website.

https://www.barchart.com/options/most-active/stocks?viewName=main&orderBy=optionsTotalVolume&orderDir=desc

Grantler · December 19, 2023, 4:39pm

Seem to be dynamic Javascript “tables”. No way to regular copy. What you can do is to take a screenshot and then let it process in an OCR “image to table” or “image to Excel” or “image to ods” (US or EN because of decimal separators).
.
Have a look into the source code (Firefox: CTRL+U, German GUI).