Removing excel junk data and selecting only required rows


#1

Hi all

I am a beginner in UI path.
I have got a excel file with first many rows(say 25) as junk data.
I want to select only useful data and operate on it.
as the useful data are starting from row 26 how do i program this in the ui path.
Any help would be appreciated.

Thanks in advance.


#2

@Shivamvats1994

How will you identify theJunk rows, I mean you are telling that first 25 rows are junk rows, will this number remains same always.

Regards,
Mahesh


#3

Hi @Shivamvats1994

If the cell from which the useful data starts remains the same, for instance B26, just use read range activity in excel application scope and put “B26” in the range property.


#4

hey mahesh

no its dynamic… like lets say first 50 rows or any number for that matter has junk data and next we have normal table like Name age etc which we want to work on.


#5

hey it doesn’t remains same and its dynamic . so what could be possible solution.?


#6

@Shivamvats1994,

is there any way you can know till how many rows u will have junk data.

i mean like a keyword or some line, where it stops and you need start after that…?


#7

we know that the data we want starts from a particular row … but it’s better if the program finds it dynamically…
for eg we have some junk data and then we have our data we want to work on… is there a way to search for a row containing headers such as name age etc. ?

if we could search for the row containing these fields may be from there on we can work on our way ahead .

Thanks


#8

can you attach one sample file…?


#9

is there a way to search for a row containing headers such as name age etc. ?

@Shivamvats1994

you can do like this…

1.create a datatable DT1 with fields as you mentioned like name,age etc…

2.create datatable DT2 from excel file

3.now use for each row on DT2 to find the matching row with DT1.

  1. once you find thats your data

thanks
Yogananda


#10

To better understand the problem, we need some dummy data from you and your workflow. If there’s any keyword that lets us know where the row you need starts from, we can use that and exclude the the unwanted data.


#11

https://drive.google.com/file/d/1Rc2x3oIDC6ozIyEkvOcumlRtcSpZ8Jcp/view?usp=sharing

This is a sample file i need to work upon and update the status ok for all valid rows and throw exception for the value “Doctor”


#12

Here is the workflow. Let me know if this solves your problem.RemoveJunkFromExcelData.zip (9.6 KB)


#13

Thanks siddharth for the workflow… really appreciate your help. Will get back to you in case of more queries :slight_smile:


#14

hi sid

is there a way we can do the same thing using for each row method ?