Check if value in a cell exists, if it does strip unnecesary data

I have to two columns - Credit, Debit.

If a value is found in both columns, I’d like to remove: commas, dashes, full stops

Cndition I am using is not working: isNothing(row(“Credit”)) OrElse String.IsNullOrEmpty(row(“Debit”).toString.Replace("-", String.Empty).Replace(",", String.Empty).Replace(".", String.Empty).Trim)

Results:

What would be the best way I can achieve this?

Second question - Where can i find datatype: Dictionary<T,T>.KeyCollection, I have looked in browse for types

Hi @Mpumi_Setshedi

For the debit, you could add into If condition

row(“Debit”).ToString.IsMatch("[0-9]+.[0-9]+")

Checking for only numbers, a decimal point and numbers

For second question, are you looking for this

Hi @GreenTea,

I’m attempting to to remove all charcters from the numbers, just leaving the number plain for both columns

For they second question, yes i think so, I have a variable called KeyCollection, i’d like to save
the dictionary keys in that variable

Hi @Mpumi_Setshedi

For retaining only numbers for both columns you can use

  1. Replace activity

Input is row(“Debit”).ToString.Trim
Pattern is “[\,\.\p{Zs}]*”
In the output, ctrl + k to create a string variable, let’s say strDebit which is a value you want
Replacement is String.Empty

Regex pattern is looking for 0 or more

  • comma, dot or space

Repeat for “Credit”

Hi @GreenTea

Thank you for the reponse, just tested it and it is working perfectly

Is there a way I could add Dashes(-) to this pattern

Hi @Mpumi_Setshedi

You can do this with single line code in invoke code activity itself

dt1.AsEnumerable().Where(Function(row) row(“Credit”).ToString<>"" and row(“Debit”).ToString<>"").ToList().ForEach(Sub(r1) r1.ItemArray={System.Text.RegularExpressions.Regex.Replace(r1(“Credit”).ToString,"[^0-9]+","").ToString,System.Text.RegularExpressions.Regex.Replace(r1(“Debit”).ToString,"[^0-9]+","").ToString})

I am assuming your excel had two columns Debit and credit

Regards,
Nived N
Happy Automation

Hi @NIVED_NAMBIAR

I have a dictionary: Key(Amount), Value(FilePath)

The columns I have in excel - Debit, Credit are amounts, I want to match the amount from excel and find in the dictionary key. I need to match it number sequence wise example:

excel value: 225776
dictionary key value: (225776)90

If there is a match I want to update row(“Confirmation”) with “Yes”, then write in the file path which is saved is the dictionary value into row(“File Path”)

I am having trouble, any assistance would be appreciated

REALData.xlsx (10.8 KB)
DictionaryProcessingV1.xaml (20.5 KB)

I was following this piece of code:
Use Dictionary.Keys to get a list of all Keys into a collection
Example: keyColl = YourDictionary.Keys

'Loop through Excel Rows
For Each row in DataTable_Excel
'Get value of Excel Cell
cellValue = row(“column_name”).ToString()
'Check if the Excel value is contained in the Dictionary Key collection using the Contains function
'There are ways to do it using Lambda functions, but for now you may have to loop through the key collection

For each k in keyColl
if k.Contains(cellValue)= True then
get the filePath value of that key from your dictionary
filePath = YourDictionary(k)
'Write filePath to your Excel Cell in current row
'Write Confirmation to your Excel Cell in current row
end if
End For
End For

Hi @Mpumi_Setshedi

To include dash, your pattern will be
“[\-\,\.\p{Zs}]*”

Hi @Mpumi_Setshedi

You can also use generic.dictionary.ContainsKey or generic.dictionary.TryGetValue

Without having to extract the keys into key list just to check if key exist in key list, you can use above methods.

Hi @GreenTea

Just want to make sure I understood the use of this correctly:
If my value is: 1923 from excel, would it match 000192300 which is in my Dict Key, I understand string wise those are different values, just trying to match the number sequence


Use Dictionary<String, String> as the data type for the For Each Loop

Hi @Mpumi_Setshedi

Please see changes:

  • TypeArgument set to String dataType to match your dictionary key dataType
  • Since you are already iterating through keys which is a String use .Contains method

HI @GreenTea

I hope you had a great weekend and thank you for your response.

I have manage to incorporate the logic you have shared, Im getting a match message box.

Sequence is:
For each data table
if - Checks if there is an empty cell or not
formats value(Matches) → strDebit output
for each - loops through keys
if → item contains string debit
assign → writing “yes” to column, It seems not to be writing, is it because the assign is in a normal For Each loop?

as the loop goes in order as the excel value, would I have to implement for the loop to iterate again to look for the value, while dismissing values that have already been found

Hi @Mpumi_Setshedi

Are you trying to update a field in a DataTable while at the same time also that use the same DataTable in For Each Row activity? If yes, you cannot do that.

First, copy the original DataTable then use the copied DataTable in for each row while the original DataTable for updating.

Hi @GreenTea

Okay got it.
After the read range I’ll DT2 = DT1.copy() this will be the DT i use in the For Each Row

Then in the first For each(for the dictionary), reference the original DT insinde the For Each