Add trailer record without extra pipes at the end

[Val2018]

I need to produce a pipe delimited output with the trailer record containing the number of records without the header.  However, the trailer record should not have pipes after the number or records.  I need help with figuring out how to do it since my results ended up having as many pipes as the fields in the data.  The current WF and the resulting output are attached.

[Garabujo7]

Hello @Val2018 ,

I looked at your results and are correct. The extra pipes on the end mean empty spaces in the extra columns

You have two additional pipes at the end. Both mean the 2 extra columns with no values.

It is easier to see it in the data. The address and issue are null for the number of records

Gabriel

[Val2018]

@Garabujo7

Is there a way to remove the pipes but otherwise to keep the output intact?

Thanks,

Valentina

[danilang]

Hi @Val2018 

To remove the extra pipes, you can use 

TrimRight([Field1],"|")

Note that if the target program is expecting a properly formed .csv file, you may get errors on input to the external program if the extra pipe characters are missing.

Dan  

[Val2018]

Hi @danilang,

Could you please clarify where in the workflow I need to insert the Trim formula?  As long as this specific record modification does not affect the rest of the data and allow it to be outputted in the right format, all is good. 

Thanks,

Valentina

[danilang · Accepted solution]

Hi @Val2018 

Since you're using the Output Tool to create the csv file, the delimiters are put in automatically with no way to remove them if they are the final characters in the field.  To get around this build the strings manually, trimming the trailing pipes as required, add a header row  and write them out to a csv file with no delimiter(\0)

Dan

[Val2018]

Hi @danilang,

Thank you for a creative solution!  It will work as long as there are not too many fields.

Valentina

[danilang · Accepted solution]

Hi @Val2018 

Here's the dynamic version.  It will work with any number of columns with no changes.

It transposes all values and then builds the value and header rows.  

Dan

[Val2018]

Hi @danilang,

Thank you so much for your help!

Valentina