Quickly Import CSV Data into a SharePoint List with Power Automate

want to quickly get your CSV data into a SharePoint list most platforms offer a CSV export for leads event RSVPs contact info you name it although you can copy and paste data from a CSV file into a SharePoint list unless the columns in your CSV file are in the same order as your SharePoint list there may be a bit of manual work required automate that this power automate flow will take this CSV file of event RSVPs and import it into a SharePoint list if you're interested in learning how to build this automation keep watching be sure to stick around till the end of the video where I'll go over an alternative workflow that involves an Excel table timestamps are included in the description box below feel free to skip ahead on my SharePoint site I have a SharePoint list for my rscps I downloaded a CSV export file and I've saved it to a synced SharePoint folder from my experience it's best to edit the CSV files in the Excel desktop app when using them with a power automate flow here in the allergies column there are a few cells that contain commas which will cause issues in our flow first I'm going to replace all the commas with a semicolon back in SharePoint I can see that my file has updated in power automate I'm going to create a new instant Cloud flow give your flow a name so you can find it later I'm going to trigger this flow manually for now add a get file content using path action I'll select my site and navigate to the CSV file add a compose action this compose action will be used to convert the file content to a string add a string expression and insert the outputs from the get file content action here add another compose action remember to rename your actions to keep things organized a line break is found at the end of each row this compose action will hold that line break your cursor into the inputs field and press the return key once add another compose action this compose action will split each row in the CSV file add a split expression insert the outputs from the CSV content compose action here insert a comma and then insert the outputs from the line break action here add another compose action this compose action will grab the first line of the CSV file which will return the column names of the CSV add a first expression and insert the outputs from the split rows compose action here wrap this expression in a split expression add a comma a single quote and another comma and close it off with a bracket I'm going to test this flow now to see the output of these compose actions in this first compose action the outputs are the contents of the CSV file as a string in the split rows compose action the CSV content was split by a line break which is indicated here by a backslash and an r in the column name compose action we've taken the first row from the previous compose action which gives us the column headers add a select action since the first line of the CSV data is the column header I'll want to skip that line I'll use a skip expression and insert the outputs from the split rows compose action here add a comma and a 1. for the map properties in the select action the column header needs to go into the key field the row data will go into the value field let's take a look at the outputs from the previous compose action array positions start at zero in my SharePoint list I need the first name the last name the number of guests email address and food allergies in the first key field I'm going to add an expression I'll insert the number 2 in square brackets for the last name I'll place my cursor before the square bracket and I'll insert the outputs of the column names compose action here in the value field insert a split expression next insert an item expression the item expression is used to access data I've specified in the from field in this case it's accessing the outputs of the split rows compose action except for the first row add a comma single quotes and insert a comma between the single quotes this split expression will split each item returned which is essentially a row in the CSV file and two individual pieces at the comma place your cursor outside of the split expression add a question mark add square brackets and insert a 2 in between this number here should match the number you entered for the key this will select the third piece in each item in this case it's selecting the last name I'm going to repeat this for all the columns I want to import into my SharePoint list by copying and pasting the expressions from the key and value field and replacing the number within the square brackets remember that each row in the map here represents a column of data in the CSV file I'm going to run another test to review the output here in the select outputs it looks like all the data has split correctly I'm going to take the first few rows here and copy it to my clipboard next I'm going to add a parse Json action I'm going to insert the select outputs here and click on generate from sample I'll paste the data that I have on my clipboard I'm going to remove the last comma and close off the square brackets next add and apply to each action insert the body from the parse Json action here add a compose action I'll use this compose action to combine the first and last name which I'll insert into the title column of my SharePoint list under the parse Json header you can see all the columns from my CSV file that I selected in the select action of this flow I'll select first name add a space and then select the last name add another compose action to store the number of guests add a create item action I'll select my site and my list name I'll insert the output of the full name compose action here the first name will go here and the last name here this column in SharePoint stores a number insert an INT expression insert the output from the number of guests compose action here I'll insert the email address here and the food allergies here I'll give that a test that took only one minute to run you can see in the SharePoint list that all the data has been imported since we're not using any variables in this flow we can turn on the concurrency control to reduce the overall duration of the flow click on the three dots click on settings and toggle concurrency control on I'm just going to leave it at 20 press done save that again I'm going to delete my items in SharePoint and we'll give that another test and so with the concurrency control turned on that only took five seconds this CSV only has 50 rows of data imagine if you had a couple hundred rows of data this flow would save you a ton of time now that I've confirmed that the flow works I'm going to adjust the trigger of my flow to trigger on a selected file replace the manually trigger a flow action with a four selected file action select the site address and Library name next add a get file properties action select the site address and Library name insert the ID from the previous action we're going to delete this action and we're going to insert a get file content action select the site address and insert the file identifier from the previous action in the compose action replace the original output with the output from the get file content action I'm going to save the flow and give it a test and I'll navigate to my CSV file from here click the three dots hover over automate sometimes you have to give it a second or two for the automations to show up I'll click import rscps to run my automation here you can see that the flow is run successfully now I can trigger this flow from any file in this document Library it will only work for a CSV file though you'll also want to ensure that The Columns of your CSV file are the exact same each time if the columns in your CSV file change you'll have to adjust your flow to ensure you're getting the correct data one thing I'd like to do with the flows that are triggered from a selected file or List item action is send myself a notification in teams this lets me know that the flow ran successfully in this adaptive card I'll also include a link to the SharePoint list to make it easy for me to view if I need to first I'll add a get my profile V2 action which will allow me to retrieve the email of the user who has run the Flow by adding this action it makes it easy to share the flow with anyone from my team or organization at the bottom of my flow I'll add four compose actions the first one will hold the card title I like to use compose action for my adaptive card so that it makes it easy for me to change the card content when I need to next compose action will be for the card body I'd like to include the total number of RSVPs that are added to the SharePoint list for that I'll use a length expression and I'll insert the outputs from the select action here this will count the number of rows which is essentially the number of items that will be added to the SharePoint list I'd like the number of RSVPs to be bolded I'll wrap the number with two asterisks on either side next I'll add a compose action for the text on the button and one last compose action for the button URL for my button I'm going to use the buy created date view this sorts all my list items by create a date in descending order I'll copy the URL and insert into the button URL compose action lastly I'm going to add a post adoptive card in a chat or Channel action I'm going to post as a flowbot and I'm going to post in a chat with the flowbot in the recipient field I'm going to insert the mail Dynamic content from the get my profile action I'm going to copy and paste the Json I've Linked In the card description box into this field the card title goes here the body here the button text here and the button URL here going to save this flow and we'll give this a test another way you can get your CSV data into a SharePoint list is by copying it into an Excel table even though it's an extra step the flow is a bit more streamlined let me show you I have this Excel file with the table it has the same columns as my CSV file I'll copy the data from my CSV file into my Excel table in power automate I'll delete this trigger and insert the manual trigger next insert the list rows present in a table action I'll select my SharePoint site and document Library I'll navigate to my file and select the table I'll delete these next two actions I'll delete all these compose actions too in the apply to each action I'll insert the value from the list rows present in table action I'll insert the first and last name into this compose action and the number of guests into this compose action I'll insert the first name here last name here email address and food allergies since the title and number of guests use the outputs from the compose actions above I don't need to do anything with these fields I'll save my flow and give that a test with this workflow I'm going to use the same Excel file to import my data into my SharePoint list instead of having to adjust my flow each time I want to use a different Excel file copying and pasting the data into the same Excel file each time will streamline this workflow which flow would you choose to import data from a CSV file into a SharePoint list let me know in the comments down below if you found this power automate tutorial helpful please consider giving it a like don't forget to subscribe so you don't miss out on any other power automate tutorials or time saving workflows thanks for watching

Channel: A Creative Opinion

Views: 13,814

Keywords: PowerAutomate, CSV to JSON, CSV Import, Share Point List

Id: pmQkeFeSkiw

Length: 10min 48sec (648 seconds)

Published: Tue Mar 07 2023