Getting or writing large datasets to and from Excel

January 30th 2024
Back to all blogs

Handling large datasets in Excel can be challenging, especially when using native Power Automate features. The built-in Excel connector, limited to actions like “Get a row” and “Add a row into a table,” often struggles with bulk data, leading to performance issues or timeouts.

Typically, managing large Excel datasets in Power Automate involves looping through rows. However, this approach is time-consuming and prone to efficiency bottlenecks. This is where Encodian Flowr comes into play.

 

Encodian Flowr: A Game Changer for Bulk Excel Data

Encodian Flowr introduces robust Excel file manipulation actions designed for bulk data handling. With actions like “Get Rows from Excel” and “Add Rows to Excel,” Encodian Flowr bypasses the need for looping, offering a more streamlined approach.

The Encodian Flowr actions are architected to work with bulk data by design, thus avoiding the requirement to loop through data. Get Rows returns all data within the worksheet or a specified range. Add Rows adds an entire JSON payload containing multiple rows.

 

Demonstrating Encodian’s Efficiency

To highlight the efficiency of Encodian Flowr, we conducted a test using a spreadsheet containing 49,897 rows and 15 columns. The objective was to assess performance in bulk processing operations.

Screenshot of an Excel file used in a Power Automate tutorial, demonstrating process automation with bulk data handling. The Excel file contains dummy content, illustrating how large datasets are managed and automated in Power Automate for efficient process optimization.

We built a basic flow in Power Automate that:

  1. Retrieves all 49,897 rows in the Excel file
  2. Adds the entire dataset into a different blank Excel file

 

Results

The below run history shows the Encodian connector took just 10 seconds to retrieve over 49,000 rows and 15 columns of data. And it took just 7 seconds to write the same amount of data into a blank spreadsheet.

A flow illustrating the high-speed of Encodian Excel actions within Power Automate. The steps include initiating the trigger, retrieving Excel file content from SharePoint, extracting row data using Encodian, adding row data using Encodian, and creating a file with Encodian. Key focus is on the efficiency of Encodian, with process times for each step highlighted, demonstrating the rapid execution of tasks in the automation sequence.

 

Alternatives and Comparisons

While other solutions like Graph API calls and Office Scripts are available for bulk data management in Excel, they often require premium features and more technical expertise. Encodian, in contrast, simplifies bulk Excel processing within Power Automate.

 

Conclusion

Encodian empowers users to manage large Excel datasets in Power Automate efficiently, transforming data handling into a fast, scalable process. For those looking to enhance their automation capabilities in Excel, Encodian is an invaluable tool.

The Encodian connector contains 150+ actions, including more Excel functionality and other useful tools. Check out the full capability here.

Happy automating!

 

Try Flowr for Free!

No credit cards, no catches, no contracts!

Author
Alex Rackwitz

Director

Back to all blogs

You might also be interested in...