Consolidate Excel attachments to CSV in Power Automate: guide

João Barros 19 de July de 2026 7 min read

This tutorial shows how to consolidate Excel attachments (with a table) into CSV using Power Automate — useful to combine reports received by email into a single file for analysis. The goal is to automate the process when you receive multiple attachments (for example 3–50 files per email or throughout a day) and generate a RelatorioConsolidado_YYYYMMDD_HHMM.csv ready to be consumed by Power BI or another ETL process. We will explain the rationale behind the choices and show practical examples and plausible numbers for each step.

Prerequisites

Microsoft account with access to Power Automate (appropriate license for Standard/Office 365 connectors).
OneDrive for Business or SharePoint account for temporary files and the result; a folder with write permissions (for example /Temp/Anexos and /Relatorios/Consolidados).
Excel attachments should contain a defined table (or be CSV files). If they do not have a table, the "List rows present in a table" connector will not work.
Permissions to create and delete files in the chosen folders.
Note about limits: very large files can cause failures. It is recommended to validate sizes over 10 MB per attachment; if you have hundreds of thousands of rows enable "Pagination" in "List rows present in a table" (e.g., Page Size = 5000).

Step 1: Flow trigger (When a new email arrives)

Create a new Automated cloud flow in Power Automate with the "When a new email arrives (V3)" trigger from the Outlook connector. Filtering on the trigger reduces unnecessary work — for example, process only emails from the finance sender or with a subject that contains "Relatório". This avoids processing hundreds of emails per day.

Trigger: When a new email arrives (V3)
- Folder: Inbox
- Include Attachments: Yes
- Filter: Subject contains 'Relatório'

Why: restricting the trigger decreases run usage and avoids hitting flow limits. Practical example: if you receive on average 20 emails with attachments per day and each has 3 attachments, without filters the flow would run 20 times; with a filter for a specific sender you reduce it to, for example, 5 runs per day.

Step 2: Save attachments temporarily to OneDrive/SharePoint

Add an "Initialize variable" action of type Array named attachmentsList to store paths of the temporary files (this allows iterating and deleting later). Then use "Apply to each" with the Attachments field from the trigger. Inside the loop, use "Create file" (OneDrive for Business / SharePoint) to write the attachment. It is recommended to prefix the name with utcNow to avoid collisions: e.g. "20260719_1030_NomeFicheiro.xlsx".

Initialize variable: Name = attachmentsList, Type = Array, Value = []
Apply to each: Attachments
  - Create file (OneDrive for Business)
    Folder Path: /Temp/Anexos
    File Name: @{utcNow('yyyyMMdd_HHmmss')}_@{items('Apply_to_each')?['Name']}
    File Content: @{items('Apply_to_each')?['ContentBytes']}
  - Append to array variable: attachmentsList <- path of created file

Example: if you receive 4 attachments, the attachmentsList variable will have 4 paths like "/Temp/Anexos/20260719_103012_Relatorio1.xlsx". This facilitates tracking and later cleanup.

Step 3: Read rows from each Excel (List rows present in a table)

After the files are created, add another "Apply to each" that iterates over the attachmentsList variable. For Excel files use "List rows present in a table" (Excel Online (Business)). Select the file by the stored path and choose the Table (e.g., Table1). If the file is CSV use instead "Get file content" and convert the content to rows with Power Automate expressions.

Apply to each: value in attachmentsList
  - List rows present in a table
    Location: OneDrive for Business
    Document Library: OneDrive
    File: @{items('Apply_to_each')}
    Table: Table1

For CSV (e.g., file ends with .csv) follow this flow inside the same loop: 1) Get file content (OneDrive for Business); 2) Compose: base64ToString(body('Get_file_content')?['$content']); 3) Compose: split(outputs('Compose'), ' ') to get lines; 4) use "Skip"/"Select" to skip the header and map columns. Example of an expression to decode: base64ToString(body('Get_file_content')?['$content']).

Tip: if your tables have more than 256 rows enable "Pagination" in "List rows present in a table" and set Page Size = 5000 to read large files without losing rows.

Step 4: Aggregate rows into an array and create the CSV table

Initialize an array variable called consolidatedRows before the second Apply to each. Inside the loop, use the "Select" action to map the columns you want to consolidate (e.g.: Date, Item, Value) — this ensures inconsistent columns are normalized. Then use "Append to array variable" with the result of the "Select" to add all rows.

Initialize variable: consolidatedRows, Type = Array, Value = []
Inside Apply to each (after Select):
  - Append to array variable: Name = consolidatedRows, Value = outputs('Select')
After loop:
  - Create CSV table
    From: variables('consolidatedRows')
    Columns: Automatic (or Custom to define order/headers)
  - Create file (OneDrive/SharePoint)
    Folder Path: /Relatorios/Consolidados
    File Name: RelatorioConsolidado_@{utcNow('yyyyMMdd_HHmm')}.csv
    File Content: output of Create CSV table

Concrete example: if you process 3 attachments with 100, 120 and 80 rows respectively, consolidatedRows will have 300 rows. If you want to avoid duplicates add a "Union" action between arrays or use "Filter array" to deduplicate based on keys (e.g.: Date+Item).

Step 5: Clean temporary files

To avoid accumulating files, add a final loop to delete the temporary files in attachmentsList with the "Delete file" action from OneDrive/SharePoint. This keeps the /Temp/Anexos folder clean and reduces storage costs.

Apply to each: value in attachmentsList
  - Delete file
    File: @{items('Apply_to_each')}

It is also recommended to log entries (for example, in a SharePoint list or a Log table) with the names of processed files and number of consolidated rows (e.g.: 3 files → 300 rows) for audit purposes.

Common errors and how to resolve them

- "Table not found" error: confirm the workbook has a table with the used name; if it does not, ask the sender to add one or use Office Scripts/Power Automate to create a table (requires permissions).
- File limits: very large files can fail; validate the attachment size (for example ignore > 20 MB) or add retry policies.
- Inconsistent columns: use mapping with "Select" and handle missing columns with coalesce(item()?['Col'], '') to avoid errors.
- Encoding: if the CSV has special characters ensure UTF-8; Power BI usually recognizes UTF-8 with BOM, consider adding a BOM if you see accentuation issues.

Verify the result

Open the /Relatorios/Consolidados folder and confirm that the RelatorioConsolidado_*.csv file exists with all processed rows and without duplicated headers. To test, send an email with 2 sample attachments (each with Table1 of 3 rows) and confirm that the final CSV shows 6 rows. If something does not match, check the log and the content of consolidatedRows during a test run.

Conclusion

With this flow you can automate the consolidation of reports sent by email into a CSV file ready to be consumed by Power BI or other processes. Recommended next steps: add duplicate detection, send a Teams/email notification when the file is created, or connect the output to a dataset in Power BI via OneDrive/SharePoint. Final tip: test first with small files and mapped columns (e.g.: 3 attachments of 5 rows) to reduce errors and adjust pagination and page size as needed.