Hi All,
Need some help regarding a task.
I have one excel and in that there is one of the columns named “Action to Perform” .
I want to filter on column “Actions to Perform” and create five excels based on 5 different values that are there. It is a 20000 plus row excel.
Can you help / advise on how to achieve that ?
Also, i would like to copy only specific corresponsing columns based on values in “Actions to Perform” columun.
Hi
you have more than 20,000 rows in your Excel file, using Excel as a database can be a more efficient option for splitting and filtering details. Excel databases allow for faster data retrieval and manipulation, making it a suitable choice for handling large datasets.
In Automation Anywhere, you can use SQL queries to interact with Excel as a database, allowing you to perform filtering and data extraction operations with greater speed
For example :
SELECT Column1, Column2, Column3 FROM YourSheet WHERE EActions to Perform] = 'YourFilterValue'
Get Unique Values from "Actions to Perform" column
For Each Unique Value: Execute SQL Query to select specific columns based on the Unique Value
Save Query Results as a new Excel file
End Loop
Hi
FYI, Starting from V.31, excel advance package sort & filter action items are updated, you can now directly Filter or Sort data on the worksheet without defining any table, please see if this can help with your use cases.
https://docs.automationanywhere.com/bundle/enterprise-v2019/page/enterprise-cloud/topics/aae-client/bot-creator/commands/excel-advanced-package-sort-table-action.html
https://docs.automationanywhere.com/bundle/enterprise-v2019/page/enterprise-cloud/topics/aae-client/bot-creator/commands/cloud-using-filter-table.html
Thank you.
Also, verify the following resource.
Regards
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.