Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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
19 - Altair
19 - Altair

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 reboot. 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
19 - Altair
19 - Altair

\r was the same result?

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Please Subscribe to my youTube channel.
MarqueeCrew
19 - Altair
19 - Altair

Let's use brute force:

 

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

 

 cheers

 

mark

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Please Subscribe to my youTube channel.
RobertOdera
12 - Quasar

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
14 - Magnetar
14 - Magnetar

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
12 - Quasar

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
19 - Altair
19 - Altair

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 reboot. Order shall return.
Please Subscribe to my youTube channel.
Labels