Get Rows(Parse) from CSV file using Power Automate and bulk generate documents

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:
  • An Office 365 subscription with SharePoint Online license.
  • Muhimbi PDF Converter Services Online full, free or trial subscription(Sign up).
  • Appropriate privileges to create Power Automate(Flow).
  • Office Scripts enabled in your organization.
  • Working knowledge of SharePoint Online, Office Scripts and Power Automate.
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 .

  • Navigate to the ‘Automate‘ tab and select ‘All Scripts’.
  • Click on ‘New Script’.
  • Name the script ‘csvtoJSON
  • Paste the script below and save the script. You can download the script at link.
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 location for 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.

  • Location, Document Library and File – Select an Excel file that we created in Step 1..
  • Script – Select the script(csvJSON) that we added in Step 1.
  • csvData – Select the ‘Output‘ of the Compose action from Step 5.

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.

  • Content – Add the ‘result‘ the output of the ‘Run Script‘ action.
  • Click on ‘Generate from Sample‘ and copy the text from your clipboard.

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 :
<html>
<body>
<h2>Report Card</h2>
Student First Name:<br />
Student Last Name:<br />
Test 1 Score :<br />
Test 2 Score :<br />
Test 3 Score :<br />
Test 4 Score :<br />
Final Score :<br />
Grade :<br />
</body>
</html>

Step 9 : Inside the ‘Apply to each‘ loop add the Muhimbi Convert document‘ action.

  • Source file name: Pass in the output of the ‘Parse JSON’ action ‘First Name’-‘Last Name’.html.
  • Source file content: Enter the output of the ‘Compose‘ action (HTML we created in step 8);
  • Output Format: PDF‘. Note: If you want to generate a Word document you can select ‘DOCX’.

For details see the screenshot below:

Step 10 : Inside the ‘Apply to each loop ‘ add the ‘Create file‘ SharePoint action to create the PDF document in the SharePoint document library.

  • Folder Path: Specify the output path to write the PDF file to.
  • File Name: ‘Base File Name‘.pdf  is the output variable of the ‘Convert document‘ action.
  • File Content: Processed file content‘ is the output variable of the ‘Convert document‘ action.

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 blog for the latest updates about SharePoint Online, Azure, PowerShell, Power Automate (Microsoft Flow), Power Apps and document conversion and manipulation using The PDF Converter

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s