Retrieving Whole Row Data using Specific Cell data in Excel

[Ishan_Shelke1 (Ishan Shelke)]

I have a table like this

What I need to do is I need to fetch the whole row in excel wherever the date 18 May is appearing

PS. Table has no headers to it

Any Suggestions ?

[ppr (Peter Preuss)]

in general it is a filter task on Excel / Datatable after read range
for missing headers we can use the column index in case of datatable

[Ishan_Shelke1 (Ishan Shelke)]

Exactly. Can You provide me a solution to this ?

[ptrobot]

Try this with an Assign activity:

dtFiltered =

(From i In {0}
	Let matchingRows = dt.AsEnumerable.Where(Function(r) r(1).ToString.Trim.StartsWith("18 May"))
	Select If (matchingRows.Count > 0, matchingRows.CopyToDataTable, dt.Clone)
).First

[jack.chan]

add 2 columns, Day and Month column then
assign Day = day of 2nd column
assign month = month of 2nd column

then filter datatable where day=18, month=5

testt.xaml (9.5 KB)

[Ishan_Shelke1 (Ishan Shelke)]

Getting output like this.

[ptrobot]

That seems to be just the headers (i.e. no matching rows). Is the date not always in the second column?

[Ishan_Shelke1 (Ishan Shelke)]

No it’s not, I have Multiple date columns

[jack.chan]

if you dont want additional columns,

assign
filteredDt = dt.AsEnumerable.Where(function(row) cdate(row("Column1").ToString).Day = 18 and cdate(row("Column1").ToString).Month = 5).CopyToDataTable

[ptrobot]

If the date can be in any column, then the expression should be:

(From i In {0}
	Let matchingRows = dt.AsEnumerable.Where(Function(r) r.ItemArray.Any(Function(x) x.ToString.Trim.StartsWith("18 May")) )
	Select If (matchingRows.Count > 0, matchingRows.CopyToDataTable, dt.Clone)
).First

[Rahul_Unnikrishnan]

Hello @Ishan_Shelke1 ,

You can do as below. WHile using read range disable “Has Header”.

[Ishan_Shelke1 (Ishan Shelke)]

Well I got the Output with this Only thing is I don’t want the time (00:00:00) in this.

Also What If My date column is random. So what code should I put in that case ?

[jack.chan]

create dateColumnName string variable
then loop over all the columns, if 1st row of column contains date, then that column = dateColumnName

then loop over datatable and remove 00:00:00 for dateColumnName
replace Column1 with dateColumnName too

refer to this
find date column and remove 0.xaml (11.2 KB)