Any tricks of getting Cell number when using filtered table?

Help
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.

2 Likes
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:

1 Like
4

Hi…I need to select all rows and columns in a datatable from todays date to the end and paste it in other excel…can you please help me with the same? I am stuck at a place where i need to find row and column cell as todays date can appear at any place on the sheet

5

Hi @Mchande,

You need to use the same select statement with multiple conditions for every column.

Like dt.Select(“[Column1]=’” & Now.ToString(“dd/MM/yyyy”)&“’
AND [Column2]=’” & Now.ToString(“dd/MM/yyyy”)&“’”) :grinning:

6

Hi @Vivek_Arunagiri thanks for replying and apologies for not being clear. I have 7 columns in a datatable. Only the first column is a date column so based on today’s date i need to select all the data from that row to the end. For eg: below
Row 1 to 10 with 7 columns
2nd row First column has today’s date(10/15). So i need to select rows 2 - 10
Tomorrow for that datatable, the the date column will have 10/16 on row 3. So i need to select rows 3-10

7

If you want to get the rows with date greater than today’s date, please check the below link.

https://forum.uipath.com/t/date-condition/19830/7?u=vivek_nalashaa

1 Like
8

Hi @Vivek_Arunagiri …quick question…I have a data table that i filtered and it became a datarow (In the screenshot, I am trying to filter based on date)…the other columns earlier had values in Percentage for e.g. 76.5% but now it shows as 0.765. Any idea how to keep the original persisted?

image
image810×300 21.5 KB

9

In Excel file, Format the data as “Text”.

10

Hi Vivek…I need to perform further actions on the filtered datatable/datarow…is there a way to achieve “format data as text” in uipath?