Hi I have excel date like below,
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.
try using the LIKE operator. Dt.select using like
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
Try this and let know buddy @Tiberiu_Niculescu
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
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.
you can get the data rows with this itself buddy
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
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.
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
you can try this instead of Length
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
.count and .length gives me same output
Is there a possibility to share your excel to see how exactly the data looks like in the excel
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)
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?
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
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?
Hope it helps
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.
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,
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,
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
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,
The for the 000006 record, I’m getting the below error
I’m very puzzled on this. Can help to analyse?
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?
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
- put try catch for assign activity
- first convert as data row then check using if condition datarow is empty or not after convert as datatable.
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.
Yes LINQ query is more reliable than select query. We can’t write complex queries into select method.