Remnoving extraneous delimiters within a *.txt output file

I have created a "|" 'piped' delimited *txt file with records with variable number of fields and length by using the UNION and OUTPUT to a *.txt file.

However the shorter records, there are extra delimiters which I need to remove. Does anyone know of a way to remove these extra delimiters?

#I|213198-001|ACME|ACME1|06/02/2023|0.00|USD|||||006||||Macro Upload
#Z|99USDZV1|||06/02/2023|||||||||||

#D|448.31|019304-0026|NOTAXIN|80293|G.Mathews - Breakfast - Cold Continental on May 18 2023|H0150|||||||||
#D|793.74|019304-0026|NOTAXIN|80293|G.Mathews - Lunch - Hot (Two Entrees) on May 18 2023|H0150|||||||||

Workflow

Output

Accepted answers

All comments

geraldo

@SLITTLE

Isn't it better for you to correct the generation of this file

SLITTLE

I am open to any suggestions, but I am not sure how you would generate a 'piped' delimited file containing with variable # of fields and length?

jdminton

Are the extra delimiters there because there a blank columns after the data? Typically in a delimited file, you will see multiple delimiters at the end of shorter lines or extra delimiters in the middle because those columns need to be held with null values or they won't line up when imported.

jdminton

Are you creating this somewhere else to import to Alteryx? I'm just trying to understand the context.

geraldo

@SLITTLE

in these records that you passed all of them have 15 "|"

he is balanced

SLITTLE

Yes, correct. I leveraged the UNION to join 3 record types with vaiable # of fields and length. See screenshot.

Ignoring the header, the position of the fields are correct. My issue is the 1st records has 16 fields, the 2nd record type has 5-fields and the 3rd record type has 7 fields. Therefore, the shorter record types have the extra delimiters at the end record which needs to be removed.

jdminton

I think you are misunderstanding the delimiter purpose. As Geraldo pointed out, the example you provided has the correct number of delimiters in each row as the empty columns still need to be delimited. You do want to have the \newline delimiter at the end of each row since the text file will not read them as separate lines without it, but that may just not be visible in your example. Have you tried to import the text file back into Alteryx to read it and make sure it is working correctly?

SLITTLE

I do understand what the delimiter purpose is. However, my text file contains different record type ( i.e. qualifier). So based on the record type - each has a required # delimiter ( i.e. # of fields). Not all require 15 '|' delimiters.

jdminton

Sorry if I offended. That was not my intent.

Ok, so whatever is reading this text file later will qualify each record and expect a certain number of fields based on the qualifier. If that's the case, you should be able to add a formula tool with TrimRight([Field] , "|") at the end of the workflow to remove the trailing pipes.

SLITTLE

No worries - all good. I appreciate the assistance. 😀

jdminton

Did that fix your issue?

SLITTLE

Actually, after the UNION, I did an IF statement based on the record type to output to a single field by concatenating all required fields required along with the required number "|" for each record type.

Thank you again for your guidance

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15881

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703