Datatable select query not giving correct result

Help
1

Hi I have excel date like below,

ID First Name Middle Name Profession Email
100093 James Agnes Kong Teacher james@xyz.com
100093 James Agnes Kong Teacher james@xyz.com
100579 Kevin Tan Engineer kevin@xyz.com
100579 Kevin Tan Engineer kevin@xyz.com
100579 Kevin Tan Engineer kevin@xyz.com
100579 Kevin Tan Engineer kevin@xyz.com
100579 Kevin Tan Engineer kevin@xyz.com
100579 Kevin Tan Engineer kevin@xyz.com
100579 Kevin Tan Engineer kevin@xyz.com
100579 Kevin Tan Engineer kevin@xyz.com
000005 Bill Wong Student bill@xyz.com
000003 Michael Ben Helper mike@xyz.com

I’m reading the whole sheet in read range and getting dt1 as output. When I use select query to table like below, the length is not correct for all the data. For example, if I use below query,

dt1.Select("[ID]=‘100093’ “).length it returns correctly as 2, but the same query doesn’t work for dt1.Select(”[ID]=‘100579’ ").length as it return only 6 and not 8. Also for 000005 it returns 0.

Is there anything else I’m missing? Also the when I output the datatable, I’m able to see the ID column like (000005) its in the excel file but when I use write range the leading zeros are gone and only 5 is written.

Hope to have some solution on this.

Thanks

2

try using the LIKE operator. Dt.select using like

3

Buddy what do you mean the length here buddy.
i hope you are talking about the length of the string like 10093, but even though its length is 5 but you meant like its showing correctly with 2

i didn’t get that buddy @Tiberiu_Niculescu
Kindly correct me if i have understood the query wrongly
and you select method looks perfect buddy

may be there might be some space in front or back…try to remove them with Trim like this
row(“ID”).ToString.Trim

Try this and let know buddy @Tiberiu_Niculescu
Cheers

4

HI @siszack

May be you are having a small space or a tab in front or at the end. Try to trim the column data before using the select command. Or else, you can use the like command in the select command.

If you are using the trim, you can use a for each row activity to trim the values in the command using row(“ID”).ToString.Trim

Hope it helps

1 Like
5

Hi Palaniyappan,

What I meant for the length is the number of datarows. I checked there are no white spaces and also I have used trim functions and its same.

6

you can get the data rows with this itself buddy
outdt.Rows.Count
once after the filtering with select
or you can use FILTER DATATABLE ACTIVITY buddy

with this activity you can pass the datatable you have and make the filter by mentioning the columnname and the value to be filtered with and finally you will get a datatable variable with filtered data and give it a name like outdt
now you can get the rows count like outdt.Rows.Count

Cheers

7

For this, you can use Like or “IN” MS SQL operator for the correct Out of this Database.

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

8

Hi @siszack

The best option would be to use the Filter Data Table activity for this kind of activities. IT would be the best option. This will return a filtered datatable. Then using that datatable you can get a row count which gives you the exact row count

To get the row count: FilteredDT.Rows.Count

Try this and see whether it works

9

hello @siszack

you can try this instead of Length

dt1.Select("[ID]=‘100093’ “).count

Thanks

10

Hi @Lahiru.Fernando @Palaniyappan,

What I did was to get the datatable count by using dt1.Rows.count and then I use another read range activity to specify the ID column range like A1:dt1.Rows.count and then get the output dt2.

I then use remove duplicate rows activity to remove the duplicate ID values from dt2.

Then I use for each row in dt2 and filter the earlier datatable dt1 by using select query like below

I get the row value like rowItem = row(“ID”).ToString.Trim

filteredDt = dt1.Select(“[ID]='”+rowItem+“'”).CopyToDataTable

But for some ID values, I get no datarows found error. Thats why I wanted to check the length of the datarows initially

11

.count and .length gives me same output

12

@siszack

Is there a possibility to share your excel to see how exactly the data looks like in the excel

13

Hi @Lahiru.Fernando

I have attached two files both are same data but in a different format. For me testdata1 looks to work and the other one doesn’t work.

testdata1.xlsx (18.4 KB)
testdata2.xlsx (27.4 KB)

14

Is there any formatting issue in the excel? Looks like the ID column in testdata1 is text and format and testdata2 is in special format? Does this have any impact?

15

Hi @siszack

Yes… It is a formatting issue. I checked both files and in testdata1, the format is correct, but in testdata2, its not.

So the thing we need to remember about excel is, When UiPath is working with excel, it does not count the way the values are displayed to us in excel. it counts on the exact value in the cell. So a value may be displayed to us as “00005” using formatting, but the exact value is “5”. Get the idea?

In such case, if we look for “00005”, there is no such value. but if we look for “5”, there is. So it counts on the exact value, not the format it is shown in excel. So in testdata2, the values are different, but it shows in a same format as in testdata1. However, in testdata1, the value is exactly the one it is showing. That’s why testdata1 is working fine and testdata2 is not :slight_smile:

So, when working with excel, ignore the formatting done in excel. It does not help. We have to look for the exact value, then it will work fine…

Got the idea?

image
image712×399 26.3 KB

Hope it helps

16

Hi @Lahiru.Fernando ,

Strangely, I don’t get the error at row 8 or 9 as indicated by you (000005 is actually 5). Below is my sequence.

image
image679×503 10.7 KB

image
image
image565×557 11.8 KB

image

First getting the excel using excel app scope, then read range whole date sheet and output datatable as dtWholeDataSheet.

This is the output of the whole date sheet as DT,

image
image375×690 7.96 KB

Then I do a read range again the same data sheet but this I give the range as “A1:A”+dtWholeDataSheet.Rows.Count.toString and save the output dt as dtIDColumn

Below is the dtIDColumn output string,

image
image114×696 4.03 KB

I then do a remove duplicate rows activity to remove the duplicates from ID column and save the output as dtDistinctID, and below is the dtDistinctID

image

I then do for each row in dtDistinctID and get the row value as

rowItem = row(“ID”).toString

Then I filter the datatable dtWholeDataSheet using the below select,

dtFilteredData = dtWholeDataSheet.Select("[ID]=’ " +rowItem+ " ’ ").CopyToDataTable

I’m able to get correct output for some ID values like below,

image

image

image

image

image

The for the 000006 record, I’m getting the below error

image

I’m very puzzled on this. Can help to analyse?

Thanks

17

@siszack
Hmm… that’s very interesting. It should work… can you share your workflow file with me so that i can have a bit of a deep look on why it is not working for that particular record?

18

hi @siszack

that two excel files does’t contains 000006 row value…

if it is the case, it throw error only … for that u have two option

  1. put try catch for assign activity
  2. first convert as data row then check using if condition datarow is empty or not after convert as datatable.
19

Hi @Lahiru.Fernando,

Thanks for your time so far! I have managed to solve this by using LINQ query like below as suggested in various posts by @ClaytonM. Thanks a ton to him as well!

dtWholeDataSheet.AsEnumerable.Where(Function(row) row(“ID”).ToString.Trim= rowItem).CopyToDataTable

I still wonder why the select method is not reliable in this case. So is it safe to say we always use linq rather than going for Select. May @ClaytonM can help shed some ideas.

Thanks
Zackariya

1 Like
20

@siszack

Yes LINQ query is more reliable than select query. We can’t write complex queries into select method.

2 Likes