As UiPath RPA developers, we tend to use the Robotic Enterprise Framework quite a lot while stumbling through different requests made by the end-users of the applications that we build. One such request might come in the form of altering the format of the file which will be used to specify the settings of the aforementioned applications. In this article, I will specify the how related to using a JSON file instead of the default XLSX one used in the configuration of the application.
The main reason why someone would choose a JSON configuration file over an Excel Workbook would be to serialize and transmit structured data over a certain network, which would provide a more secure way of transferring data. This can be useful when a user would like to send a configuration file from his local network over to one hosted on a virtual machine. Another advantage would be the fact that JSON files are very lightweight especially when compared to Excel files. In terms of user experience, Excel files are easier to modify and use by the end-user which most likely has limited experience when dealing with more exotic formats such as JSON, although the learning curve remains quite shallow.
Preparing the environment
First off, we need to create a JSON file which will encompass all of the sheets and their associated data present in the Excel file, namely “Settings”, “Constants” and “Assets”. As you can see, the nested data in the picture below strongly resembles the data used in the default configuration Excel file. Now, the developer can insert whichever additional data they need.
An additional requirement would be to install the official UiPath WebAPI activities package found in the “Manage Packages” menu in order to access the “Deserialize JSON” activity which will be used inside the Workflow that will be built in the next section of the article.
Initializing the process
Now that the data has been successfully reorganized into its new format, the developer can start working on integrating it into UiPath. The first step in this regard would be to create a new Workflow having six arguments.
- “in_JSONConfigFile” should contain the relative path of the newly created JSON configuration file.
- “inout_DTConfig” should contain the data table which will later be used to overwrite and store the key/value pairs inside the “SettingsAndConstants” argument.
- “in_ConfigSection” should contain the name of the sheet which is currently processed. (Settings, Constants, Assets)
- “in_SettingsOverwrite”, “in_ConstantsOverwrite” and “in_AssetsOverwrite” should contain boolean values pertaining to whether the existing data should be overwritten in the “inout_DTConfig” data table. These arguments are useful due to the fact that they make the component reusable.
Building the workflow
After initializing the arguments, we can start working on the workflow itself. First, we should add an “If” statement which analyzes the existence of the JSON configuration file. In the “Then” section of the statement, we should build three data tables using the “Build Data Table” activity, which will later be used to store and merge the key/value pairs from the JSON configuration file with the “inout_DTConfig” data table.
Afterwards, the JSON configuration file will have to be read through a “Read Text File” activity and deserialized through a “Deserialize JSON” WebAPI activity. The output of the deserialization should be of the “JObject” type.
Up next, we should add three “If” statements in order to check which sheet is being processed. For simplicity’s sake, I will analyze only one of them, the rest being very similar in structure, the only difference being the Sheets and data tables used as variables.
Inside the first “If” statement, we should check whether the current sheet that we are analyzing is the “Settings” one. Inside the “Then” section, we should add a “Try Catch” sequence which should contain a “For Each” statement, iterating through each key/value pair within the JSON “Settings” object. Inside the “Body” of the statement, we should declare another “If” condition verifying whether the value is empty and appending it to the previously built data table if it is not so. We should also add a catch to the “Try Catch” activity, logging an error message if the “Settings” object could not be read.
After the “Try Catch” activity is filled in, the previously populated data table should be filtered alongside “inout_DTConfig” into a new data table variable through an Inner Join query based upon the values of the keys of both joined data tables. The newly created data table must then be analyzed inside an “If” activity to see whether it has any rows and whether the “inout_DTConfig” data table must be overwritten through the “in_SettingsOverwrite” argument.
If both conditions pass off as “True”, then we shall build an error message by iterating through each row of the previously joined table and parsing the value of each key inside a string variable. Afterwards, we shall throw a system exception through the use of a “Throw” activity stating the number of duplicate names inside both the Excel and JSON files, followed by the error message containing the names of the duplicate rows. Otherwise, we should use a “Merge Data Table” activity in order to add the contents of the data table which we have populated with the JSON Configuration file contents to the “inout_DTConfig” data table argument.
Invoking the Workflow
After the reusable Workflow has been built, it must be invoked in two different places. Initially, it must be invoked after the default “Read range (Settings and Constants sheets)” activity, having the “in_SettingsOverwrite” and “in_ConstantsOverwrite” argument values set to True.
Finally, the Workflow has to be invoked after the default “Read range (Assets sheet)” activity, having the “in_AssetsOverwrite” argument value set to True with no further modifications apart from the two invocations. Additionally, it must be mentioned that the second invocation of the Workflow is optional depending on whether the Orchestrator will be used or not. Consequentially, the “in_AssetsOverwrite” argument and its pertaining activities will become redundant and therefore removable.
Conclusion
And there you have it! After following all of the steps described above, you will have a fully functional Workflow ready to load and process the contents of a JSON Configuration file.
I would like to thank my colleague, Bogdan Istrate, for building and providing the Workflow necessary in illustrating and relating the steps mentioned in my article 🙂