Using Search And Replace to Correct Bad Database Records

Document created by josh_hutchins778263 Employee on Apr 7, 2017Last modified by chris_stevens on Apr 28, 2017
Version 4Show Document
  • View in full screen mode


You have a process where you receive database records and send through a map, turning it into XML to upsert into Salesforce. But within your data, there is a record that contains carriage returns and line feeds, as such:


What do we do in situations like this? Where the only thing wrong with your record is one too many Line Feeds / Carriage Returns (LF / CR) and worse yet, this isn't the only time it will happen if left untouched. We need to fix it!



On whichever database you're using, you'll need to add a dummy field. The name is important, in that it should not occur anywhere within your data as well as making sure it is placed AT THE END of each record. For this example we'll set it to: DummyValue.


The data should now look something like this:


After setting up your data similar to the above, you're going to add a data process shape and configure two Search/Replaces. The first Search/Replace will Find All Instances of Carriage Returns and Line Feeds in the document and replace them with Nothing. You're going to want to put in the Text To the regex [\r\n], the [\r] is for Carriage Returns and the [\n] is for Line Feeds. When both Carriage Returns and Line feeds are together, you have to combine the two into one [\r\n]


The second Search/Replace should be set to search for our DummyField value and replace it with Carriage Returns and Line Breaks. Since you added that extra Database field, you have an extra delimiter "|", you're going to get rid of both this and the DummyField value by putting it in our Text To Find. You're then going to use a \ as an escape character for the "|" so it doesn't add [\r][\n] to everything due to it's Logical OR reference.  In Text To Find put \|DummyValue and for Replace With put  [\r][\n].


An example process using this data is shown below. The data provided above is in a message shape, which is then attached it to a Data Process Shape:


When you run this as a test, it will result in the following data:

4 people found this helpful