Using Config files in RPA

[Agnius]

Context

So, you have all probably heard of the multitude of various "best practices" in RPA that can be found online. Some of them are truly great, some are mediocre, and some are just copying and pasting those that are great (which is still cool, since they help spread the word).

Most of them will include a Config file of some sort. This is a file that is intended to make certain constant values be easier to maintain and update. It basically means storing constant values outside of the flow in some sort of a file that is easy to access and manage. This way, whenever a value needs to be changed, the flow does not actually need to be adjusted - instead, the values in the file can be modified.

 

Excel Config files

What I don't like with most suggestions on best practices is the use of Excel files as Config. People like using Excel, because it's easy: they're used to it - they've been using it daily for decades. But it is usually a pretty bad idea to use it for Config.

There are multiple reasons to not do it:

• It is inefficient: It requires opening the Excel app, reading the data, processing it, closing the app, etc. It actually takes a while to do all of that.
• It has potential for error where it is completely unnecessary: An Excel flow can break because of Excel itself failing, or because of the file already being open, or various other reasons.
• It is very easy to break formatting in Excel: It is especially easy to break numeric values and dates. And when people are involved (changing the constants), it is highly likely that they will break it.
• It requires an Excel license: I know - in most attended flow cases we will obviously have an Excel license. But in some cases where the flows are designed to run unattended on a service account on a VM, we might not even need an Excel license for the flow itself. But using an Excel config file makes it mandatory.
• It requires further processing for easy access: Values from an Excel file will usually be retrieved into a data table. And while data tables are nice, for config values, one would still usually define variables and need to access each value from the table. So, most best practice guides still recommend converting the table to a custom object via some scripting, which actually makes using it quite complex.

 

A (much) better alternative - JSON

I would thus always recommend using a plain text file with a JSON config inside it. It is very easy to use, it does not require an extra app and licenses to extract those values, it is less prone to error, much more efficient and does not require any scripting, as most RPA tools already have actions for parsing JSON.

There is one drawback of using JSON files for config - the syntax is quite sensitive. Some users without any tech/IT background may find it more complex to adjust those constants than they would if it was an Excel file. And it is also quite easy to break the syntax by forgetting a comma, a closing quotation mark, or not escaping special characters.

But once you get used to it, it works like a charm. Try it and you will never want to come back to using Excel config files anymore.

 

A sample JSON Config

This wouldn't be much of an article, if I tried proving JSON is better than Excel without really providing an actionable sample.

So, here's a sample Config file:

{
    "Recipient": {
        "Error": "support@domain.com",
        "Info": "name@domain.com",
        "Default": "support@domain.com"
    },
    "Logger": {
        "LogLevels": "TRACE,DEBUG,INFO,WARN,ERROR,FATAL",
        "ErrorLogLevels": "ERROR,TRACE,FATAL"
    },
    "MaxErrorCount": {
        "Excel": 10,
        "Web": 10,
        "WorkItem": 3
    },
    "Environment": "DEV",
    "ScreenshotsPath": "C:\\RPA\\Demo\\Flow\\Screenshots"
}

It's a very simplified version of what I generally use with my team when building projects for our clients. Ours will normally contain much more values in there, but this is the basics of what is necessary.

We would usually create a file like this for a single project that can consist of several automated flows and then have some nested configurations for each flow based on its name.

It is quite easy to read, and all we need to do in our flows is read the file, parse the JSON and then access the relevant properties.

For example, the following actions are what we use to read the config in Power Automate Desktop:

SET ProjectName TO $'''{ReplaceMe}'''
SET ProjectDirectory TO $'''C:\\RPA\\%ProjectName%'''
SET ConfigFile TO $'''%ProjectDirectory%\\Config\\Config.json'''
File.ReadTextFromFile.ReadText File: ConfigFile Encoding: File.TextFileEncoding.UTF8 Content=> Config_Object
Variables.ConvertJsonToCustomObject Json: Config_Object CustomObject=> Config_Object
SET Config_RecipientObject TO Config_Object['Recipient']
SET Config_LoggerObject TO Config_Object['Logger']
SET Config_MaxErrorCountObject TO Config_Object['MaxErrorCount']
SET Config_ScreenshotsPath TO Config_Object['ScreenshotsPath']
SET Config_Environment TO Config_Object['Environment']

This is actually a lot of actions, yes, but that's just because we use a lot of variables for directories. It could also be done in less steps.

The important thing here is the fact we don't need to launch Excel or any other app, read the data there and close it, nor do we need any kind of special processing or scripting to convert the table to a custom object that would allow us to easily grab the values.

This will usually take a second or two to run, even if there are many more values to retrieve from the config.

Bonus tip: use a proper text editor

I would also like to briefly note that the complexity of the JSON syntax can be easily worked around when using a proper text editor that actually has syntax highlighting for JSON.

For instance, I use VScode and if I open a JSON file with it, it will highlight the syntax, make it more readable and will also highlight any errors if there are any.

 

So, even if I make a mistake and forget to escape a backward slash in a folder path, or forget a comma or something, I will notice that very quickly and fix it before it causes any problems during runtime.

This is much more efficient than using a simple Notepad to modify your configs, but it's not absolutely necessary. Nor do you have to go with VScode specifically. But I personally love it for writing code and it works great for JSON as well.

I hope this is actionable enough for you to try it out yourself and see the benefits. I'm confident that if you do, you will like it so much that you'll never want to think of using Excel configs ever again!

About me

I specialize in helping businesses improve and automate their processes. If you see any value in what I shared here, please follow me on LinkedIn for more insights into RPA, process improvement and similar topics. Hit the 🔔 on my profile to get a notification for all my new posts. 🙏