remove blank fields from end of rows in a text file

[paulb1]

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.

[BarnesK]

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

[paulb1]

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

[MarqueeCrew]

@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

[BarnesK]

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).

Input data

Output

[paulb1]

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.

[BarnesK]

@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.

[paulb1]

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

[BarnesK]

@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?

[paulb1]

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).