2

I would like to import a KMZ file with data on basin internal evaporation recycling into QGIS.

However when I import the KMZ file into QGIS, the relevant data (Bier-jan, BIER-feb, ...) is not added in individual columns of the attribute table but rather all values are added into one single column with together with html syntax.

enter image description here

The cell content of cells in the 'description' column looks is as follows:

    <html xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:msxsl="urn:schemas-microsoft-com:xslt">

<head>

<META http-equiv="Content-Type" content="text/html">

<meta http-equiv="content-type" content="text/html; charset=UTF-8">

</head>

<body style="margin:0px 0px 0px 0px;overflow:auto;background:#FFFFFF;">

<table style="font-family:Arial,Verdana,Times;font-size:12px;text-align:left;width:100%;border-collapse:collapse;padding:3px 3px 3px 3px">

<tr style="text-align:center;font-weight:bold;background:#9CBCE2">

<td></td>

</tr>

<tr>

<td>

<table style="font-family:Arial,Verdana,Times;font-size:12px;text-align:left;width:100%;border-spacing:0px; padding:3px 3px 3px 3px">

<tr>

<td>BIER-jan</td>

<td>0,021422</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>BIER-feb</td>

<td>0,02088</td>

</tr>

<tr>

<td>BIER-mar</td>

<td>0,019949</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>BIER-apr</td>

<td>0,013789</td>

</tr>

<tr>

<td>BIER-may</td>

<td>0,011909</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>BIER-jun</td>

<td>0,007501</td>

</tr>

<tr>

<td>BIER-jul</td>

<td>0,007841</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>BIER-aug</td>

<td>0,005692</td>

</tr>

<tr>

<td>BIER-sep</td>

<td>0,005994</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>BIER-oct</td>

<td>0,00542</td>

</tr>

<tr>

<td>BIER-nov</td>

<td>0,009162</td>

</tr>

<tr bgcolor="#D4E4F3">

<td>BIER-dec</td>

<td>0,016144</td>

</tr>

<tr>

<td>BIER-annual</td>

<td>0,013681</td>

</tr>

</table>

</td>

</tr>

</table>

</body>

</html>

How can I import the KMZ file and add the individual data sets (BIER-jan, BIER-feb, ... ) to dedicated columns in the attribute table?

I already tried to extract the respective numbers with string extraction and string position via the field calculator but a lot cells ended up with a NULL values. ... something like extracting the eight values after the start position of BIER-jan+syntax

 substr(description,strpos(description,'BIER-jan')+17,8)

There is also an online converter which does the job but it is limited to 5MB per month.

Improve this question

1 Answer 1

Reset to default
1

To extract the information, you can use Regular expressions with function regexp_matches(). Based on the syntax you provided, to extract the value for BIER-jan, use this expression:

regexp_matches(description , '(<td>BIER-jan</td>

<td>)(.*)(</td>)')[1]

Replace jan with feb etc. for the other fields.

Variant:

Use the following expression to get an array of all values (jan to dec + annual). If you add an index operator in angular brackets [] at the very end, on the last line, after the closing bracket ), you get the value for jan with [0], feb with [1], ... annual with [12]:

array_foreach (
    array('jan','feb','mar','apr','may','jun','jul','aug','sep','oct','nov','dec','annual'),
    regexp_matches(
        description , 
        eval('''(<td>BIER-'' || @element || ''</td>

<td>)(.*)(</td>)'''
        )
    )[1]
)

Using index operator [12] to get the last - annual - value. Index start qith 0 for the first element, so index 12 stand for the element no. 13: enter image description here

Improve this answer

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.