Question

Excel Task : Copy data from one excel and create other excels

  • 22 January 2024
  • 3 replies
  • 86 views

Badge +1

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.


3 replies

Userlevel 6
Badge +15

Hi @Nishant.D ,

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 [Actions 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

 

 

 

 

Userlevel 3
Badge +8

Hi @Nishant.D,

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. 

 
Userlevel 5
Badge +9

@Nishant.D 

Also, verify the following resource.

 

 

Regards

Reply