Parse CSV File Using Power Automate

Introduction

I recently had a use case, where my customer wants to have data in a CSV file uploaded to SharePoint. Currently, they are updating manually, and it is cumbersome. This article explains how to automate the data update from CSV files to SharePoint online list. Here the CSV file is uploaded in OneDrive, but this file can be also in the SharePoint document library. Below is the block diagram which illustrates the use case. Thanks to Paulie Murana who has provided an easy way to parse the CSV file without any 3rd party or premium connectors.

• User uploads the CSV in OneDrive
• Power Automate kicks of automatically
• Data gets updated in Sharepoint.

Steps

Step 1

Upload the file in OneDrive for business. Here I am uploading the file in my dev tenant OneDrive. Here I have created a folder called ‘CSVs’ and put the file ‘RoutesDemo.csv’ inside the CSVs folder.

Step 2

Go to Power Automate using the URL (https://flow.microsoft.com) or from the app launcher.

Step 3

Now click on ‘My Flows’ and ‘Instant cloud flow’.

Step 4

Here I am naming the flow as ‘ParseCSVDemo’ and selected ‘Manual Trigger’ for this article.

Step 5

It should take you to the flow designer page.

Step 6

Click on the new step and get the file from the one drive. Search for action ‘Get file content’ and select the action under ‘OneDrive for business’ actions.

Here I am selecting the file manually by clicking on the ‘folder’ icon.

The final action should look like below in my case.

Step 7

Now select the ‘Compose’ action and rename it to ‘Compose –new line’. inside the ‘Inputs’ field just hit the ‘Enter’ key. This denotes a new line.

Hit save. If the save is successful. then there is no errors inflow. If there is it will be denoted under ‘Flow checker’.

Step 8

Now select another compose. Rename it as ‘Compose – split by new line’. Here we need to split outputs of get file content, by the new line. let's see how to do this.

select expression and enter split([Select the outputs from file content], [select the output of compose-new line].

The expression of split should be,

split(outputs('Get_file_content')?['body'],outputs('Compose-new_line'))

Step 9

Now get the field names. We know from the CSV the top header has field names. Here my CSV has 7 field values.

Click on ‘new step’ and add another compose action rename it as ‘Compose – get field names’. select the expression and here enter first([Select the outputs from the compose-split by new line) now split the result with, split(first([Select the outputs from the compose-split by new line),’,’

The result in my case is,

split(first(outputs('Compose_-_split_by_new_line')),',')

Step 10

Now add new step, and chose the ‘select’ action and underexpression it should skip the first record since the first row contains the data.

Now for the key: These should be values from the outputs ‘compose - get field names’. Note that we are getting the array values here.

value: It should be the values from the outputs of compose-split by new line’.

Since we have 7 field values, we will map the values for each field.

Second key, the expression, would be outputs('Compose_-_get_field_names')[1]

value would be split(item(),',')?[1] for the final record which is the 7th record

Key would be : ('Compose_-_get_field_names')[6]

value would be: split(item(),',')?[6]

For the first record,

For the last record,

Step 11

Now add another ‘Compose’ action to get the sample data. The schema of this sample data is needed for the ‘Parse Json’ action. The expression is taken (outputs from ‘select’, 3). This means it would select the top 3 records from the previous ‘Select’ output action.

take(body('Select'),3)

Step 12

Now save and run the flow. And copy the output from the ‘Compose – get sample data’.

Step 13

Now add ‘Parse Json’ action and configure the action

Content: It would be the output from the ‘Select’

Schema: the output payload that you have copied before. Click on ‘Generate from sample’

The final ‘Parse JSON’ should look like below.

Step 14

Now for each record in JSON file, a SharePoint list item needs to be created.

Now select the ‘Body from Parse JSON’ action item

Step 15

Inside apply to each, add SharePoint list create the item.

Configure the Site Address and the ‘List Name’ and the rest of the field values from the ‘Parse JSON’ dynamic output values. Please refer to the screen capture for reference.

Validation

Before the run, I have no items on the list.

Initially, it will ask for permission to SharePoint list, click ‘Continue’ and then click on ‘Run Flow’.

After the run, I could see the values from CSV successfully updated in the SPO list.

Conclusion

Thus, in this article, we have seen how to parse the CSV data and update the data in the SPO list. If you get stuck, you can refer to the attached flow template and check for issues. Also, make sure there are now blank values in your CSV file. If there are blank values your flow would error with message "message": "Invalid type. Expected String but got Null". In this case, go to your CSV file and delete the empty rows. The best way is to open the file in a notepad and look for blank spaces and if there are any remove them. 

References

• https://www.youtube.com/watch?v=sXdeg_6Lr3o
• https://www.tachytelic.net/2021/02/power-automate-parse-csv/