"String_Split" action is returning extra records

Post Closed

Suraj Borade

Solved

Question

Hello Community members,

I am using "String_Split" action from "Text" extension. This action must split records as per delimiter.

I want to break my string based on "NewLine" characters hence giving delimiter as "NewLine()" but this action is returning me extra records which I want to avoid.

Is there any other way to split text based on NewLine() character?

or if I am doing anything wrong over here?

Thanks and Regards,
Suraj Borade

11 Apr 2016

MVP

Solution

you are not doing anything wrong, but splitting text based on newlines can be tricky...

especially, since the string_splits works on single characters only!

When you have a newline is can be (and most often is) 2 characters...namely \n and \r
Thus, when call the string_split, it will split "foo\n\rbar" in 3 records, "foo", "", "bar"

11 Apr 2016

Suraj Borade

Hi J,

Thanks a lot for the reply.

I tried to give delimiters as "\n\r" and "\r\n".

It worked partially. For some fields it broke string based on NewLine characters, for some string, it replaced characters "r" and "n" with newlines.

Not getting what to do to get correct result.

11 Apr 2016

André Siébra

Hi Suraj,

Maybe using regular expressions (see link below) to replace the newline characters with a new one (like "$"). So after you can do the split with the replaced char.

https://stackoverflow.com/questions/1331815/regular-expression-to-match-cross-platform-newline-characters

11 Apr 2016

MVP

Hi suraj,

it doesn't matter if you provide delimiters as \n\r since it will delimit on 1 character only!

I would go for Andre's suggestion, or simply remove the empty-records in the list.

11 Apr 2016

Kilian Hekhuis

MVP

You could also use a Replace() first, replacing all the CRLFs. You don't need a regex for something this simple.

11 Apr 2016