cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
egallis
Level 10

Read CSV file and create/update list items with logic

Hi all,

 

I have a CSV file in a SharePoint Online library.

I'd like to create a FLOW that goes through each line of the CSV file and, using a bit of logic, create or update items in a list.

 

By example, if my CSV file is the following:

Last name;First name;Email

SMITH;Allan;a.smith@outlook.com

DOE;John;jdoe@outlook.com

 

If a have a list with the following:

Last name | First name | Email

DOE | John | john.doe@hotmail.com

 

When I run the workflow, I'd like to do the following:

  • when processing first line of CSV file (SMITH), since it does not exist in the list -> creates a new item in the list for SMITH
  • when processing second line of CSV file (DOE), since it already exist but email is different -> update list item to set new email for John DOE

The resulting list would then look like this:

Last name | First name | Email

DOE | John | jdoe@outlook.com

SMITH | Allan | a.smith@outlook.com

 

Is this possible ? Anyone already done something similar ?

 

Thanks for the help,

 

Emmanuel

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Read CSV file and create/update list items with logic

Hi @egallis,

 

Do you want to retrieve data from a CSV file stored in your SharePoint library using Microsoft Flow?

 

If you want to retrieve data from a CSV file stored in your SharePoint library using Microsoft Flow, I afraid that there is no way to achieve yourt needs in Microsoft Flow currently. There is no way to analyze a CSV file within Microsoft Flow currently, if you would like this feature to be added in Microsoft Flow, please submit an idea to Flow Ideas Forum:

https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas

 

But you could consider take a try to convert the CSV file into a Excel file manually and then retrieve data from the Excel file within your flow. I have made a test on my side and please take a try with the following workaround:

 

I have created a CSV file in my SharePoint library and the data structure of it as below:14.JPG

I also created a SharePoint list on my side, the data structure of it as below:15.JPG

 

If you have converted your CSV file into a Excel file manually, please take a try with the following workaround:

  • Add a proper trigger, here I use Flow button trigger.
  • Add a "Get rows" action, File name field set to Id dynamic content of the "Create file" action, Table name field set to Table1 as a custom value.
  • Add a "Apply to each" action, input parameter set to output of "Get rows" action.
  • Within "Apply to each" action, add a "Get items" action, specify Site Address and List Name. The Filter Query field set to following formula:

 

Last_x0020_name eq 'item(...)' and First_x0020_name eq 'item(...)'

The first item(...) expression read as below:

 

 

item()?['Last name']

The second item(...) expression read as below:

 

 

item()?['First name']

Note: The above item(...) is wrapped with a single quotes.

 

 

Add a Condition, click "Edit in advance mode", type the following formula:

 

@empty(body('Get_items')?['value'])

 

Within "If/yes" branch of Condition, add a "Create item" action, specify Site Address and List Name. Last name field set to following formula:

 

item()?['Last name']

First name field set to following formula:

 

 

item()?['First name']

Email field set to following formula:

 

 

item()?['Email']

 

Within "If/no" branch of Condition, add a "Apply to each 2" action, input parameter set to output of "Get items" action. Within "Apply to each 2" action, add a "Update item" action, specify Site Address and List Name, Id field set to ID dynamic content of "Get items" action, Email field set to following formula:

 

items('Apply_to_each')?['Email']

 

Image reference:16.JPG

 

17.JPG

 

The flow works successfully as below:18.JPG

 

19.JPG

 

 

 

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Community Support Team
Community Support Team

Re: Read CSV file and create/update list items with logic

Hi @egallis,

 

Do you want to retrieve data from a CSV file stored in your SharePoint library using Microsoft Flow?

 

If you want to retrieve data from a CSV file stored in your SharePoint library using Microsoft Flow, I afraid that there is no way to achieve yourt needs in Microsoft Flow currently. There is no way to analyze a CSV file within Microsoft Flow currently, if you would like this feature to be added in Microsoft Flow, please submit an idea to Flow Ideas Forum:

https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas

 

But you could consider take a try to convert the CSV file into a Excel file manually and then retrieve data from the Excel file within your flow. I have made a test on my side and please take a try with the following workaround:

 

I have created a CSV file in my SharePoint library and the data structure of it as below:14.JPG

I also created a SharePoint list on my side, the data structure of it as below:15.JPG

 

If you have converted your CSV file into a Excel file manually, please take a try with the following workaround:

  • Add a proper trigger, here I use Flow button trigger.
  • Add a "Get rows" action, File name field set to Id dynamic content of the "Create file" action, Table name field set to Table1 as a custom value.
  • Add a "Apply to each" action, input parameter set to output of "Get rows" action.
  • Within "Apply to each" action, add a "Get items" action, specify Site Address and List Name. The Filter Query field set to following formula:

 

Last_x0020_name eq 'item(...)' and First_x0020_name eq 'item(...)'

The first item(...) expression read as below:

 

 

item()?['Last name']

The second item(...) expression read as below:

 

 

item()?['First name']

Note: The above item(...) is wrapped with a single quotes.

 

 

Add a Condition, click "Edit in advance mode", type the following formula:

 

@empty(body('Get_items')?['value'])

 

Within "If/yes" branch of Condition, add a "Create item" action, specify Site Address and List Name. Last name field set to following formula:

 

item()?['Last name']

First name field set to following formula:

 

 

item()?['First name']

Email field set to following formula:

 

 

item()?['Email']

 

Within "If/no" branch of Condition, add a "Apply to each 2" action, input parameter set to output of "Get items" action. Within "Apply to each 2" action, add a "Update item" action, specify Site Address and List Name, Id field set to ID dynamic content of "Get items" action, Email field set to following formula:

 

items('Apply_to_each')?['Email']

 

Image reference:16.JPG

 

17.JPG

 

The flow works successfully as below:18.JPG

 

19.JPG

 

 

 

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

egallis
Level 10

Re: Read CSV file and create/update list items with logic

Hi @v-xida-msft,

 

Thanks a lot for all your explanations... It's funny because I ended up doing exactly what you propose : I converted manually my CSV file to an Excel file with a table containing my CSV content. I then built my flow on this Excel file and of course it was much easier.

 

Cheers,

Emmanuel

asdfaf4
Level: Powered On

Re: Read CSV file and create/update list items with logic

2 potential problems by converting to Excel...

 

1. Limit of 1 million rows

2. Using Excel in flow doesn't support dynamics path and filename, you have to select the file within flow.  This defeats the purpose of having a library where a flow fires when a new file being added.  Highly frustrating...

egallis
Level 10

Re: Read CSV file and create/update list items with logic

Well, on my side I'll never get to the million records...

 

On the other hand, I totally agree wih you @asdfaf4 about the fact that Excel file paths cannot be dynamic and that's really a pain... Hope this feature will come true one day...

Super User
Super User

Re: Read CSV file and create/update list items with logic

You can use Parse CSV action from Plumsail Documents connector. It allows you to convert CSV into an array and variables for each column. Please read this article demonstrating how it works.

 

Then you can iterate through result array and create items in a SharePoint list according to your logic.

aamirparkar
Level: Powered On

Re: Read CSV file and create/update list items with logic

The feature is not available right now. I had a similar issue where I paste my csv in a SharePoint folder and I managed to build a workaround.

1. Create an Excel file.

2. Use PowerQuery to read the csv from the Sharepoint.

3. Use flow to to read the queried table in Excel.

4. Update/Replace the Csv as required.

 

Cheers,

Aamir

 

CyndyJT
Level: Powered On

Re: Read CSV file and create/update list items with logic

can i set the power query to run twice a day?

aamirparkar
Level: Powered On

Re: Read CSV file and create/update list items with logic

Using Shell Scripts you can. Unfortunately there is no such feature in Flow yet. I use VBA macros but that involves manual intervention.

 

Cheers,

Aamir

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (3,863)