Power Automate text file to SharePoint list
|
This is my solution to an Idea(Text (CSV) file Get rows) requested by Power Automate community which have 500+ upvotes. In this article, I will demonstrate how to get the CSV files Content in Power Automate, Parse the CSV using a little bit of TypeScript magic using the Excel Run Script action (yes you read it right we are going to invoke code without a third party action in Power Automate) and we will then use result from the Run script action and bulk generate documents. Scenario:Lets say you are working in a school and the teacher have filled in the grades for all students in a CSV file. Your Principle has asked you to print out Report-Cards for all the students. Please Make Sure The Following Prerequisites are in Place Before Starting:
If you prefer to watch the following steps being completed instead of reading through them, please just view the video below:Before we begin with our Power Automate configuration, let add\save our script :Step 1 : Create a new Excel file in the SharePoint Document, library. This file will add the place holder for running the script Step 2 : Open the Excel file .
function main(workbook: ExcelScript.Workbook,
csvData: string) {
var lines = csvData.split("\n");
var result = [];
var headers = lines[0].split(",");
for (var i = 1; i < lines.length; i++) {
var obj = {};
var currentline = lines[i].split(",");
for (var j = 0; j < headers.length; j++) {
obj[headers[j]] = currentline[j];
}
result.push(obj);
}
console.log(result.toString());
return JSON.stringify(result);
}
Step 3 : With reference to the scenario, this is a one time activity, s we will use the select Manually trigger. Note: You can select any trigger. For example, you can start Flow when the document is created in a SharePoint document library. Step 4 : Add the SharePoint Get file content using path and specify the path to the SharePoint Online locationfor the CSV file. Step 5 : Add the Compose action and add the formula below The formula will help us Trim the whitespaces and concatenate a comma , at the end. concat(trim(base64ToString(outputs('Get_file_content_using_path')?['body']['$content'])),',')
Step 6 : Add the Excel Run a Script action.
At this point you need to Test your Flow and copy the result of the Run Script action to a clipboard. We will use it to generate a payload for our Parse JSON action. Step 7 : Add the Parse JSON action.
Step 8 : Add the Compose action and Create an HTML and fill in the HTML template with data from the output of Parse JSON of the action. Note: The Apply to each loop will be automatically added, Sample HTML template :Report CardStudent First Name:Student Last Name: Test 1 Score : Test 2 Score : Test 3 Score : Test 4 Score : Final Score : Grade : Step 9 : Inside the Apply to each loop add the Muhimbi Convert document action.
For details see the screenshot below: Step 10 : Inside the Apply to each loop add theCreate file SharePoint action to create the PDF document in the SharePoint document library.
All Done! Start your Flow Manually and after a few seconds, the each row from the CSV document will be converted to a PDF in the destination library. Subscribe to this blogfor the latest updates about SharePoint Online, Azure, PowerShell, Power Automate (Microsoft Flow), Power Apps and document conversion and manipulation using The PDF Converter |
