Parse data for txt file

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?

data.png

Format to string.yxmd

Help

Accepted answers

alexnajm

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"

Format to string.yxmd

All comments

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

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"

Format to string.yxmd

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

Empty columns.yxmd

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703