cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

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
Highlighted
Community Support
Community Support

Hi @R3dKap,

 

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

9 REPLIES 9
Highlighted
Community Support
Community Support

Hi @R3dKap,

 

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

Highlighted

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

Highlighted

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...

Highlighted

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...

Highlighted

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.

Highlighted
Frequent Visitor

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

 

Highlighted

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

Highlighted

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

Highlighted

for problem #2 you can use a flow trigger "when file is created"

SergiPino_0-1602764100279.png

then check if this file is the csv converted into .xlsx and if it's the case fire the rest of the flow.

 

I did this on other flows and works like a charm.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (9,758)