Excel data insert

Help
1

Hi I have one requirement in excel sheet one column must contain 8 digit
eg 12345678 if six digit is present means 123456 then I need to insert 00 before this 00123456
I need to count how many digit is present if more then 8 its fine if less then 8 then insert 00 before this can you please help me how can I achieve

2

Adding leading zeros is fairly easy. See below.
image

1 Like
3

@DanielMitchell thanks for reply But did not got your point firstly I need to count how much digits is present if less then 8 then insert zero.

4

You don’t need to count how many digits are present. If you write the value to the cell with “D8” passed to the ToString function then 8 digits will be written automatically.

5

Hi @Aditya10989

I’ll suggest you to follow these steps:

  1. Read Range (AddHeaders should be checked) to read excel sheet —> Dt (output variable - DataTable)
  2. Take For each loop activity
    Foreach row In Dt
  3. In for each row take Assign activity
    Length (Int32) = (row(“ColumnName”).ToString).Length —> to get count of digits.
    Where ColumnName should be your Dt’s header name having those digit values.
  4. Take If activity just after assign and set condition as Length < 8.
    Then part should have Assign activity
    row(“ColumnName”) = row(“ColumnName”).ToString(“D8”) —> this will assign value of digits with necessary number of zeros at the leading side of the value to make it 8 digit value.
    (e.g. If value is 12345 —> 00012345)
  5. Take Write Range next to the for each row activity (AddHeaders should be checked) write Dt in sheet2 for checking.

:slight_smile:

2 Likes
6

@samir Thanks for reply I am getting error

Untitled
Untitled1440Ă—900 67 KB

7

hi @Aditya10989

yes. :slight_smile: my bad.
for that you have to mention rowIndex as well as column Index
make these 2 changes instead of that assign

  1. take assign
    no (int32) = CInt(Dt.Rows(Dt.Rows.IndexOf(row))(“Number”)) —> to get number
  2. another assign
    Dt.Rows(Dt.Rows.IndexOf(row))(“Number”) = no.ToString(“D8”) —> to make that value 8 digit. at particular position.

I’m Attching my workflow here for your better understanding,
Workflow : digit8.zip (17.8 KB)
:slight_smile:

8

Hi,

Simple on is you can use PadLeft function.

Here is syntax of same:
StringVariable.padleft(8,"0"c)

Please let me know if more info needed.

9

@samir thanks for your help.I am getting this issue but this column is present I don’t why this throwing

Untitled
Untitled1440Ă—900 65.1 KB
me error

10

@Aditya10989 make sure you’ve checked the checkBox of AddHeaders property of Read Range activity

new1

11

@samir yes I already did this but not working

12

@samir Shall I share code with you can you see this…??

13

@Aditya10989 make sure your excel columnName (header —>1st row) has the same columnName which you’re mentioned in assign (row(“columnName”).ToString).Length

Sure, but have you gone through the zip I’d attached before. ?

14

@samirtest.zip (143.1 KB)

15

hey @Aditya10989 if you check the column header in your sheet is " Case ID3"
there’s 1 blank space at the start of the header, so i’ll suggest you to copy the columnName from query (without having space) and paste in your sheet at that specific header position, save it and run it, I’d done the same and its working.

OR

you can simply pass the index of the column as its 9th column the index should be 8 —> (row(8).ToString).Length

as you’re converted DataTable into Text with Output DataTable activity, can’t display it into message Box coz the datatable is way larger so i’ll suggest you to use write text File and write that text in notpad for your checking.

and have you gone through the zip I’ve attached in my 2nd post.

1 Like
16

Check if this helps.
String8numbers.zip (17.8 KB)

17

thanks for reply in below throwing a error inside if condition Activity could not be loaded because of missing xaml

18

@samir thanks for help now only thing is pending write data in cell in previous we use write cell in this we insert static string “Amount is greater then” but here I think we need to insert DTcheckData.Rows(DTcheckData.Rows.IndexOf(row))(8) when I pass this in value then getting error

19

@samir I try also this approcach convert to string then add to value in write cell it successfully executed but changes not reflect in excel

Untitled
Untitled1920Ă—1080 113 KB

20

@samir thanks actually its working but zero not showing before digits I change the format for excel then now its showing