topic Re: Strip HTML in QlikView

Strip HTML

Anonymous — Thu, 29 Oct 2015 12:18:19 GMT

Hi guys,

I have a field in SQL with HTML tags.

Is there a way to remove these?

<div>Twin needle stitching detailing. Double fabric hood with self coloured cords. Kangaroo pouch pocket with small hidden opening for earphone cord feed. Ribbed cuffs and hem. Worldwide Responsible Accredited Production (WRAP) certified production. 50 great colours. Soft cotton faced fabric creates ideal printing surface.

Washing Instructions:

Machine wash 30°. Do not bleach. Tumble dry low heat. Low iron. Do not dry clean</div>

Iconic school sweatshirt. Twin needle coverseams on neck. Raglan sleeve cuffs and waistband for extra durability. Herringbone back neck tape. Spotshield stain resistant coating for fabric protection. Fade resistant. Washing Instructions: Suitable for 40°C wash and tumble dry

I'd also like to remove °

These are just a couple of examples!!

Thanks

Re: Strip HTML

Anonymous — Thu, 29 Oct 2015 12:20:17 GMT

I've also discovered a field like this!

<div>

<li>Tubular construction for stability</li>

<li>1x1 rib trim with elastane</li>

<li>Herringbone neck tape</li>

</ul>

Fabric:

50% Combed ringspun cotton, 50% Polyester

Weight:

295gsm</div>

Is this going to be impossible? Haha

Re: Strip HTML

marcus_sommer — Thu, 29 Oct 2015 12:26:19 GMT

Have a look on the qv table-wizard which could natively read html and provided a preview and within the extended are options to read the attribut-values, too.

- Marcus

Re: Strip HTML

Anonymous — Thu, 29 Oct 2015 12:32:06 GMT

Hi Marcus,

What if the data is stored in a SQL table. Can I still do this? If so, how?

Many thanks!

Re: Strip HTML

jonathandienst — Thu, 29 Oct 2015 12:49:09 GMT

The way I see it, you have two options (if you want to avoid messy string mangling of the text):

Read the database and output the XML as a text file, then load the text file.
Read the database to get the XML into a single field, then use LOAD ... FROMFIELD to read the XML

In both cases, you will find it easier to develop the XML load if you manually create a file containing XML in the same schema. Once that load is working, you can use the generated script as a template. Qlikview requires the XML to be well-formed, so partial tags and any constructs not complying with XML rules will prevent the data from loading.

FROMFIELD is a little used feature and there is almost no documentation and very little even here on the community. I have managed to get that working once, but it was some time ago and I no longer have that code. Perhaps someone reading this can offer some asistance.

Re: Strip HTML

jonathandienst — Thu, 29 Oct 2015 12:53:56 GMT

Another option came to mind after posting the above. VBScript supports regular expressions, and you can call VBScript functions from the load script. You could then parse the text with regular expressions, which will make for much simpler string handling.

Calling vbscript functions is a performance killer, though, so I would only consider this if the size of your data set is not large.

Search the commuity for vbscript and regular expressions / regex for the code.

Re: Strip HTML

marcus_sommer — Thu, 29 Oct 2015 13:18:21 GMT

This might be helpful: https://www.qlik2go.net/en/2014/08/08/b/

- Marcus

Re: Strip HTML

rwunderlich — Thu, 29 Oct 2015 13:19:06 GMT

See Qlikview Cookbook: Strip HTML From Text http://qlikviewcookbook.com/recipes/download-info/strip-html-from-text/

for a working example of using Regular Expressions to remove html tags.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Strip HTML

Anonymous — Thu, 29 Oct 2015 13:49:45 GMT

Thanks for your advice guys. I actually found this in on another thread which seems to work really well !

Data:

first 200

LOAD

RecNo() as RecNo,

SKU,

Description_IncHTML

From

Data\nop_product_data.qvd (qvd)

where

not wildmatch(SKU,'ML*')

;

HtmlTag_Map:

MAPPING LOAD DISTINCT '<' & TextBetween('<' & SubField(PurgeChar(Description_IncHTML, chr(13)&chr(10)), '<', IterNo()),'<','>') & '>' as HtmlTag, '' as Substitute

Resident Data

While NOT IsNull(SubField(Description_IncHTML, '<', IterNo()))

;

JOIN(Data) LOAD RecNo, MapSubstring('HtmlTag_Map', Description_IncHTML) as Field_Cleansed

Resident Data

;

I am however still seeing carriage returns

Warm and comfortable fleece jacket. Shaped to fit. Full zip front with cadet collar. Two zipped closed front pockets. Two interior pockets. Adjustable base drawcord. Easy re-label system.

Washing Instructions:

Machine wash 40°. Gentle setting. Do not bleach. Iron low. Do not tumble dry. Do not dry clean

Is there a way to remove these? I'd also like to remove the "°" too !

Re: Strip HTML

Anonymous — Thu, 29 Oct 2015 15:32:21 GMT

Thanks for all your help guys. I have found a solution here (if anyone is interested)

Data loading with extra characters - need to trim

This worked perfectly for me !

Re: Strip HTML

rwunderlich — Thu, 29 Oct 2015 18:02:11 GMT

You can remove the newlines with a mapping. The mapping is also where you can map the entities like ° as well. I've updated the QlikVIew Cookbook example above to show mapping entities as well.

-Rob