community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.9

The latest release includes several enhancements designed to improve your Community experience!

Learn More

remove blank fields from end of rows in a text file

Asteroid

I have a tab delimited text file and some of the rows have extra tabs after the last value.  I need a way to be able to remove the blank fields at the end of the rows.  I need a way to remove these blank fields while preserving the line feed.

Pulsar
Pulsar

Hello @paulb1!

 

Try downloading this macro from the Alteryx Gallery. It should accomplish what you're looking for!

https://gallery.alteryx.com/#!app/Remove-Nulls/5717e389aa690a170c480548

Asteroid

Even if I have nulls in the rows that are legit?  I only want to strips the nulls from the ends of the rows.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@paulb1,

 

I'm taking you very literally.  I assume that you're reading in a text file and want to output a text file.  The input file can be read as a CSV with \0 as a delimiter and the first row contains data.  Now you have Field1 with all of your text.

 

The following RegEx expression will remove trailing tabs.

 

Regex_Replace([Field1],"^(.*?)\s+$",'$1')

It looks from the beginning of the field until it encounters the last non-space character as GROUP 1.  When it finds that everything after that is spaces or tabs, it will drop that data.  This may cause you to have unequal amounts of delimiters per record.

 

When you output the data, you may choose to output it to a FLAT file with a .csv or other extension name.  

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Pulsar
Pulsar

Yes @paulb1! See the example below. The first field has a null value in the second row, but it will still keep that field because there are other rows that are populated. It will only get rid of a field if all of the values in it are null (here, Field2).

 

1.PNGInput data

 

2.PNGOutput

Asteroid

I might be doing some differently, or not fully understanding.  I created transactions, then unioned them, to stack them.  I need to remove the blank fields after the last field in each row.  But the columns aren't the same fields most of the time.

 

SUD Alteryx.JPG

Pulsar
Pulsar

@paulb1 So for example, are you wanting to get rid of Col5-Col10 in #1 because those fields are blank for that row? Or for #2 it would be Col8-Col10? In this data set, that isn't really something that you can do. This is because there is data in that column for other rows. Therefore that column must exist in order to capture the data in those other rows. If you transpose the data and list all of your fields in one column, you could list them all without having blanks, but I don't think that's what you're looking for.

Asteroid

Yes #1 remove 5-10 and #2 remove 8-10 and so on.

Pulsar
Pulsar

@paulb1 Did my explanation in my last post make sense for why you can't do that? 

 

@MarqueeCrew maybe you have a better way of wording it?

Asteroid

Makes sense.  Was hoping there was some way of identifying the last field on each row with data then deleting everything to the right, and add a line feed (if necessary).

Labels