Filling missing values in Excel

excel
activities

#1

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)


Selecting a row from excel from a particular ramge of date
#2

@aamir

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

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


#3

Hi Mahesh,

i tried with this. Its throwing an error.



#4

@aamir,

Instead of above, use this datatable variable.Rows(datatablevariable.Rows.IndexOf(row)-1)(“Date”)

Regards,
Dominic :slight_smile:


#5

if u completed can you share this task xmal files or upload here


#6

@aamir

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


#7

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.


#8

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.


#9

@aamir
Use write range after updating values

Regards,
Mahesh


#10

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


#11

@arivu96

If the first row is empty and if you try to access the previous row then it will throw an exception…

Regards,
Mahesh


#12

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


#13

@arivu96

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


#14

No problem, @MAHESH1

Regard,
Arivu


#15

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.


#16

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.


#17

@aamir

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


#18

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.


#19

@aamir

Sure If I get any solution, Definitely I will let you know.

Regards,
Mahesh.


#20

Hi @aamir,

i have modified few things, please use this xaml file.

Main.xaml (12.7 KB)

Regards,
Arivu