topic Re: Convert HTML value into Text in Qlik Automate

Convert HTML value into Text

MartijnWanders — Sat, 14 Jan 2023 10:17:07 GMT

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!

Re: Convert HTML value into Text

Shai_E — Sun, 15 Jan 2023 14:28:41 GMT

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, ""))

Re: Convert HTML value into Text

AfeefaTk — Mon, 16 Jan 2023 06:13:39 GMT

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-automations/working-with-formulas.htm

I have created an example automation for your reference

Hope this helps!

Thanks

Re: Convert HTML value into Text

Shai_E — Mon, 16 Jan 2023 08:26:50 GMT

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.

Re: Convert HTML value into Text

MartijnWanders — Mon, 16 Jan 2023 11:28:28 GMT

Hi Shai and AfeefaTK,

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

Re: Convert HTML value into Text

MartijnWanders — Wed, 01 Mar 2023 23:05:01 GMT

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,

Re: Convert HTML value into Text

AfeefaTk — Thu, 02 Mar 2023 03:38:26 GMT

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

Re: Convert HTML value into Text

MartijnWanders — Thu, 02 Mar 2023 06:53:09 GMT

Hi AfeefaTK,

Ok no problem. Thank your for replying!