Convert HTML value into Text

[MartijnWanders]

Hi all,

I have to extract data from a cloud source and I'm using Application Automation to sent it to Snowflake. Now I'm getting the value in HTML format. This data is not going into a Qlik dashboard but a in datamart view created in Qlik Compose. Snowflake is the datawarehouse but it seems that Snowflake can not convert the data.

Is there a block or function to convert the HTML value in normal text? I want to avoid a complex expression that purge characters etc.

Example (I cut off text to make the string shorter and less reconizable. I can not share the full string):

"value": "<p><span lang=\"NL\">Het Claen zijn: <\/span>FileMaerver.<\/p> <h3>Clro<\/h3> <p>Met Clarog:<\/p> <ul> <li>laytten<\/li> <li>ingeken<\/li> <li><\/p>"

Thank you in advance!

[Shai_E · Accepted solution]

Hi Martijn,

I assume you need the raw text without the html tags?

If that is the case, there is a regex expression which is not too complicated which strips all of the html tags and leaves only the text behind.

Code in javascript to be used in a "Custom Code" block inside an automation:

// Example code
let html = "<p><span lang=\"NL\">Het Claen zijn: <\/span>FileMaerver.<\/p> <h3>Clro<\/h3> <p>Met Clarog:<\/p> <ul> <li>laytten<\/li> <li>ingeken<\/li> <li><\/p>"


console.log(html.replace(/(<([^>]+)>)/ig, ""))

[AfeefaTk · Accepted solution]

Hi @MartijnWanders 

You can use the 'regex replace' formula to replace HTML tags based on regular expression

For more details on how to use the formula refer: https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_QlikAutomation/working-with-...

I have created an example automation for your reference

Hope this helps!

Thanks

[Shai_E]

Hi @MartijnWanders ,

Afeefas way is cleaner, since you only need to use the regex in the formula, with the string value.

I forgot about the replace formula.

[MartijnWanders]

Hi Shai and AfeefaTK,

Regex was exactly wthat I'm looking for. Thanks for the fast support!

[MartijnWanders]

Hi AfeefaTK,

I have question about the REGEX function. In Snowflake you have also the REGEXP_REPLACE function. But there your /<[^>]*>)/ doesn't work. On this page I can find more information: https://docs.snowflake.com/en/sql-reference/functions/regexp

Do they use another pattern? I don't understand how this works. Can you help me a bit? Will really appriciate this.

My expression in SF:

REGEXP_replace(T3.ATTRIBUTE_VALUE,'/<[^>]*>)/','') AS PRODUCT_VARIANT_DESCRIPTION, 

[AfeefaTk]

Hi @MartijnWanders 

Sorry, I am not that expert in snowflake

Could you please ask this question in the snowflake community

https://community.snowflake.com/s/article/How-to-use-snowflake-regular-expression

Thanks

[MartijnWanders]

Hi AfeefaTK,

Ok no problem. Thank your for replying!