Converting HTML Tables derived from URL's on the Web to Datasets 211112 Revisited

Question

In a previous question [211112] that deals with importing data from a Wikipedia table directly into a Dataset.

For the following data:

tables = getHTMLTables[  "https://en.wikipedia.org/wiki/Comparison_of_virtual_reality_headsets"]

as given by b3m2a1 works like a charm.

However, using the function xmlTableToDataset[tab_] as given as an answer in 211112, followed by its evocation as also given, fails for a different URL containing 5 HTML generated tables. This is the case even though the 5 tables seem to be faithfully identified as XML table objects containing a list of XML-Objects for input into the xmlTableToDataset function:

tables = 
getHTMLTables["http://earthwise.bgs.ac.uk/index.php/OR/14/008_Lithostratigraphical_units_used_by_BGS"];
ds = xmlTableToDataset@tables[[3]]       

Mapping over the third seemingly should work since there is a 3rd table on the URL and encapsulated in the tables list.

Unfortunately, it only produces a cryptic figure of a small box as a result of the following:

ds2 = xmlTableToDataset@tables2

whereas the command:

ds2 // Keys // First // Normal 

generates the error: {} has zero length and no first element

It seems that something in the xmlTableToDataset function needs generalizing. However, I can't seem to isolate the offending code even though I suspect it has to do with either the tab[[3]] construct or perhaps the Symbolic XML for the header row in the new target table that may be more complicated than expected.

How can this function be generalized to work without failure on the tables on the new target URL or other HTML tables on other URL's?

Learning how to manipulate XMLObjects still remains beyond my grasp so examples that broaden my understanding would be most appreciated as would any other approach in this instance.

NB. Strickly speaking, the above URL is not on the Wikipedia site itself but is pointed to as a reference standard in various discussions of geology. The first part (tables[1]) of table actually has no header info and only 1 row, so it can be ignored.

Answer 1

The problem is that the Wikipedia tables have <th> tags on the first row whereas this webpage uses <td> tags for the header line. For this reason, in the xmlTableToDataset function you need to replace

headers =
    Cases[
        headerRow, 
        d : XMLElement["th", _, _] :> (... etc ...),
        Infinity ];

with

headers =
    Cases[
        headerRow, 
        d : XMLElement["td", _, _] :> (... etc ...),
        Infinity ];

Then we have small problem when reading the rows because the header line would be repeated. This can be solved by replacing:

rows = Cases[tab, XMLElement["tr", _, _], Infinity];

with

rows = Rest @ Cases[tab, XMLElement["tr", _, _], Infinity];

After these changes, xmlTableToDataset will work with your url and will stop working with Wikipedia. Can we modify it so that it works for both? This is one way to do so with very a few small changes:

xmlTableToDataset[tab_] :=
    Module[ {headerRow, headers, data, rows},

        headerRow = FirstCase[ tab[[3]], XMLElement["tr", __], None, Infinity ];

        headers = 
            Cases[ 
                headerRow, 
                d:XMLElement["th" | "td",_ ,_ ] :> StringTrim @ StringJoin @ Flatten @ System`Convert`HTMLImportDump`SymbolicXML2Text[d],
                Infinity 
            ];

        rows = 
            Cases[
                DeleteCases[tab, headerRow, Infinity],
                XMLElement["tr" ,_ ,_ ],
                Infinity
            ];

        data = 
            Map[
                Cases[
                    #,
                    d:XMLElement["td", _, _] :> StringTrim @ StringJoin @ Flatten @ System`Convert`HTMLImportDump`SymbolicXML2Text[d],
                    Infinity
                ] &,
                rows
            ];

        Dataset @ Map[AssociationThread[headers, #] &, Select[data, Length @ # == Length @ headers &]]
    ]

Answer 2

If you only want to scrap tables, here is another alternative:

ClearAll[scrapTables, scrapTablesCore];

scrapTables[url_String] := scrapTablesCore[URLRead[url]["Body"]]
scrapTables[path_File] := scrapTablesCore[ReadString[path]]

scrapTablesCore[text_String] := 
 DeleteCases[#, {}, Infinity] &@
  StringCases[text, 
   Shortest[
     RegularExpression@"<[Tt][Aa][Bb][Ll][Ee][.\\s\\S]*?>" ~~ 
      table___ ~~ 
      RegularExpression@"</[Tt][Aa][Bb][Ll][Ee][.\\s\\S]*?>"] :>
    StringCases[table, 
     Shortest[
       RegularExpression@"<[Tt][Rr][.\\s\\S]*?>" ~~ row___ ~~ 
        RegularExpression@"</[Tt][Rr]>"] :>
      StringCases[row, 
       Shortest[
         RegularExpression@"<[Tt][DdHh][.\\s\\S]*?>" ~~ cell___ ~~ 
          RegularExpression@"</[Tt][DdHh]>"] :>
        StringReplace[StringTrim[cell], 
         Shortest[RegularExpression@"</?[.\\s\\S]+?>"] -> ""]]]]

Examples:

The input type is a URL:

Dataset /@ 
 scrapTables[
  "http://earthwise.bgs.ac.uk/index.php/OR/14/008_Lithostratigraphical_units_used_by_BGS"]

The above code will output 5 datasets.

Or as a File:

Dataset /@ scrapTables[File["C:\\file.html"]]

If you want to pick the second table:

Dataset @ scrapTables[File["C:\\file.html"]][[2]]

Notes:

• It does not store tag's information, only the cell content as a string
• Strings in form of tags such as <i>,<div>,... will be removed from the content (anything wrapped inside <>)
• Output is a list of matrices in which each cell type is String
• Use AssociationThread and the first row to create a Dataset with column
• If you save the .html file, you could use the function with File[...] as the argument

Remember the page source may be different from what you see. Seeing 2 tables on a page doesn't mean that the page has only 2 tables in its source. As an example, the light blue banner on top of the page of your link is also a table if you check the source.