# Excel data insert

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

1 Like

@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.

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.

Iâ€™ll suggest you to follow these steps:

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.

2 Likes

@samir Thanks for reply I am getting error

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)

Hi,

Simple on is you can use PadLeft function.

Here is syntax of same:

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

me error

@samir yes I already did this but not working

@samir Shall I share code with you can you see thisâ€¦??

@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. ?

@samirtest.zip (143.1 KB)

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

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

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

@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

@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

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