This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
The highly anticipated Alteryx Community tradition is back! We hope you'll join us!
Learn MoreI 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.
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
Even if I have nulls in the rows that are legit? I only want to strips the nulls from the ends of the rows.
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
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
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.
@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.
Yes #1 remove 5-10 and #2 remove 8-10 and so on.
@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?
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).