Problem: Cannot replace ampersand contained in a loop variable.

Description: I have a flat file that is comma seperated. The third section is destined to be a varchar (max) field. It is a user input field.

I do a for each over the rows in the csv and build a sql statement varible that does a IF NOT EXIST (select...) INSERT INTO table (columns) VALUES ('{LOOP(CurrentValueXArray,2)}',...)

When it encounters a row with an ampersand it throws an error, so I tried to replace the ampersand.

When I try to do a REPLACE it returns Unhandled error in RemoveVariable: There is an error in XML document (12,85). Even though this works in the variable editor.

When I try to do a '{REGEX(Replace|{LOOP(CurrentValueXArray,2)}|\&+|, )}' it returns Unhandled error in RemoveVariable: There is an error in XML document (12,85). Even though this works in the variable editor.

The only way I can get it to work is to put
'{REGEX(Replace|{LOOP(CurrentValueXArray,2)}|\W+|, )}' but that replaces all the other non word characters we need.

This file is from a client so we cannot get them to pre-process and remove those characters.

I've tried all sorts of \& & combinations in replace with no luck.

Any advice?

Thanks,
Gary

Workaround: switching the Regex to use the unicode code for ampersand works in the replace. I'll check for any emails that are triggered when this gets fixed but this works for now. \u0026