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