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
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
For retaining only numbers for both columns you can use
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
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
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
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
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
Please see changes:
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
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
Anil_G
ashokkarale
jinal.shah
Gautham_Pattabiraman
postwick
chandreshsinh.jadeja
vrdabberu
Ajay_Mishra
sven.wullum1
Vyshnavi_Nalumachu