Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Carriage Returns in Data - Creating New Rows

MonBrazier
8 - Asteroid

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.

 

MonBrazier_0-1590970251835.png

 

MonBrazier_1-1590970251844.png

 

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

 

Thanks!

10 REPLIES 10
MarqueeCrew
20 - Arcturus
20 - Arcturus

Try this:

 

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

 

 that will delete the newline

 

Cheers,

 

Mark

 

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
MonBrazier
8 - Asteroid

Thanks for the suggestion.

Unfortunately this didn't work. 

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

MonBrazier_0-1590971149447.png

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

\r was the same result?

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
MarqueeCrew
20 - Arcturus
20 - Arcturus

Let's use brute force:

 

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

 

 cheers

 

mark

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
RobertOdera
13 - Pulsar

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)

RNO2_0-1590973677391.png

 

MonBrazier
8 - Asteroid

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:

 

MonBrazier_0-1590973902099.png

 

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
15 - Aurora
15 - Aurora

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.

AbhilashR_0-1590975264440.png

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

   

RobertOdera
13 - Pulsar

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
20 - Arcturus
20 - Arcturus

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels