Handling Large CSV Data in Power Automate: Efficient Processing with Chunking and Excel Scripts 

Processing large CSV files in Power Automate can be a challenge, especially when dealing with files containing thousands of rows. In this blog, we’ll walk through an efficient technique for handling large CSV files by reading them from OneDrive, dynamically chunking them, processing them with an Excel script, and inserting the data into an Azure SQL Database using a stored procedure.

By following this approach, you can overcome Power Automate’s memory and timeout limitations while maintaining a smooth data pipeline.

Every modern organization grapples with data integration. CSV files are still among the most common formats for exchanging data between systems, finance exports, CRM reports, and IoT sensor data, yet Power Automate, Microsoft’s go-to workflow automation platform, has inherent limits when processing large files.

The technique detailed below is not just a workaround; it’s a scalable data engineering pattern that bridges Power Automate, Excel Office Scripts, and Azure SQL, bringing automation and analytics together in one ecosystem.

By combining chunking, Excel automation, and SQL stored procedures, this approach turns Power Automate from a lightweight workflow tool into a robust ETL engine for cloud-native data ingestion.

Challenges of Handling Large CSV Files in Power Automate

1. Memory Limitations: Power Automate has a limit on how much data can be processed in a single step.
2. Execution Timeout: Processing a large dataset in a single operation may exceed Power Automate’s time constraints.
3. Inefficient Processing: Without chunking, processing the CSV file as a whole can slow down the flow.

Expert Insight: Why Power Automate Struggles with Big Data

Power Automate, by design, is optimized for event-driven automation rather than massive data ingestion. Each action has payload and memory constraints (typically capped around 100 MB per flow run), and looping through thousands of records can quickly exhaust execution limits.

• Without chunking, large CSVs lead to:
• Flow failures due to memory spikes.
• Timeout errors on premium connectors.
• Data loss or skipped records.

This is where data chunking, processing files in smaller segments, becomes mission-critical. It ensures that Power Automate workflows remain stable, efficient, and aligned with enterprise SLAs.

Proposed Solution

We use a chunking mechanism that processes the CSV file in manageable portions. The approach consists of the following steps:

1. Read the CSV file from OneDrive.
2. Extract the headers and store them in a variable.
3. Use two variables (Index and Buffer) to control chunking.
4. Process the CSV in chunks using the take(skip(…)) expression.
5. Concatenate the headers with each chunk and pass the result to an Excel script to convert the CSV to JSON.
6. Pass the JSON output to an Azure SQL stored procedure for insertion.

This approach smartly leverages Microsoft’s ecosystem:

OneDrive acts as the ingestion layer.

• Power Automate orchestrates data chunking and flow control.
• Excel Office Scripts handle CSV parsing and transformation into JSON.
• Azure SQL Database becomes the final destination for structured storage and querying.
• By decoupling logic between these components, you achieve a low-code, high-performance data pipeline that can process 50,000+ rows without hitting Power Automate’s memory limits.

Step-by-Step Implementation in Power Automate

Now, let’s explore the step-by-step breakdown, enhanced with best practices and technical commentary for enterprise-scale use.

1. Read CSV File from OneDrive

• Use the “Get file content” action to retrieve the CSV file from OneDrive.
• Store the file content in a variable.

Reading from OneDrive provides native integration with Power Automate and Microsoft 365. In larger enterprise environments, this same technique can be adapted for SharePoint, Azure Blob Storage, or Microsoft Fabric Data Lakehouse (OneLake) by simply replacing the connector source.

2. Extract Headers

• Since headers are required for structured processing, we store them separately.
• Use the following expression to extract headers:
• first(split(outputs(‘Get_file_contents’)?[‘body’], decodeUriComponent(‘%0D%0A’)))
• split() breaks the file into an array of lines.
  decodeUriComponent(‘%0D%0A’) represents the newline (CRLF).
  first() extracts the first row (headers).
• Store the extracted headers in a variable (Headers).

By extracting headers separately, you maintain data schema consistency, crucial for structured conversion to JSON later.

In large-scale automations, this step ensures:

• Dynamic column mapping (adapts to CSV schema changes).
• Error handling for missing or malformed headers.
• Simpler Excel scripting, since header validation happens upfront.

3. Define Chunking Variables

We define two variables:

1. Index: Tracks the starting row for each chunk.
2. Buffer: Defines the number of rows per chunk.
3. Initialize Index as 1 (to skip headers).
4. Set Buffer to a reasonable number, e.g., 500 rows.

The Buffer variable directly affects performance.

For smaller CSVs (<10k rows), 500 is optimal.

For large datasets (50k+), increase buffer to 1,000–2,000 rows for better throughput.

Remember, Power Automate’s loop iterations have a 5,000 limit; plan your buffer size accordingly to avoid exceeding run quotas.

4. Process the CSV in Chunks

To handle large files, we extract a chunk of rows dynamically using the take(skip(…)) function.

Expression:

take(skip(split(outputs(‘Get_file_contents’)?[‘body’], decodeUriComponent(‘%0D%0A’)), variables(‘Index’)), variables(‘Buffer’))

The take/skip approach ensures that Power Automate processes only the current batch in memory. This keeps the flow lightweight and performant, ideal for business processes involving financial transactions, IoT telemetry, or CRM exports.

It mirrors classic windowing functions in data engineering, but is implemented visually via Power Automate’s expressions.

5. Concatenate Headers with the Current Chunk

Each chunk should be processed as a complete CSV, so we concatenate the headers with the chunk.

Expression:

4. concat(variables(‘Headers’), decodeUriComponent(‘%0D%0A’), join(take(skip(split(outputs(‘Get_file_contents’)?[‘body’], decodeUriComponent(‘%0D%0A’)), variables(‘Index’)), variables(‘Buffer’)), decodeUriComponent(‘%0D%0A’)))

This is a crucial step for data integrity , each chunk now becomes a valid standalone CSV with headers, ensuring the Excel Office Script reads consistent schema segments for JSON conversion.

6. Convert CSV to JSON Using an Excel Script

Now that we have a well-formed CSV chunk, we pass it to an Excel script to convert it to JSON.

This step is a masterstroke, using Excel Scripts (Office Scripts) in Power Automate to dynamically transform data in Excel Online. It offloads parsing logic from Power Automate and uses Excel’s powerful in-memory grid to convert structured data to JSON quickly.

Excel Script for CSV to JSON Conversion

function main(workbook: ExcelScript.Workbook, csvData: string): string {let sheet = workbook.getWorksheet(“Sheet1”);
sheet.getRange().clear(); // Clear previous data

let rows = csvData.split(“\n”).map(row => row.split(“,”));

sheet.getRange(A1:${String.fromCharCode(65 + rows[0].length – 1)}${rows.length})
.setValues(rows);

let jsonArray = [];
let headers = rows[0];

for (let i = 1; i < rows.length; i++) {
let rowObject: { [key: string]: string } = {};
for (let j = 0; j < headers.length; j++) {
rowObject[headers[j]] = rows[i][j];
}
jsonArray.push(rowObject);
}

return JSON.stringify(jsonArray);
}

This script efficiently converts each CSV chunk into JSON arrays, perfectly aligned with Azure SQL’s OPENJSON() function. It’s a lightweight ETL transformation that integrates natively with Power Automate’s “Run script” action.

7. Insert JSON into Azure SQL via Stored Procedure

The JSON output from the Excel script is passed into an Azure SQL stored procedure for bulk insertion.

Using stored procedures for ingestion gives you transactional control, data validation, and rollback safety, all within Azure SQL.

This pattern is used in real-time analytics pipelines, sales reporting systems, and data warehouse staging.

For enhanced performance, use table-valued parameters (TVPs) or bulk insert APIs for datasets with more than 100k rows.

8. Loop Until All Data is Processed

After processing each chunk, update the Index variable:
variables(‘Index’) + variables(‘Buffer’)
Continue looping until the entire file is processed.

Looping ensures the flow autonomously processes massive datasets with zero manual intervention, turning Power Automate into a self-regulating ingestion engine.

Add monitoring steps (email notifications, run history logging) to ensure observability at scale.

Benefits of This Approach

• Handles Large CSV Files: The chunking technique ensures smooth execution without hitting memory limits.
• Optimized Processing: Instead of processing the entire file at once, the data is processed in smaller, manageable chunks.
• Scalability: Works for both small and large datasets without modifications.
• Flexible Data Handling: The approach can be extended for different data formats and target databases.

This method isn’t just about handling CSVs; it’s about future-proofing automation architectures. The same chunking and script-based conversion technique can be adapted for XML, JSON, or API-basedingestion, scaling seamlessly with Microsoft Fabric’s OneLake dataflows or Azure Synapse pipelines.

By leveraging chunking, Excel scripts, and SQL stored procedures, Power Automate can efficiently process large CSV files without exceeding memory or execution time limits. This method ensures seamless data flow from OneDrive to Azure SQL, making it a scalable and robust solution.

Addend Analytics

Addend Analytics is a leading Power BI consulting services provider and Microsoft Power BI partners based in Mumbai, India. In addition to Power BI implementations, we specialize in providing end-to-end solutions, such as Business Central with Power BI, to unlock actionable insights. Our expertise also extends to Microsoft Fabric consulting, with competitive pricing to meet your business needs.

We have successfully delivered Power BI solutions for the Manufacturing industry, including real-time manufacturing dashboards. Having successfully completed over 100 projects across sectors such as financial services, banking, insurance, retail, sales, real estate, logistics, and healthcare. Whether you’re exploring the cost of Business Central implementation or seeking advanced data analytics, Addend Analytics is here to help. Get a free consultation now by emailing us at kamal.sharma@addendanalytics.com.

Call to Action:
Ready to modernize your data automation architecture with Microsoft Power Platform, Azure SQL, and Microsoft Fabric?
Book your free consultation with Addend Analytics today.