Hi everyone,
i have an excel with a date column in it at certain rows it has date value in it and at certain rows date value is missing i have to fill those missing values in excel. i dont have any idea how to do it. can anybody help me in this?
i am attaching an excel for the same.GL.xls (1.9 MB)
2 Likes
1.First Read the excel sheet and store it in a datatable.
2.Then use foreach row activity
3.Inside for each row, add one if condition
- In If condition - not datatablevariable.Rows.IndexOf(row).equals(0) and string.IsEmpty(row(“Date”).Tostring)
5.inside that use one Assign Activity
6.Assign activity - row(“Date”)=row((datatablevariable.Rows.IndexOf(row))-1)(“Date”)
Regards
Mahesh
1 Like
Instead of above, use this datatable variable.Rows(datatablevariable.Rows.IndexOf(row)-1)(“Date”)
Regards,
Dominic 
1 Like
if u completed can you share this task xmal files or upload here
1 Like
Sorry I failed to mention datatable variable in assign activitry.
Use this in assign activity - datatable variable.Rows(datatablevariable.Rows.IndexOf(row)-1)(“Date”)
It will work
thank you @Dominic
Regards
Mahesh
1 Like
Hi Mahesh,
This time it ran without thrwoing any error but its not written anything in my excel my excel is same as before not changed how should i write this and make changes. in my excel.
1 Like
HI dominic,
This time it ran without throwing any error but its not written anything in my excel my excel is same as before not changed how should i write this and make changes. in my excel File.
1 Like
@aamir
Use write range after updating values
Regards,
Mahesh
1 Like
Hi @aamir,
First using read range activity store int into datatable, then loop through it if the date field is empty update the previous date field value.
Refer the below xaml file.
Main.xaml (12.4 KB)
Regards,
Arivu
1 Like
If the first row is empty and if you try to access the previous row then it will throw an exception..
Regards,
Mahesh
1 Like
Hi @MAHESH1,
please verify the xaml file, i checked that condition also if it’s empty first row until gate find it ll not write the date value in the upcoming rows. once find the value then next field i empty it ll write the date value in that field.,
Regards,
Arivu
1 Like
Oh sorry I haven’t seen the xaml, I saw your post, there you just mentioned “loop through it if the date field is empty” so I thought that you might have missed to check that condition.
Regards,
Mahesh
1 Like
No problem, @MAHESH1
Regard,
Arivu
1 Like
Hi Mahesh,
The solution which you gave i wrote the same solution and wrote write range to do this but still the code is running from the last 1 and half hour and still its not finished yet.
1 Like
Hi Mahesh,
i stooped the automation and checked that it filled around 300 rows for column date till now and total there are 10k rows and for 3k its taking 1 hour 40min.
Is there any other way to do this so that time can be saved.
1 Like
If it’s having 10K rows, then definitely it will take time.
I think to update the values, there is no other method, If any one aware about other methods please post here, it will help every one.
Regards,
Mahesh
1 Like
Hi Mahesh,
First of all thank you so much for helping me alot and yes i tried the solution given by you but its taking too much time for 3000 rows to fill the data it took around 1hour 40min but it has around 10k rows so that might take up more time just to fill this. Is there any other way to do the same thing so that i can save time because the main purpose of this automation is to show that time is getting saved.
1 Like
1 Like