{"id":314,"date":"2017-12-09T15:19:00","date_gmt":"2017-12-09T15:19:00","guid":{"rendered":"https:\/\/swb1914.uk\/?page_id=314"},"modified":"2018-01-05T14:56:17","modified_gmt":"2018-01-05T14:56:17","slug":"accessing-the-data-tables","status":"publish","type":"page","link":"https:\/\/swb1914.uk\/index.php\/accessing-the-data-tables\/","title":{"rendered":"Accessing the data tables"},"content":{"rendered":"

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.<\/p>\n

The growth of interconnectivity does mean the following options are available:<\/p>\n

Microsoft Excel:<\/h1>\n

Import<\/h2>\n

From the menu Data, Get External Data<\/b><\/p>\n

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

Alternatively….<\/p>\n

Power BI<\/h2>\n

From the menu Power Query, From Web<\/b>. At the prompt Enter a Web page URL<\/b> 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<\/b> button.<\/p>\n

Google sheets:<\/h1>\n

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<\/p>\n

=IMPORTHTML(\u201chttps:\/\/swb1914.uk\/index.php\/the-1st-battalion\/regular-6\/\u201d,”table”,1)<\/span><\/p>\n

It is important that the URL is in paretheses.<\/p>\n

Open Office Calc<\/h1>\n

Create a spreadsheet. Place the cursor in the cell which will be the top left corner of the table.
\nFrom the menu, select Insert, Link to external data<\/b><\/p>\n

From here, it gets a bit fiddly, but do stick with it.<\/p>\n

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

When the website had been pinged, a new dialog box appears.<\/p>\n

Import options <\/b><\/p>\n

Select the language to use for import<\/b> – automatic\/custom<\/p>\n

Options: Detect special numbers (such as dates)<\/b><\/p>\n

Having entered these details, I was returned to the original<\/p>\n

The one difference is that values now appear in the A<\/b><\/u>vailable tables\/ranges<\/b> box. I selected \u201chtml tables.\u201d Then, I unchecked the \u201cupdate every<\/b>\u201d box. The \u201cOK\u201d box was no longer greyed out, so I clicked on here to conclude.<\/p>\n

If I want to edit this at a later date, it can be accessed via the menu option Edit, Links<\/b>.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 \u201cNew Web Query\u201d, and … <\/p>\n

Continue reading “Accessing the data tables”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"class_list":["post-314","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/swb1914.uk\/index.php\/wp-json\/wp\/v2\/pages\/314","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swb1914.uk\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/swb1914.uk\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/swb1914.uk\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swb1914.uk\/index.php\/wp-json\/wp\/v2\/comments?post=314"}],"version-history":[{"count":2,"href":"https:\/\/swb1914.uk\/index.php\/wp-json\/wp\/v2\/pages\/314\/revisions"}],"predecessor-version":[{"id":348,"href":"https:\/\/swb1914.uk\/index.php\/wp-json\/wp\/v2\/pages\/314\/revisions\/348"}],"wp:attachment":[{"href":"https:\/\/swb1914.uk\/index.php\/wp-json\/wp\/v2\/media?parent=314"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}