REGEX and REGEX_Replace

I have data (comma delimited text with text qualifier ") that ends ever line with a line feed (LF or \n). However, there is a description field that contains multiple LF. This is causing problems in the input as every the software hits the LF it pops a new record.

I was able to fix this 2 ways. One load data into Notepad++ and replace the end of lint "\n with "}, then remove all LF, then replace "} with "\n again. Alternately load the csv and check "allow new lines in quoted fields" (only works with AMP).

Before I found the check box I was trying to leverage regular expressions to repeat the method I used in Notepad ++. This involved loading the data as a flat file instead of csv. However, neither the RegEx tool nor the REGEX_Replace formula is working? This bugs me because I can see usefulness in this functionality in the future.

Suggestions?

here is a pic of the data. Every record ends with "LF, but you can see the other LF in that one description field:

Regex

Accepted answers

All comments

binu_acs

@goatley1 would you be able to provide a sample file?

acarter881

Hello, @goatley1.

What is the specific ask? Are you interested in a regular expression to parse data?

I believe this example does not require we manually find and replace newlines within Notepad++.

As an example, if everything is read into one column in Designer, we can split by newline and remove empty/null rows.

terry10

@goatley1

There might be several issues with the data in the picture you shared of your csv.

The description isn't enclosed in quotes and contains commas (so it can't be parsed as a comma-separated list)
Is all of the data of the same structure? e.g., is there a header for the description (picture is cut off on the side)

If your csv is properly configured, you can parse the csv file, then you can either

use the multifield formula tool with the formula: REGEX_Replace([_CurrentField_], "[\n\r]", "")
use the Data Cleansing Tool to remove unwanted characters

Example workflow attached.

remove unwanted line returns.yxmd

remove unwanted line returns.yxzp

goatley1

Unfortunately no.

goatley1

While what your describing may work. I should be able to load the data as a flat file. Replace all the "\n (this is the true end of line) with say a "}". Then replace all remaining \n with " " (space). Then go back and add reverse the firs step i.e., } --> "\n. However regex and regex_replace wouldn't work for this.

goatley1

Actually the description field is encapsulated. Take a closer look.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703