In the past, if you wanted to use a table of data published on the internet, you would have to copy the data, and paste in into a spreadsheet.

The growth of interconnectivity does mean the following options are available:

Microsoft Excel:

Import

From the menu Data, Get External Data

Select “New Web Query”, and type in the URL in the address box. (For example https://swb1914.uk/index.php/the-1st-battalion/regular-6/)Now click in the “One or more specific tables on the page” box. There is only one table on each page on this website. You now have some formatting options, then you can press “OK” and import.

Alternatively….

Power BI

From the menu Power Query, From Web. At the prompt Enter a Web page URL copy the URL. By clicking on the OK button, a list of items will appear, one of them being the table. Clicking on it will produce a preview. Once you are happy with the selected item, click the Load button.

Google sheets:

Create a spreadsheet. Place the cursor in the cell which will be the top left corner of the table. In the cell, the following formula was inserted

=IMPORTHTML(“https://swb1914.uk/index.php/the-1st-battalion/regular-6/”,”table”,1)

It is important that the URL is in paretheses.

Open Office Calc

Create a spreadsheet. Place the cursor in the cell which will be the top left corner of the table.
From the menu, select Insert, Link to external data

From here, it gets a bit fiddly, but do stick with it.

You will be asked for the URL of extended data source and I inserted into this field. It seemed to do nothing for a while, so I also clicked on the Update every ..seconds checkbox in order that it would be prompted to ping the website, which it eventually did.

When the website had been pinged, a new dialog box appears.

Import options

Select the language to use for import – automatic/custom

Options: Detect special numbers (such as dates)

Having entered these details, I was returned to the original

The one difference is that values now appear in the Available tables/ranges box. I selected “html tables.” Then, I unchecked the “update every” box. The “OK” box was no longer greyed out, so I clicked on here to conclude.

If I want to edit this at a later date, it can be accessed via the menu option Edit, Links.