Get Rows from CSV file using Power Automate

hello everyone and a very warm welcome to today's tutorial in today's tutorial we'll go ahead and see how we can get rows from a csv file using power rma my name is clavin and i work as a technical consultant you can find my blogs at clevenfernander.wordpress.com and my twitter handle is at the rate cleveland fernandes so without wasting any further time let's get into the demo so before i start the demo i want you to see that there is a request in the power automate community which has got 521 upvotes and we are going to go ahead and work out this scenario today so let's go ahead and start with the csv file this is the csv file that i'm going to use and this csv file is pretty basic it go it has few columns and if you go ahead and see the name of the csv file it tells you that it's exam grade so let's go ahead and start with the scenario let's consider that your principal has given you a csv file which contains the grade of students okay test one test two test three test four and test five and you need to go ahead and create a report card and you need to go ahead and bulk create the report cards right here you see that there are only nine students but in the real life scenario there will be like 100 200 or maybe like 500 students so we you need to go ahead and automate this task so as a power user or as a citizen developer you need to go ahead and architect a solution so let me go ahead and first of all upload this particular file to my sharepoint now i want to go ahead and automate the tab so i know that my file is in sharepoint and this is a one-time process so let's consider i use the manual trigger so i have gone ahead and selected the manual trigger what next the next thing that comes to my mind is to go ahead and get the file from my sharepoint into power automate so that's the first thing that i want to do i'll use the sharepoint action and i'll say get file content using path i'll go ahead and type in my site name so my site name is power automate tutorials and i'll go ahead and manually get the file so the file is in the get rows demo folder so the file is here now let me add a compose action pass in the output file content i'll give my flow a meaningful name excel grades and i'll go ahead and save it and i'll test it so here i have the content right i have the content but the content is in base64 i don't want the content in base64 i want the content as a string so i have got inbuilt functions out here which i can use so i can say base64 to string i can just go ahead and pass the file content but remember we need the content because it's also inferring the content type we don't need it so i'll just go ahead and pass in the content so this looks good i can hit ok i'll rerun my flow so now if you see i have what i need right all the files or all the data which was there in my csv file is now in my power automate so first job is done but now if i go ahead and as you know excel does not have get rows and we want to pass this so it has get row but it gets row from the xlx file or there is no get rows right and there is nothing like pass csv as well so what do i do what do i do any ideas so the first thing that comes to my mind if power automate can go ahead and support code how easy it would have been to go ahead and transform the csv into the json and here comes something comes to my mind if i go to my excel and if i search for run script oh i can run scripts i can go ahead and run scripts or you can say code for my excel file and i think that's the idea out here so what i'll do is that i'll go ahead and create an excel file and i'll try to pass in the data that i had in my compose to this file so let me go ahead and create a script with which i can go ahead and transform my data my excel data to jason so i'm going to go ahead and copy a code snippet out here now don't worry about the code snippet because i'm going to go ahead and provide you just one thing out here the code snippet is in javascript or you can say in typescript so if you need something which is not available you can always go ahead and google so here if you see i'm going ahead and passing a parameter known as csv data so the csv data will be the data that we are going to go ahead and pass in from a power automate to the excel script and i'm going to go ahead and return the result to power automate so let me go ahead and name my script give it a meaningful name so csv and i'll go ahead and save my script now i'll go back to my power automate i'll click run the script in the meantime i can go here i can go to the location i can say power automate tutorials that's the sharepoint site second i'm going to go ahead and select the document library so it's going to be getrogedemo now i'm going to go ahead and select my file so it's said to be book.xlsx and here i can go ahead and select my script okay so let me go ahead and search for my script so it's csv to jason and let me go ahead and just expand it once so here you see there is a placeholder where i can go ahead and pass in my csv data so what i'll do is that i'll just say csv data i'll just rename this so that we don't get confused and i'll pass the output of the csv data out here now what next next i just need to go ahead and run the script so let me go ahead and test it so let me manually start it let's see how does it work and my script ran so here if you see and if i copy the output onto a notepad you'll see that the output is in the form of a json array isn't that what we need yes indeed right we need an output in the form of json but if you see there's something wrong the last name is empty so there is an empty field that's coming in so why is that happening so if you have a close look at the csv i see that the final a after the a grade i don't see a semicolon i also go ahead and see that there is a there's a white space so i need to get rid of those two things so let me go ahead and clean this up a little so first and foremost what do i need i first need to go ahead and trim so i need to trim the text so i'll just go ahead and trim this and next what i need to do i need to concatenate a comma right and i think that is pretty much it so if everything works now we should have a clean csv file so i'll just copy this and i'll put it on a notepad out here so that you can go ahead and see so first what do i do i go ahead and convert the output content into a string then i go ahead and trim white spaces and finally i go ahead and concat it with a comma so now let me go ahead and run save it and let me again test it manually and let's see if we get the correct output without the extra last name field so a flow has ran and now if i copy this i'll put it on the same notepad so that we can see the difference you see out here now we don't have a last name field so we have a clean looking json now that we have a clean looking json getting out rows from the json is pretty easy right i can go ahead and use the parse json action i can go ahead and pass this output as a payload and generate a payload from it so i'll hit done i'll pass the output results out here and now next thing is that i want to go ahead and create a document so i want to create a pdf or i want to create an html or i want to create a word all the three scenarios are possible but for this demo we'll create an html and we'll go ahead and convert it into a pdf so let me go ahead and add the compose action first and i rename this action to html score card and i'll paste a sample html that i have created so the html is pretty simple it just goes ahead and gets the student's first name so what i can do i can use the output of the pass json action and this is how i can get individual rows see it adds a loop automatically around it so i'm adding the first name i'm adding the last name i'm adding test1 i'm adding scores from test2 and these are all the columns if you remember from my csv file so here i have my scorecard created right now i want to go ahead and generate a pdf out of it so if i want to generate a pdf i can use the mohembi convert document action so here i can pass in the file name so which is something like first name dash last name dot html we'll go ahead and pass the output from the html scorecard action to the to the convert document action and finally we'll go ahead and create we'll go ahead and create a file in a sharepoint library so again i'll quickly type in my sharepoint site name it's power automate tutorial the folder path would be great great pdf the name will be base file name and it will be dot pdf and the file content would be the processed file content so here i'll go ahead and save my flow and just to show you the grade pdf folder is empty so i'll go ahead and test my flow so it's looping in so if you see it is looped in nine times so it has gone ahead and got the rows so if i go ahead and open this file which has exam grades you'll see that it has got nine values okay so that's why you have got nine loops it's going to go nine times and go and it will go ahead and create nine files in our grade folder so here you see it's creating it's creating something like alpha alpha so let me check if there's something like okay something went wrong it should be alpha i'll use but i think i went ahead and passed in the wrong thing but let our flow complete and then we'll go ahead and check okay so time to check why it created wrong name so let me just quickly check i use the last name last name so instead of that i can just go ahead and do first name last name so now you see that the names are coming right so let me go ahead and open this file and if you see here is a report card there is the first name last name this course that happened and finally the grade and if we compare here the grade is d minus bad student s and the final score is 49 so if i go here the final score is 49 and it's d minus similarly i should have all the nine files out here so this is how easy it is to go ahead and pass csv file or you can say get rows from the csv file using a little bit sprinkle of javascript using run script in excel i hope this was informative thank you for listening bye bye for now

Channel: Clavin Fernandes

Views: 3,409

Rating: 5 out of 5

Keywords:

Id: fI9S5781P3o

Length: 15min 12sec (912 seconds)

Published: Fri Apr 23 2021