Read CSV file and create/update list items with logic

[R3dKap]

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

[v-xida-msft · Accepted solution]

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:

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

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:

The flow works successfully as below:

Best regards,

Kris

[R3dKap]

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]

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

[R3dKap]

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

[anton-khrit]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[SergiPino]

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

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.

[takolota]

Hello,

Please see this template if you want to parse a CSV without 3rd party connectors. It works even if there are commas in the data.

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/td-p/1508191

For this use-case, you can insert a Filter array action after the last Select action to filter the Select output before parsing the JSON to dynamic content.

[fritz77]

Hi, I'm trying to use your solution but I get a strange behavior: the first 5 rows in Sharepoint list have one field empty while all the others show correctly the field value. I tried to re-run the flow starting from the same excel to update the values and I get the same result.

I checked the input and output of every step of the flow and I can find the correct value in the correct field but not in the Sharepoint list. The excel file is correct, the value in this rows are formatted correctly and are all plain text.

Any idea?

[fritz77]

Hi, I'm trying to use your solution but I get a strange behavior: the first 5 rows in Sharepoint list have one field empty while all the others show correctly the field value. I tried to re-run the flow starting from the same excel to update the values and I get the same result.

I checked the input and output of every step of the flow and I can find the correct value in the correct field but not in the Sharepoint list. The excel file is correct, the value in this rows are formatted correctly and are all plain text.

Any idea?