Extracting specific excel column data

Help
, , ,
1

Hi there,

I’m having problem in extracting data from specific excel sheet columns.
This is what I want to achieve:
If Column “Department” has value “IT” and column “Designation” contains any of these string- “Developer” or “Software Developer”.
If condition is true copy that particular data (sorted data based on above filter condition) into separate file.

2

@samarth_p

You can use filter datatable activity. Or else you can try like this

Your newdatatable=dt.Select(“[Department]=‘IT’ And [Designation]=‘Developer’ OR [Designation]=‘Software Developer’”). copytodatable

2 Likes
3

Getting this error: Assign: Syntax error: Missing operand after ‘Status’’ operator.

4

Hi @aman_sheik thanks for your help.
As you said I used filter datatable activity. As I want to extract data from multiple files based on above filter, there are 5 files, if a file does not have any matching records (based on filter datatable condition), I need to create a separate file File Not Found and copy the name of empty file in the sheet. What should I do to achieve this? I think there is some problem in my if condition.
As I’m a new user I cant upload my xaml file for your reference.

5

@samarth_p

You can check if condition (if activity) filterdatatable.rows.count>0 then you have some data after filter then you can update asusually what you are doing.

Else you create a file Excel application scope and create the file and in write range or write cell activity you can write item.getfilename +“has no data”

Item.getfilename > I hope anyway ur looping for each file there you can give like this (item.getfilename) by using that loop

Hope this helps!!!

Thanks,
Aman Sheik.

6

I need to achieve this:

  1. If Column " Item Changed " is “Employee Status” and column " New Value " contains Hire or Rehire

  2. If Column " Item Changed " is “Full/Part Time Status” and column " New Value " could be “Full Time, Part Time or Part Time - Casual”.
    This how my filter looks like:
    filter

Is it right or wrong?

And based on this I have to copy that particular data into separate file. (what should be the if condition, else copy the name of empty file in the sheet.

7

Try this

IF Condition:

outputDt.AsEnumerable.Where(Function(row As DataRow) (row.Item(“ITEM CHANGED”).ToString.Equals(“Employee Status”) And (row.Item(“NEW VALUE”).ToString.Equals(“Hire”) Or row.Item(“NEW VALUE”).ToString.Equals(“Rehire”))) Or (row.Item(“ITEM CHANGED”).ToString.Equals(“Full/Part Time Status”) And (row.Item(“NEW VALUE”).ToString.Equals(“Full Time”) Or row.Item(“NEW VALUE”).ToString.Equals(“Part Time”)Or row.Item(“NEW VALUE”).ToString.Equals(“Part Time - Casual”)))).Any()

Copy to newDT based on filter:

outputDt.AsEnumerable.Where(Function(row As DataRow) (row.Item(“ITEM CHANGED”).ToString.Equals(“Employee Status”) And (row.Item(“NEW VALUE”).ToString.Equals(“Hire”) Or row.Item(“NEW VALUE”).ToString.Equals(“Rehire”))) Or (row.Item(“ITEM CHANGED”).ToString.Equals(“Full/Part Time Status”) And (row.Item(“NEW VALUE”).ToString.Equals(“Full Time”) Or row.Item(“NEW VALUE”).ToString.Equals(“Part Time”)Or row.Item(“NEW VALUE”).ToString.Equals(“Part Time - Casual”)))).CopyToDataTable()

image
image804×273 9.12 KB

8

This one will work. You can use this @samarth_p :slight_smile:

Kudos to @aman_sheik

1 Like