How to give space according to length in text file

[bhanu.priya2 (Bhanu Priya)]

Hi Team,

this is the reference file

this is the input file
the bot has to calculate the length of each row and subtract the calculated length from reference sheet with length column and the result is the space which we have to give in the text file…
say for example - in the input sheet first row is 238 and in the reference length is 3 so length of first row in the input is 3 so if i subtract it 3-3 = 0 so there is no space…likewise for the second row length is 8 digit and if i subtract it with the length column from the reference it is 30 so 30-8 is 22…22 times space i have to give…so including the length of the each row it should be the length as mentioned in the reference length column. Please help me how to achive this

and the output should be like this

[J0ska]

What you want is to build fixed lenght file format.
You can use approach described here

Formatting Fixed-width Text Output-VBForums

format = "{0,3}{1,30}{2,2}{3,2}{4,1}{5,2}{6,8}{7,8}{8,13}"

formatedString = String.format(format,238,86571234,4,3,1,1,13122021,12122022,12345)

Explanation:
1/ define format of the fixed lenght string (details under provided link)
2/ use “string.format” method to format data from your sheet using “format”

Cheers

[bhanu.priya2 (Bhanu Priya)]

Hi, Thanks for your response but i dont want to hard code anything inside the code, bot should calculate the space based on the length from the reference sheet

[J0ska]

Sure. You could build the “format” string dynamicaly based on your “reference” file, just loop through lenght column.

NOTE: If the value in your input file is longer than defined lenght of given field this method will not trim the value.

Cheers

[V_Roboto_V]

Hi @bhanu.priya2

The solution is same as @J0ska,
Here is how you can do it Dynamically:

The Data:
dt_Reference

dt_Input

The flow:

The Assign Activity for Format:

format ="{0," + dt_Reference.Rows(0).Item("Length").ToString + "}" + 
"{1," + dt_Reference.Rows(1).Item("Length").ToString + "}" +
"{2," + dt_Reference.Rows(2).Item("Length").ToString + "}" +
"{3," + dt_Reference.Rows(3).Item("Length").ToString + "}" +
"{4," + dt_Reference.Rows(4).Item("Length").ToString + "}" +
"{5," + dt_Reference.Rows(5).Item("Length").ToString + "}" +
"{6," + dt_Reference.Rows(6).Item("Length").ToString + "}" +
"{7," + dt_Reference.Rows(7).Item("Length").ToString + "}" +
"{8," + dt_Reference.Rows(8).Item("Length").ToString + "}"

The Assign activity inside For Each:

formatedString = String.format(format,CurrentRow(0).ToString,CurrentRow(1).ToString,CurrentRow(2).ToString,CurrentRow(3).ToString,CurrentRow(4).ToString,CurrentRow(5).ToString,CurrentRow(6).ToString,CurrentRow(7).ToString,CurrentRow(8).ToString)

THE OUTPUT:

Verification
As you can see below, when i paste the output on Regex 101 and check the length of the space, it matches with the math you gave (30-8=22)

I Hope this solves your issue, Do mark it as a solution.
Happy Automation

[bhanu.priya2 (Bhanu Priya)]

Hi Thanks for the response, but i dont know why my bot is picking the 3rd row

see it should pik the first row which is 238

[V_Roboto_V]

Did you use write text after the for each? Then only the last row will be there.

[V_Roboto_V]

Hi @bhanu.priya2

Here is a fix:
Give a default value of Empty String

Update The Assign Activity inside For Each:

formatedString = formatedString + String.format(format,CurrentRow(0).ToString,CurrentRow(1).ToString,CurrentRow(2).ToString,CurrentRow(3).ToString,CurrentRow(4).ToString,CurrentRow(5).ToString,CurrentRow(6).ToString,CurrentRow(7).ToString,CurrentRow(8).ToString) + Environment.NewLine

Include an Assign Activity outside the ForEach

formatedString = formatedString.TrimEnd(Environment.NewLine.ToCharArray())

Now use the Write Text Activity:

The Final FLow:

THE OUTPUT

I Hope this solves your issue, Do mark it as a solution.
Happy Automation

[imad-pro (Imad Eddine Berjamy)]

@bhanu.priya2

this is the input file
the bot has to calculate the length of each row and subtract the calculated length from reference sheet with length column and the result is the space which we have to give in the text file…
say for example - in the input sheet first row is 238 and in the reference length is 3 so length of first row in the input is 3 so if i subtract it 3-3 = 0 so there is no space…likewise for the second row length is 8 digit and if i subtract it with the length column from the reference it is 30 so 30-8 is 22…22 times space i have to give…so including the length of the each row it should be the length as mentioned in the reference length column. Please help me how to achive this

and the output should be like this

First, make sure you’re reading the input data and the reference sheet into two separate DataTables. For each row in the input data, calculate its length. Then, for each row, get the corresponding reference length from the reference sheet using the row index.

After that, subtract the input row’s length from the reference length to get the space required. If the result is greater than 0, use PadRight() to add the necessary spaces to the row content. If no space is needed, just use the row content without padding.

Finally, write the formatted output to a text file, making sure each line has the correct number of spaces.

This will ensure that the space calculation and text formatting work dynamically for each row.