Any tricks of getting Cell number when using filtered table?


#1

Hi,

I’m trying to automate following steps;

Assume there is an Excel file and it has 3 columns, “date”, “customer number” and “status”

  1. Filter the Excel table using “date” column (say if it’s today)
  2. Get value of “customer number” column of each row and search the customer info on CRM system
  3. Get customer status info from CRM system screen and write it into “status” column

As to step no. 1 and 2, I was able to automate them by using “Filter Table”, “Read Range (enabled UseFilter option)”, “For Each Row”, “Get Row Item” and some other activities.

However, now I don’t know how to write “status” on a specific row.
Does anyone have any ideas?

*I think I can know row number if I give up using Fillter Table and use array to find rows which matches certain condition. But I don’t want to since I know it takes much more time; the Excel file contains about 4000 rows and about 200 rows will be processed in a day. It took more than 50 mins to just check “date” column of 3800 rows.


#2

Hello,
First To filter you datatable for today’s date you could use:

If your date is a string (i’m assuming the format):
dt.Select("[date]=’" & Now.ToString(“dd/MM/yyyy”)&"’")

if you date has the datatype date, then you would rather use daterange. Hereunder is an example for the last 7 days

dt.Select(string.Format("[date] >= ‘{0}’ And [date] <= ‘{1}’",now.AddDays(-7).ToString(System.Globalization.CultureInfo.InvariantCulture),now.ToString(System.Globalization.CultureInfo.InvariantCulture)))

Both examples are to put inside an Assign Activity to a variable of type : DateRow() aka Array of datarow

Then you can loop trough the array of Row (with a For each activity, not a For each row) doing the following.

Look for status in my CRM --> Assign it to varStatusFromMyCrm -->Use Assign Activity with Row(“status”) = varStatusFromMyCrm.toString().

I believe that should do the trick.

Note that even if the Rows are part of the array, they are still part of the Datatable as well since both the datatable rows and the items in the array point to the same object references (the todays rows). It means that the value will be changed at both places.

Cheers.


#3

Thanks!

By using DataTable.Select method I was able to get filtered data.
Then I got row number of the row in the original datatable by using IndexOf method and was able to write status into correct cell!!

In addition, “use for each, not for each row” for array is really helpful info for rookies like me :wink: