Activity to compare 1 random string with fixed string in Excel

[Gokuss7 (Andre Roessner)]

Hi Guy’s,

I have been trying to get the following done and need help on the logic, since I can’t find one. Here comes the issue described:

I have a request from many users who provide me a fixed location and a folder path within a ticket, my project gets this info from the ticket and saves it to a variable. The location is pretty standard and with some trimming I was able to get always the proper city name out of it, but the folder path is a bit of an issue, since the user can user the input and put whatever they want ( Not possible to standardize ), as of an example, it can be looking like any of this:

Y:/192.168.1.1/ADMINISTRATIVE/OPERATIONS/INVENTORY
\192.168.1.1\ADMINISTRATIVE\OPERATIONS\FINANCES
\OPERATIONS\INVENTORY
\FINANCES

This information can be anything similar to what I have provided before as example, but the issue is that I was provided with an excel file where I have to first look within the location column ( that is easy ) and then based on the matching location by applying a filter which only lefts the rows for this location visible, there could be in between 1-30 possible folder existing from which I have the exact folder path as of example here:

\DEDSFDSFFEFWE\DEPTOS$
\DEDSFDSFFEFWE\DEPTOS$\ADMINISTRATIVE\OPERATIONS\INVENTORY
\DEDSFDSFFEFWE\DEPTOS$\ADMINISTRATIVE\OPERATIONS\FINANCES
\DEDSFDSFFEFWE\DEPTOS$\ADMINISTRATIVE\FINANCES

The issue here is the folder path is almost never the same as in the provided excel file or contains more/less info as IP Address, DEPTOS$, Path letter or simbols /,:.

Is there a way to indicate the robot to filter/strip/etc the folder path in a way so it can counter check with the excel and find proper path if correct keyword by user was provided?

Ideas so far:

• Create a list/excel where I add specific strings which should be stripped from the folder path before checking with excel file to see if path can be found ( would not always be correct since I will not be able to anticipate each of the not needed characters a user can type into )
• strip always all the values and only leave the last 2 to check against the Excel, but this would also get sometimes errors on how to find the proper path if the user enters it incorrectly, example:

Before: Y:/192.168.1.1/ADMINISTRATIVE/OPERATIONS/INVENTORY
After: OPERATIONS/INVENTORY

So not sure if there is any other better solution I am not seeing on how to solve this, the best would be to obligate the user to only be able to select the path from a drop down list, based on their available option on their location, but since this is not possible ( already asked ). Hope someone can help me with an idea or even provide a workflow I could try and see if that works!?

Thank you for the help in advance!

[msan (Michel SAN)]

Hello,

Here some thoughts in the middle of the ngiht on your question, I hope they will help you.

• If on Windows only, you might want to use String.Normalize before comparing strings.

• I didn’t take into account the possibility of multiple path per occurence

• You can split the string as “parts” with regex and try to match any known path from the end.

• You want to check your comparisons are exclusive, for example don’t compare just to FINANCES at the end. Your last example can match two cases.

• Maybe reversing the parts order can help with comparison (< and >) and finding good control values. I can elaborate if you need to.
  "FINANCES/ADMINISTRATIVE" <= "FINANCES/OPERATIONS" <= "FINANCES/OPERATIONS"

An approach for cleaning:

Assign (String)
path = path.Trim.TrimEnd({"/"c, "\"c}.TrimEnd

Assign (as Array of String)
parts = System.Text.RegularExpressions.Regex.Split(path, "\s*[\\\/]+\s*")

Assign (as String)
String.Join("/", parts)

[Gokuss7 (Andre Roessner)]

Thank you very much for the help I was able to adapted this to my project, still not the solution I wanted, but based on what I was explained there is no 100% solution for this issue! Reason: folder path data is way more complex and big in variety then what I indicated in the description of the issue, but thanks a lot!