Data Import & Export

Combine CSV Files into Separate Excel Worksheets with Power Automate

Phi Hung Vo Oct 13, 2026 868 views

Question details

The user needs to build a Power Automate cloud flow that imports multiple CSV files into a single Excel workbook, with each CSV file's data being placed on its own separate worksheet.

Combine CSV Files into Separate Excel Worksheets with Power Automate

Product: Power Automate
Device & OS: not provided
Scenario: Automating the aggregation of multiple CSV data files from cloud storage into a single structured Excel report with separate tabs.
Observed behavior: The goal is to successfully extract, parse, and insert raw CSV file contents into pre-created Excel tables across multiple worksheets without manual intervention.

Before you start

Before building your flow, ensure you have created your destination Excel workbook in OneDrive or SharePoint, complete with the necessary worksheets, tables, and correctly named columns to receive the CSV data.

Solution 1Recommended

Use Power Automate Expressions and Excel Online Connector

Pre-configure an Excel workbook and use Power Automate expressions to manually parse the CSV text, then insert the parsed rows into individual worksheet tables.

Power Automate does not have a native, out-of-the-box action to parse CSV files directly into arrays. To accomplish this in a cloud flow, you must use data operations and string manipulation functions to break down the raw CSV file content into readable rows and columns.

By iterating through your CSV files and applying these expressions, you can route each dataset to its respective worksheet using the Excel Online connector.

Prepare the Destination Excel File

Create a new Excel workbook. Add a separate worksheet for each CSV file you plan to import. On each worksheet, insert a Table with headers that exactly match the columns of your CSV data.

Retrieve the CSV File Content

In your Power Automate cloud flow, add an action to get the file content of your CSVs (e.g., 'Get file content' from SharePoint or OneDrive).

Parse the CSV Data

Add a 'Compose' action and use expressions to convert and split the data. Start with base64ToString() to convert the file content to text, then use split(), replace(), and substring() expressions to separate the data by line breaks and commas into an array.

Insert Rows into Excel

Add an 'Apply to each' loop to iterate through the parsed CSV rows. Inside the loop, add the 'Add a row into a table' action from the Excel Online (Business) connector. Select the appropriate worksheet and table, and map the parsed column values to the table headers.

Use Power Automate Expressions and Excel Online Connector

Expression Complexity: Parsing CSVs natively in Power Automate requires careful string manipulation. You will heavily rely on expressions like skip, take, indexOf, and length to accurately format the data, especially if your CSV contains commas within text fields.

Free Microsoft Office alternative

Easily Manage and Combine Spreadsheets with WPS Office

If complex Power Automate cloud flows and string parsing expressions seem too overwhelming, consider using WPS Office. It provides intuitive, built-in tools for importing CSV files, managing multiple worksheets, and organizing large datasets without needing advanced cloud configurations.

1. Open a Blank Workbook: Launch WPS Spreadsheet and create a new blank workbook to serve as your master file.
2. Import CSV Files: Go to the Data tab, select 'Import Data', and locate your first CSV file. Follow the wizard to load it into the first worksheet.
3. Add Worksheets: Click the '+' icon to add new worksheets and repeat the import process for your remaining CSV files.

Fully compatible with Microsoft Excel formats (.xlsx, .csv, .xls).Lightweight and fast, allowing you to seamlessly process large CSV files locally.Familiar tabbed user interface, making it easy to migrate from MS Office.Free to use with comprehensive spreadsheet functionalities.

microsoft office alternative - wps office

Frequently Asked Questions

Can Power Automate automatically create Excel tables for the CSV data?

While Power Automate has a 'Create table' action, it is highly recommended to pre-create the workbook, worksheets, and tables manually. Dynamic table creation in flows can cause mapping issues in subsequent 'Add a row into a table' actions.

Why do I need to use the base64ToString expression to parse the CSV?

When Power Automate retrieves file content from cloud storage, it often returns the payload in base64 format. The base64ToString function is required to convert this encoded string back into plain, readable text before you can split it into rows.

Is there a premium connector available to parse CSV files easily?

Yes, there are premium third-party connectors (such as Plumsail Documents or Encodian) available in Power Automate that can parse CSV files directly into arrays without requiring complex string manipulation expressions.

How do I handle commas inside CSV text fields during parsing?

Native split() functions will split all commas, which breaks data alignment if a text field contains a comma (e.g., "Smith, John"). You must write advanced nested replace() expressions or use a dedicated CSV parsing premium connector to handle quoted commas correctly.

Excel & Data Problems