Product: Kryon RPA
Product Version: Kryon RPA All Versions
Components: Kryon Robot; Kryon Studio
Article Contributors: Artur Martirosyan
During automating processes often we work with Excel spreadsheets, for spreadsheets that have small dataset using standard Excel advanced commands are suitable and handy, however, when we deal with big data in Excel, the processing time might take much longer if we use the standard Excel ACs. For this reason Kryon has 'Query Excel' AC which gives the option to work with the large datasets and achieve the same outcome spending less time on the processing and computing, this in its turn shortens the time taken from your bot.
Steps:
When working with large datasets in Excel, it’s faster to use 'Query Excel' AC. The AC will make your Excel data processing several times faster as opposed to the standard Excel ACs.
In this article, the usage of Excel Query AC will be explained with some samples and use cases. At first, some simple queries will be shown, and afterward, using the combination of those queries a short business use case will be presented as well as the comparison in terms of processing time when using Query Excel AC as opposed to Loops, Split, or Concatenation methods. The Excel Spreadsheet which has been used for this article together with the Kryon Wizard is attached. The Kryon Wizard contains all the examples that are discussed below. For using Excel Query ACs, you need to use SQL query statements, some words might be reserved and to avoid errors while executing the queries you may need to use square brackets around the statements, e.g. [AS].
In the following excel file, we have a dataset as depicted below with around 500 rows.
In this article, the usage of Excel Query AC will be explained with some samples and use cases. At first, some simple queries will be shown, and afterward, using the combination of those queries a short business use case will be presented as well as the comparison in terms of processing time when using Query Excel AC as opposed to Loops, Split, or Concatenation methods. The Excel Spreadsheet which has been used for this article together with the Kryon Wizard is attached. The Kryon Wizard contains all the examples that are discussed below. For using Excel Query ACs, you need to use SQL query statements, some words might be reserved and to avoid errors while executing the queries you may need to use square brackets around the statements, e.g. [AS].
In the following excel file, we have a dataset as depicted below with around 500 rows.
2. The Excel Query AC will select the mentioned columns only if the column 'Location' of the respective row contains 'Urban'.
3. The following query will give us the total amount of 'InsuredValue' of all rows which contain 'Urban' as a location.
4. This query will output the distinct dates from the column ‘Expiry’, in other words, It will ignore all the duplicate dates from the selection.
5. In the below Excel Query, you can see how to define Parameters and how to use them in your query. As a parameter, a sample date from the Spreadsheet will be used and it will be part of the condition of the query. The query will output the total amount of the 'InsuredValue' based on the specific date from the column called 'Expiry'.
6. In this Query, two parameters have been used, and additionally, the results have been grouped by the given/selected dates from the column called ‘Expiry’.
If you use the query as it is, the result will be the total amount of all rows of ‘InsuredValue’ grouped by date. If you remove the “Grouped By [Expiry]” part then the output will be the total amount of all rows for the selected dates.
If you use the query as it is, the result will be the total amount of all rows of ‘InsuredValue’ grouped by date. If you remove the “Grouped By [Expiry]” part then the output will be the total amount of all rows for the selected dates.
Result 1: with Grouping
Result 2: Without Grouping
7. In our Excel Spreadsheet, the date range stretches from 02/01/2021 - 31/12/2021. Most often when automating processes the business process flow requires getting the output of the total amount of all Insured Values per distinct date and also combine the values for the duplicate dates. The following Query results in the output as described above, the total amount of all Insured Values per distinct date.
8. The last example was built to compare the speed of the process when we use the Excel Query in sample nr. 7. Both options output the same result however, when comparing the time it is considerably faster when using option 7 as opposed to using loops, split, Read Excel, or concatenation of variables. Additionally from the maintenance point of view, the wizard becomes more compact and short with fewer ACs achieving the same desired result.
Al above-mentioned sample queries are simple and straightforward, of course, it's possible to use more complex queries based on the use case requirements. As mentioned at the beginning of the article, when working with Excel Queries, some SQL statements/words can be reserved therefore you can use square brackets around that word in your query.
To find out more about reserved words you can visit the following link.
I hope this article helps you when working with an Excel file with a large amount of data, for any questions please post in the Nintex Community.