Extract information from Excel file "if" the conditions meet a criteria

[Jonalis]

Need help cos I m just 5 days old doing RPA. I have done READ Excel, then using a IF function set a condition to extract certain line items that meet the condition using WRITE RANGE. But when I run it, it gives a file with exactly the same information as the original. What did I do wrong ?

[Jan_Brian_Despi]

Hi @Jonalis
To loop through the rows of the data table, use For Each Row
Then use If activity (e.g row(“column1”).ToString = “Yes”)
Then add that data row to a different data table

Or you can also use Filter Data Table activity

[Lahiru.Fernando]

Hi @Jonalis

Welcome to the community!!

You have to follow the below steps.

1. Use a Read Range activity to read the data. On this activity undet properties output of this should be set to a datatable variable to hold the results

2. You have yo filter the results based on certain criteria right. So here, you can use Assign activity to filter the data as you need. Create another datatable variable to hold your filtered results. In the assign activity under To mention the new datatable. Under Value which stands next to it, specify your condition in below format. Lets assume you have two conditions.

Use this formula
DT_ExcelVariable.Select("[ColumnName] = 'Cat' And [ColumnName2] = 'dog'").CopyToDataTable

Now use a Write Range activity to write the data back to a excel file. For this excel write range, specify the new datatable that holds the filtered results

Hope this helps…

Please mark the answer as the solution if it works for you

[Jonalis]

For Point 2, how do I create another data table variable? Thank you for your help

[Jan_Brian_Despi]

Hi! Use a Build Data Table activity

[Jonalis]

I received this message. What do i need to do next?

[Jan_Brian_Despi]

Can you post the complete statement in the assign activity?

[Vijay_Kumar_C]

Hi @Jonalis

i guess you are filtering datatable by using Linq query. Can you please share the syntax for which you are trying to filter.

Missing operand after ‘Invoice’ operator ==> this will occur due to syntax. Please share the filter syntax.

Regards,
Vijay.

[Jonalis]

Here is the complete statement:

1.1 Sequence (Sequence)
	Private = False
	Variables
		ExpenseData(DataTable)
		Test2(DataTable)
	Activities
		1.19 Read Range (ReadRange)
			DataTable = ExpenseData
			AddHeaders = True
			PreserveFormat = False
			WorkbookPath = C:\Users\admin\Documents\UiPath\Expenses Tracking for Admin.xlsx
			SheetName = FY16-FY17 Expenses
			Private = False
		1.16 Assign (Assign)
			To = Test2
			Value = ExpenseData.Select(“Date Invoice Certified or REJECTED=‘Pending’”).CopyToDataTable
			Private = False
		1.2 Write Range (WriteRange)
			StartingCell = A1
			DataTable = Test2
			AddHeaders = False
			SheetName = Sheet1
			WorkbookPath = C:\Users\admin\Documents\UiPath\PendingInvoice3
			Private = False

[Vijay_Kumar_C]

@Jonalis - Got it.

In ExpenseData.Select(“[Date Invoice Certifiecate] = ‘abc’ or [REJECTED] = ‘Pending’”).CopyToDataTable.

please specify the column name in [ ] - Square brackets if it is having spaces in between.

Hope this will help you.

Regards,
Vijay.

[Jonalis]

Hi Robot Master
I still having problems

[Vijay_Kumar_C]

Hi @Jonalis

you are progressing. Now you have to change the Test variable datatype to Datatable. for that go to variables panel and select Test Variable and change the datatype from generic to datatable.

to select datatable type click on browse and type system.data.datatable and select the datatable option from the list.

This should solve your problem. if this works please mark this as solution.

Regards,
Vijay.

[Jonalis]

Robot Master
I have another problem now after updating the variable type

[Vijay_Kumar_C]

@Jonalis

With what value you are checking the column value. is it of double datatype. if it is please change it to string datatype by using .tostring.

Regards,
Vijay.

[Jonalis]

Robot Master, where do I update the using .ToString

[Vijay_Kumar_C]

@Jonalis
can you please share the Expensedata.select Linq query.

Regards,
Vijay.

[Jonalis]

Is this the one?

[Rahul24293 (Rahul Ramachandran)]

Please verify the format of the “Date Invoice” column’s type in excel either it should be in text format or Date format.

Hope this would resolve the issue.

[Vijay_Kumar_C]

@Jonalis

Yeah, what is the input value that you are trying to filter the [Date Invoice] column. If it is date the above syntax won’t work. Try to use below syntax

ExpenseData.Select(“[Date Invoiced] = #” + YourDateValue + “#”).CopyToDataTable

If you are having date values should specify it in between # #.

Hope this will help you.

Regards,
Vijay.