web

You’re offline. This is a read only version of the page.

News and Announcements icon

Power Platform CommunityForums Power Automate Merge CSV files

Power Automate

Suggested Answer

Merge CSV files

(0) Share

Report

Report

Posted on by T24k

32

Hi all,

I have three CSV files stored in a folder, and their file names are dynamic. I need to merge them into a single Excel file using Power Automate Desktop. The problem is that the second file contains additional header columns compared to the first and third files. As a result, when I use the Read from Excel worksheet and Write to Excel worksheet actions, the data is written into the wrong columns. I’d like to remove the extra columns from the second file before merging so that all three files align correctly. Could you please guide me

on how to achieve this?

Categories:

Power Automate Desktop

I have the same question (0)

All responses Img

All responses (2)

Answers Img

Answers (0)

Suggested answer

UshaJyothiKasibhotla 225 Moderator on at

Like (0)

Report
Copy link

Link copied!

HI @T24k

Get all CSV files
Get files in folder →
Folder: your CSV folder
File filter: *.csv
Store in variable: %CSVFiles%
2️⃣ Initialize variables
Set variable → MasterHeaders = ""
Set variable → MergedRows = %Empty list%
3️⃣ Loop through each CSV file
For each → CurrentFile in %CSVFiles%
Inside the loop:
a. Read file content
Read text from file → File = %CurrentFile% → Store in %FileContent%
b. Split into rows
Convert text to list → Text = %FileContent% → Separator = \r\n → Output = %Rows%
c. Extract headers
Get first item of list → %Rows% → Store in %CurrentHeaders%
Remove item from list (index 0) → to remove headers row, leaving only data
4️⃣ Handle MasterHeaders
If MasterHeaders = "" →
Set MasterHeaders = %CurrentHeaders%
Continue to next file loop for data rows
Else (when it’s not the first file):
Compare %CurrentHeaders% with %MasterHeaders%
Create a mapping list of indexes that match
Ignore extra headers (not in MasterHeaders)
5️⃣ Rebuild aligned rows
For each DataRow in %Rows%:
Split text to list → Separator = , → %RowValues%
Initialize empty list → %CleanedRow%
For each Header in %MasterHeaders%
Get the index of this Header in %CurrentHeaders%
If found → Add %RowValues[index]% to %CleanedRow%
If not found → Add "" (empty)
Join list %CleanedRow% with delimiter , → %AlignedRow%
Append to list %MergedRows%
Write to Excel

Hope this helps,

Usha

Was this reply helpful? Yes No
Chriddle 8,672 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
A cloud flow ;)

In this example CSV-2 has an additional column "e" that should not appear in the result.

Apply to each

An array with the outputs of the 3 CSV contents.

Select

Creates an array of objects from the CSV content.

From:

Split CSV into lines and skip the header line.

skip( split( trim(items('Apply_to_each-CSV')), decodeUriComponent('%0A') ), 1 )

Map a:

Split the line into values and take the value with the same array index as the corresponding header value.

split( item(), ',' )[ int( xpath( xml( addProperty( json('{}'), 'Root', addProperty( json('{}'), 'Item', split( split( items('Apply_to_each-CSV'), decodeUriComponent('%0A') )[0], ',' ) ) ) ), 'count(//Item[following::*/text()="a" or ./text()="a"]) - 1' ) ) ]

Map b:

Similar to Map a, just replace both "a" with "b" in the xpath expression.

Map c:

Similar to Map a, just replace both "a" with "c" in the xpath expression.

Create CSV table

Combine the 3 arrays.

json( concat( slice( string(body('Select')[0]), 0,-1 ), ',', slice( string(body('Select')[1]), 1,-1 ), ',', slice( string(body('Select')[2]), 1 ) ) )

Was this reply helpful? Yes No

Join the conversation