Getting or writing large datasets to and from Excel

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.

• Get Rows from Excel
• Add Rows to Excel

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.

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.

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!