Check digit number of a column

Help Activities
, ,
1

Hey everyone,

I have datatable, and there is column named “Agreement Number”, this column contains 10 digit number or at least it should contain 10 digit number.

I want to create a checking mechanism to check the digit number of rows. I dont want to use for each since there are lots of rows, is there a solution without for each activity?

Because What I want to do is check the columns cells, if there is any cell which its digit number is not equal to 10, send outlook mail, basically.

Thank you,

2

@jntrk - could please share any sample data? We can do without for each loop and using regex we can check the digits too…

3

This isn’t the best solution, but assuming it is always just numbers (and there are no leading zeros) you could use a filter activity to identify any number under 1,000,000,000 and output that as a separate data table, containing only the troublesome rows.

You could likely work around the leading zero issue with several more “OR” filters (e.g., if starts with 0 and < 999,999,999), etc.

4

@Bobby_J - Not sure what you mean…

@jntrk - Please have a look here…using the Invoke code and Regex Match…

image
image1628×744 45.3 KB

Invoke Code

dt.AsEnumerable().ToList().ForEach(Sub(row) row("DigitCheck")=system.text.RegularExpressions.Regex.Match(row("ID").ToString,"\b\d{10,}\b").Success.ToString)

Regex

image

Hope this helps…

5

For Each is the best solution for this, I’m not sure why you want to avoid it. If you just wanted to find those rows you could use a Select query to find them, but since you also want to send an email you should use a For Each, check the length of the value converted to string in the clause of an If, and send an email if it’s under 10 in length.

6

@postwick - Not OP, but unfortunately I’ve found for-each to run quite slow sometimes, even though it should be the correct solution. Maxing 1-2 cores for a 5k row data table for 5+ minutes when it takes Excel 2 seconds to do the equivalent calculations is… bad.

@prasath17 - If the agreement number must be at 10 digits, the universe of valid agreement numbers is 1,000,000,000 - 9,999,999,999 (assuming 0,000,000,001 isn’t valid). If that holds, a quick filter on anything less than a billion (or >= 10B) is valid. I wouldn’t call it good code since it only works for a very specific set of criteria, but since I can’t just will good code into existence like you, I’ve got to use workarounds :slight_smile:

7

First of all, there won’t be any commas in the agreement number(similar to invoice #)…please read his requirement again, he clearly explained "this column contains 10 digit number or at least it should contain 10 digit number." Regex can easily identify if its 10 digits or less than 10 digits…(refer the screenshot provided)

Do you know, what that invoke code does?? if yes, pleas explain or if you do not understand i would suggest better don’t comment…

Also, another requirement user provided was no for each loop…so there is only two option either through LINQ or Invoke Code…These 2 handles large data and process within few seconds…

8

I think you missed Bobby’s point. Any 10 digit number (if stored as a number, not text) will be the value 1000000000 or higher. You couldn’t have 0999999999 because stored as a number, leading zeroes are not maintained.

9

Hi @postwick - Based on the how the # is stored in excel, we can first print it and make sure whether it is printing in what format…based on that we can easily turn on/off Preserve Format…

However it is based on the data, we can easily adjust the Regex in the invoke code…

10

I only inserted commas for readability. I was stating my solution would work in a limited subset of cases - hence my code isn’t great code, more of a hack that could work.

Your code is much more robust, and I am always impressed by those who can solve problems in minutes that would take me an hour or more for a less elegant solution. One day I’ll be there.

As far as understanding your code (again as a learning exercise), I think I do except for the “Sub(row) row” part - that syntax is throwing me. Is that creating a subroutine, and is it really that easy to do?

11

The OP said datatable, not Excel spreadsheet.

12

@postwick - datatable come from the spreadsheet…

I would let the OP to speak if anything incorrect with the approach which I shared :wink:

13

Doesn’t matter where the data in the datatable came from. It’s now a datatable. Assuming the value is stored as a number, as the OP indicated, the best way to handle this is with a For Each or a Select looking for values below 1000000000.

14

he is looking without For Each…

Yes, we can filter the data doesn’t meet the criteria using string Manipulation or Filtering also…

15

Hi @jntrk
You can use the linq query to filter the rows whose cell does not less had more than 10 digits
after reading datatable and storing in dt1

use the below assign activity

dt2 = dt1.AsEnumerable().Where(Function(row) row(“Agreement Number”).ToString.Length<10).CopyToDataTable

It will filter the rows having the numbers of digits under Agreement Number column below 10.

Note: I assume that there is only digits in Agreement Number column.

Regards,
Nived N
Happy Automation

1 Like