Remove HTML characters from Excel upload into Exact Online items

Question

I have a spreadsheet with items to upload into Exact Online. Many of the items still have article descriptions containing a lot of HTML, such as:

<p><span style=""font-size: 12px""><span style=""font-family: verdana, geneva, sans-serif"">text<br />
text&nbsp;max&nbsp;text<br />
text</span></span></p>

I've tried to replace all occurrences by hand and using Excel find/replace, but it concerns thousands of articles.

Is there a possibility to use Invantive SQL to remove these special characters and get plain text?

Answer 1

The best way to handle such scenario is to define the Excel range as a named range or as an Excel table.

Let's assume the Excel range is named 'MYDATA'.

Then in Invantive SQL you can enter:

select xmldecode /* Replace & by & etc. */
       ( replace /* Remove line feeds for exact online when not desired. */
         ( regexp_replace /* Remove all other XML/HTML tags. */
           ( regexp_replace /* Replace line break in HTML by a line feed. */
             ( COLUMN
             , '<br/>'
             , chr(10)
             )
           , '<[^>]*>'
           , ''
           )
         , chr(10)
         , ' '
         )
       )
from   MYDATA@ic

And of course you can then do:

insert into exactonlinerest..items(columns) select ... -- see above