Parse data for txt file

[Carlyn]

Hi - hoping someone can help me.

I am trying to use data from an excel file and convert it into a string format in order to use in a txt file.

The first 3 columns in my attachment 'data' shows the way the data is received and the last column shows how I need it to be formatted.

I have also included it in the workflow attached.

The main issue I have is that the first part of the string is coming from the amount column, but I need to ensure that it is a total of 17 characters. With all the amounts being different lengths I am not sure how to fill the characters with zero's.

Can anyone please advise the best way to do this?

[Luke_C]

Hi @Carlyn 

Try using this formula: padright([FieldName],17,'0'). 

[DavidSkaife]

Hi @Carlyn 

Create a new field (String data type) and use the following formula:

PADLEFT(TOSTRING([Original Amount],2),17,"0")

This will convert your existing INT data to a string, but retaining the decimals, and then add 0 to the start until the length of the field is 17 characters

Note: the 17 characters will also include the decimal point and the 2 decimal places after which seems to be at odds with your example output. Do you need it to be 17 characters excluding the decimals or including?

[alexnajm · Accepted solution]

Seems like we need to add onto the above formula from @DavidSkaife to make it the correct amount of digits and add the Sign!

PADLEFT(TOSTRING([Original Amount],2),20,"0")+[Sign]+"0000000000"

[Carlyn]

Alex - thank you!

It works perfectly

[Carlyn]

Alex - do you know how I can format some empty columns in my text file. I have included a formula tool at the end of my flow where I tried to re-name the columns with just the relevant amount of spaces (characters) required, but this has not worked, as it returns the columns with ,, and all for 2 spaces .

The txt file I am trying to build as 5 empty columns, but the character lengths are different, so 1 character, 2 character, 4 and 11.

In addition its further complicated as there are 2 columns with 2 characters, so when I tried to re-name them, there was a conflict.

I also tried using the Padleft formula to see if that would work with spaces, but I am getting a parse error.

I have attached my workflow to demonstrate what I mean.

Just realised i had missed a bracket from my Padleft formulas, but still just getting ,, in my output - updated workflow attached

[alexnajm]

Sorry but this workflow doesn't work since it doesn't include the data - can you Export Workflow and upload the .yxzp?

also, since this is a separate problem, I might recommend posting as a new question since people will be more likely to answer since this already has an accepted solution

[Carlyn]

Thanks Alex - I have submitted a new query :-)