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.

[bhanu.priya2 (Bhanu Priya)]

Hi, thanks for the solution but I have one issue
Please find the below image, the date should type as same as in the input file example if the date is 13122021 it should also type the same in the txt file as attached below.

strong text

But it is printing like this

strong text

[V_Roboto_V]

Hi @bhanu.priya2

Make a small change in the Assign statement.

Convert.ToDateTime(CurrentRow(0)).ToString("ddMMyyyy")

Note: do this for the columns with date (i.e, column 6 and 7, i guess)

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

[bhanu.priya2 (Bhanu Priya)]

**it is coming like this, it threw an error **

[V_Roboto_V]

Could you check CurrentRow(7) in the immediate panel and tell me what it shows

[bhanu.priya2 (Bhanu Priya)]

**

**[quote="V_Roboto_V, post:13, topic:2752037, full:true this one?

[bhanu.priya2 (Bhanu Priya)]

This is row(6)

[bhanu.priya2 (Bhanu Priya)]

It should print like this…

But it is printing like below

strong text

[V_Roboto_V]

It is because of the data type that you have set for that column on Excel. I can see “Custom”.
Could you show the data format on excel?

The data is being read wrongly.

Also, Try with Use display format enabled and then try with ordinary assign of CurrentRow(6).ToString

[bhanu.priya2 (Bhanu Priya)]

It’s working fine now, I used the below logic DateTime.FromOADate(CDbl(row(6))).ToString(“ddMMyyyy”) Thanks for your help☺️