Carriage Returns in Data - Creating New Rows

[MonBrazier]

Please help! Hopefully this is an easy one!

I have a tab delimited csv file that contains carriage returns within a particular field.

Unfortunately this comes through a new row within Alteryx.

Does anyone know how I can handle this situation so there is no new row within Alteryx?

Thanks!

[MarqueeCrew]

Try this:

regex_replace([fieldname],"\n","")

 that will delete the newline

Cheers,

Mark

ps:  \r is a carriage return if \n doesn't work. 

[MonBrazier]

Thanks for the suggestion.

Unfortunately this didn't work. 

The carriage return comes through as a new row on initial input:

[MarqueeCrew]

\r was the same result?

[MarqueeCrew]

Let's use brute force:

regex_replace([field],"[^ -~]","")

 cheers

mark

[RobertOdera]

Hi, @MonBrazier  @MarqueeCrew 

How about REGEX_Replace([Field], '\r\n', "")?

Else you might be able to pre-work in a text tool

I used Notepad ++ to check (it worked there)

[MonBrazier]

Hi all 🙂

Thanks very much for your suggestions - however, I think I may need to handle this in a unique way....

Since the carriage return comes through into Alteryx as a new row in the input, I may need to be creative to wrap up and combine the affected rows.

Here is my test:

I used multi-row formula, and then filtered out the affected erroneous rows. 

However, I'm not too keen on the idea of hard-coding in the formula to look for (Honours) specifically. 

I'm thinking I can change my formula to:

if IsNull([Row+1:instcscd]) then [instcdsc]+[Row+1:course] else [instcdsc] endif

What do you guys think?  Is there a better way to handle this situation do you think?

Thanks again

Mon

[AbhilashR · Accepted solution]

Hi @MonBrazier, my suggestion would be to bring in the CSV data into Alteryx as is, parse the data and then remove the unwanted carriages. Something like the attached sample solution.

I have had success using this approach in the past and am hoping it works out for you too.

[RobertOdera]

Sure thing, @MonBrazier 

This is Alteryx!

As @MarqueeCrew stated, brute force is good-to-go.

First, make it possible, then make it work, then make it better. Repeat!

However, also, don't let perfect detract from good-enough.

No. Hard-coding for specific verbiage is not a good idea.

Yes. Multi-Row formula because the 'carriage effect' on the data set is predictable (null in [instcdsc]) and behavior the same (new row generated).

Can you share a 5-row sample file where this is occuring?

Otherwise, great job and cheers!

Kindly credit @MarqueeCrew 

[MarqueeCrew · Accepted solution]

Aaaah!

use \0 as your delimiter when you read!  also, uncheck first row option to keep names as data. 

 Next use one of my replace expressions.

now use a text to columns to create the right number of fields.  

now a dynamic rename and change the drop down option to use first row as names. 

the issue is that the row update happens on the input tool. 

cheers,

 mark