0

I'm using QGIS. I have a KMZ file, which I have converted to a GPKG layer, with a column containing HTML code. That HTML encodes additional fields as lines in a table.

I would like to extract those additional fields into new columns in my gpkg table.

Here is an example of the html-coded table:

<html><body><table border="1"><tr><th>Field Name</th><th>Field Value</th></tr><tr><td>GlobalID</td><td>{D7537371-0A42-4DC7-A7F9-432FC741DF7F}</td></tr><tr><td>CreationDate</td><td>10/18/2020 9:26:10 AM</td></tr><tr><td>Creator</td><td>sfares</td></tr><tr><td>EditDate</td><td>3/20/2021 11:29:41 AM</td></tr><tr><td>Editor</td><td>sfares</td></tr><tr><td>Archaeo Site</td><td>Null</td></tr><tr><td>Other - Type</td><td>Null</td></tr><tr><td>Isolated artefact</td><td>other</td></tr><tr><td>Other - Isolated artefact</td><td>stone tool</td></tr><tr><td>Observations</td><td>Null</td></tr><tr><td>Site Extension</td><td>Null</td></tr><tr><td>Site Name</td><td>WS20-AGC</td></tr><tr><td>ID Number</td><td>2</td></tr><tr><td>Nbr of pieces</td><td>Null</td></tr><tr><td>Latitude</td><td>Null</td></tr><tr><td>Longitude</td><td>Null</td></tr><tr><td>Altitude</td><td>Null</td></tr><tr><td>Haccuracy</td><td>Null</td></tr><tr><td>Vaccuracy</td><td>Null</td></tr><tr><td>Satellites</td><td>Null</td></tr><tr><td>Position Source </td><td>Null</td></tr><tr><td>Altitude Type</td><td>Null</td></tr><tr><td>Ecosite Type</td><td>Null</td></tr><tr><td>Ecosite Extension</td><td>Null</td></tr><tr><td>X UTM38N</td><td>Null</td></tr><tr><td>Y UTM38N</td><td>Null</td></tr><tr><td>Point Type</td><td>feature</td></tr><tr><td>Previous ID</td><td>Null</td></tr></table></body></html>
1
  • 1
    Have you tried the plugin "KML Tools" it has a function named "Expand HTML description field" to extract the KML/KMZ description to individual columns. It doesn't always work - depends if the layout of your description matches one of the plugins predefined formats. Worth a look.
    – M Bain
    Commented Sep 29, 2022 at 21:02

1 Answer 1

1

A robust solution that fully parses any syntactically valid HTML table, extracting whatever field names are within it, will probably require writing a Python script which uses an XML converter like lxml. Then you could extract the fields therein as a dictionary or array, and then add those elements as fields to your layer. This is doable but will take some effort unless someone's already built a plug-in that does it.

However, if you know what the field names in the table are, and you can be sure the way the table is encoded is precisely as in your example, you can do the following.

  1. Load up the layer with the HTML table as a string field. I'll assume it's called "HTML Table"; modify below accordingly.
  2. For each field you want to extract, in QGIS use Field Calculator to create it (potentially even as a virtual field).
  3. While creating it, populate it with a field expression that uses regex to extract the relevant item in the table.

For this field expression, the following works for me with the table HTML exactly as you specified, replacing GlobalID with the right field name for each field:

try(regexp_substr("HTML Table",'GlobalID'||'</td><td>(.+?)</td>'),NULL)

As explanation, and in case you or a future reader needs to modify this for their situation:

  • The 'GlobalID'||'gobbledygook' is regex syntax that looks for a table cell containing precisely GlobalID, the field name, in one table cell, and then grabs the contents of the next cell over, which is (hopefully) the intended value of that field. .+ means one or more characters. The rest looks for it preceded by the field name and </td><td> and due to the ? (indicating lazy in regex) it matches as few characters as possible up the first possible next </td>. Finally, the () means it only returns that group, not the delimiters around it.
  • regexp_substr is a QGIS field expression function that applies that regex pattern and pulls out the substring it matches, which is what you want
  • the try(...) wrapper makes sure you get a null value back if the field can't be found or there's some sort of syntax error. It may not be necessary (maybe regexp_susbtr always fails elegantly) but I like using it with arcane or fragile stuff to avoid inscrutable errors for users if anything goes wrong, something I didn't test for when coding it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.