Find and replace by dictionary

Help Studio
,
1

Hi all!
Is it possible to pass the dictionary keys to the Find and Replace activity to compare the keys with the values ​​in the excel file and then replace?
For example, in a dictionary, the key-value pair is 0333 - Home. In the excel file, a line of this type: 0333; Street 5. If the key in the dictionary matches the value in the excel file, then the replacement is also at the output: Home; Street 5.

1 Like
2

If yourDic.Keys.Contains(“0333”) then yourDic(“0333”) = “Street 5”

3

Let me explain. In excel cells the following is written: 0333; Street 5.
It is necessary that in the agreement with the key from the dictionary (if it matches the value in the exсel cell) - replace it with the value from the dictionary. Was: 0333; Street 5, became Home; Street 5.

4

Hi,

Can you try the following?

img20201204-6
img20201204-6670×846 17.7 KB

Regards,

1 Like
5

Hi!
Created a project, where from the Config.xlsx file - add values ​​to the dictionary. It is then necessary to compare the keys value in the present with the values ​​in the excel file ExtractXML and replace if they match. For example: the key “002001002000” matches the value in the ExtractXml file, which means you need to replace it with “Здание”. Please tell me how to modify the project to make it work. Thanks.
Add to Dictionary and compare replace.zip (33.4 KB)
example

6

Hi,

I got you requirement. How about the following?

img20201204-7
img20201204-7704×718 17.8 KB

Add to Dictionary and compare replaceV2.zip (29.9 KB)

Regards,

1 Like
7

Thanks, but how to make it change the values ​​in all columns? Change parameters in row?

8

I think need to create an index variable with an initial value of 0, add it to row (index), then in the for each loop, increase the value and so on to 7 (8 columns, but counting from zero).
Please look at the xaml and tell me what the error is. Thanks.
Main.xaml (13.5 KB)

9

Hi,

Perhaps you should use not index but to iterate columns as the following.
Hope this helps you.

img20201204-A
img20201204-A747×663 18.2 KB

Add to Dictionary and compare replaceV3.zip (30.1 KB)

Regards,

1 Like
10

Awesome! Thank you very much, very helpful.

1 Like
11

And if I want to replace all matches. How do I return more than one item?
Change in expression: Out_Config.SingleOrDefault (Function (d) row (item) .ToString.Contains (d.Key)). Key ?
Thanks.

12

Hi,

Can you try the following?

img20201207-2
img20201207-2865×765 22.5 KB

Regards,

13

Did as shown, but errors occur. Look please. Thanks.
Main.xaml (11.4 KB)

14

Hi,

I haven’t test the following yet. But it helps you.

Main.xaml (12.9 KB)

Regards,

15

An error occurs:
If: Action “1.12: VisualBasicValue ” does not have access to this public location reference because it is only valid for action “1.31: VisualBasicValue <IEnumerable >”. A link to a public location can only be used by the action that receives it.
What could be the problem? In data types?

16

Hi,
Sorry、I had mistake. Can you add ToList method as the following?

img20201207-3

Regards,

1 Like
17

Thanks a lot, everything works great.

1 Like
18

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.