Unanswered

Merge CSV and xlsx into 1 using a common column (primary key)

(0) Share

Report

Posted on by jeremyncs

Hi Community,

I have a CSV files generated in SharePoint folder and another Excel file in .xlsx format stored in Document folder in SharePoint. I These 2 files have a common column. I want to use the common column as primary key to merge the 2 files into 1 file.

Any advice on how to design the flow to achieve the above? Please provide the details steps as I am quite new to Power Automate.

Thanks in advance!

Jeremy

Categories:

Building flows

I have the same question (0)

All responses (24)

Answers (0)

Chriddle 8,436 Super User 2025 Season 2 on at

Like (0)

Report

First you have to think about how to convert the CSV to JSON.

If the CSV only contains simple values (without " to protect the values), this is doable with built-in PA functions.

If not, you will need to purchase an action to do this (see "Parse CSV") action.

You can also create your own action, use PA Desktop, or fiddle with Office scripts.

After this is settled, the rest is straight forward:

Create an array of the values in you id column and grab the data records from both JSONs with this id.

You will find an example with 2 different ways to do so here:

https://powerusers.microsoft.com/t5/Building-Flows/Power-Automate-Create-HTML-Table-rows-staggered-against-colums/m-p/2351663#M261769

If you show me the structure of your JSONs, I can help you to do so.

Was this reply helpful? Yes No
jeremyncs 47 on at

Like (0)

Report

Hi, what is the steps in PA should I use to convert CSV format into JSONs and xlsx into JSONs. Attached is the CSV file I downloaded from SharePoint.

Extract_ SMM_resu...

Was this reply helpful? Yes No
Chriddle 8,436 Super User 2025 Season 2 on at

Like (0)

Report

This is not a CSV, ist's XLSX 😉

Was this reply helpful? Yes No
jeremyncs 47 on at

Like (0)

Report

Ignore the attachment extension because the forum doesn't allow CSV to be attached. I just want to show you the headers of each column.

Was this reply helpful? Yes No
Chriddle 8,436 Super User 2025 Season 2 on at

Like (0)

Report

Please attach the CSV, just zip it.

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (0)

Report

@jeremyncs

Here is a blog explaining CSV to JSON: https://www.tachytelic.net/2021/02/power-automate-parse-csv/?amp

If you do have a file with in-data commas, then you can use this more complicated set-up: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/td-p/1508191

Was this reply helpful? Yes No
jeremyncs 47 on at

Like (0)

Report

Attached is the zip file

Extract_ SMM_resu...

Was this reply helpful? Yes No
jeremyncs 47 on at

Like (0)

Report

@AlexEncodian When I add Parse CSV into the flow it show the screen below. What should input for Connection Name and API Key

Was this reply helpful? Yes No
jeremyncs 47 on at

Like (0)

Report

@takolota I will try whether it will work. I have a total of 76 columns and 2000 to 3000 rows, performance might be an issue. Thanks for your advice.

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (0)

Report

@jeremyncs

Yes the built in actions can easily handle that.

By the way, what license does your account have?

And is speed/performance a design consideration for this flow?

I ask because after you convert to JSON there are a couple of options for merging, one is easier to explain & understand, but slower & uses a lot of action calls towards daily limits, the other is much faster & only takes a few actions, but involves more advanced expressions.

Was this reply helpful? Yes No