How to Convert CSV to Excel using Power Automate?

In this Power Automate tutorial, we will see how to convert a CSV file to an Excel file using Power Automate.

There are two ways we can convert CSV to Excel using Power Automate:

• Using Office script
• Using Encodian action i.e. Convert Excel

Power Automate Convert CSV to Excel [Using Office script]

Here we will see how to convert a CSV file to an Excel file using an office script in Power Automate.

For this, we will create an Automated Cloud flow. The flow will trigger when an email receives in the Outlook inbox with an attachment. Then we will get attachments, create an Excel file in SharePoint Library and insert data in Excel by using an office script.

In this example, first, we will follow the below steps.

• First, we will create an office script
• Then we will create a flow in Power Automate.

Create Office script

To create an office script in Excel, follow the below steps.

As our CSV file contains 4 columns like the below:

• User name
• Identifier
• First name
• Last name

• Create an Excel file in the SharePoint library and renamed it as Empty workbook.xlsx.

• Open the workbook, click on Automate tab -> click on Record action. Using record action we can record the step we will implement in Excel, and after that, it will generate the script for us.

• As our CSV contains the 4 columns, we will write {1,2,3,4,) in the first row for each column (A1:D1). Then come to send row {2, 3,4,5} second row for each column.

• Then click on Insert tab -> click on Table, select My table as header, and click on Ok. Now the Excel sheet is formatted as a table.

Once done, click on the Stop button on the right side of the Excel table. You can see Excel recodes the steps.

Then click on the edit icon to see the script is generated for you.

The below script contains the code for inserting data into a range and creating a new table as a range.

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range A1:D2 on selectedSheet
	selectedSheet.getRange("A1:D2").setValues([["1","2","3","4"],["2","3","4","5"]]);
	// Add a new table at range A1:D2 on selectedSheet
	let newTable = workbook.addTable(selectedSheet.getRange("A1:D2"), true);
}

As our script contains a script that will insert two rows. But we must insert one row from the CSV data array at a time. Also, want to track in which row when data is entered.

So, for this, we replaced the script code with the below code:

function main(workbook: ExcelScript.Workbook, lineCSV: string[]) {
	let selectedSheet = workbook.getActiveWorksheet();
	const alllines = lineCSV;
	let counter = 1;
	for (let line of alllines) {
		if (line.includes(",")) {
			let items = line.split(",");
			selectedSheet.getRange("A" + counter + ":D" + counter).setValues([[items[0], items[1], items[2], items[3]]]);
			counter++;
		}
	}
	workbook.addTable(selectedSheet.getRange("A1:D" + counter), true).setName("UserNames");
}

Explanation:

• The main function accepts two parameters: workbook (which represents an Excel workbook) and lineCSV (an array of strings).
• It begins by retrieving the currently active worksheet in the workbook and assigning it to the variable selectedSheet.
• It declares a new variable named alllines to hold the lineCSV array. Each element in lineCSV represents a comma-separated list of values.
• To keep track of the row number in the Excel sheet, initialized a counter variable.
• It then begins a loop that goes through each line in the alllines array.
• It checks if the line has a comma within the loop. If it does, it indicates that it contains multiple values.
• If the line contains a comma, it divides it into an array of items using the comma as a separator.
• The function gets the items array and assigns its elements to specific cells on the selectedSheet’s Excel sheet. It detects the row number using the counter variable and writes the values in columns A to D.
• It increments the counter (counter ++) after writing the values so that the next set of values will be written to the next row on the sheet.
• The function adds a new table to the selectedSheet whenever the loop has finished applying all lines with commas. The table contains the values typed in cells from A1 to the last row D (using the counter variable).
• “UserNames” is the name of this table which set using setName().

After that, rename the script and save the script in the SharePoint library. A script by default, save in the OneDrive.

To save in the SharePoint library, select the SharePoint site under ‘ Quick access’ and then select the Library you want to save.

As our script is ready, now, we will create our flow in Power Automate.

Create a flow in Power Automate

Step 1: Log in to Power Automate and click +Create -> select the Automated Cloud Flow.

Then provide the flow name and select the trigger action as ‘When a new email arrives (V3)’. Click on Create.

After that you can see ‘When a new email arrives (V3)’ action is added to the flow page. Then Provide the below information:

• Folder: Select the Folder as Inbox.
• Include Attachments: Set the include attachments as Yes.
• Only with Attachments: Set the only with attachments as Yes.

Step 2: Click on the +New step -> select Compose action. Then provide the below information.

• Inputs: Click on enter, to go to the next line.

Step 3: Now we will get the Attachments, so click on the +New step -> select Get Attachment(V2) action. Then provide the below information:

• Message Id: Select the Id from dynamic content. That will automatically add Apply to each action.
• Attachment id: Select the Attachment Id from dynamic content.

Step 4: Now, we will convert the Attachment content to base64string. So, click on the Compose action. Then provide the below information:

• Inputs: provide the below expression:

base64ToString(outputs('Get_Attachment_(V2)')?['body/contentBytes'])

Step 5: Next, we will split the above output with a line break. Click on the Add an action -> select Compose action. Then provide the below information:

split(outputs('Compose_5'),outputs('Compose-_Line_break'))

Step 6: Now we will get the file content of empty excel file, so click on the Add an action -> select Get file content action. Then provide the below information:

• Site address: Select the SharePoint site address.
• File Identifier: Select or provide the file Identifier.

Step 7: Now we will create an Excel file, so click on Add an action -> select Create File action. Then provide the below information:

• Site address: Select or provide the SharePoint site address.
• Folder path: Select or Provide the folder path
• File name: provide the file name. Here i have provided the name like below:

Username@{guid()}.xlsx

• File content: Provide the file content from dynamic content.

Step 8: We will run the script on top of the above excel file. For this click on Add an action -> select Run script from SharePoint library action. Then provide the below information.

• Workbook Location: Select the workbook location
• Workbook Library: Select the workbook library
• Workbook: Select the Id from the dynamic content
• Script Location: Select the Script location.
• Script Library: Select the script library.
• Script: Select the script
• lineCSV: Click on the T icon to switch to the entire array mode. Then select the output from dynamic content.

Step 9: To test the flow click on Test icon, click on the Test icon-> select Manually -> click on Test -> configure the action with connectors, then click on Run flow -> Then click on Done.

Now send an email to the inbox of Outlook, once the email is received flow trigger and run successfully.

In the defined SharePoint Library, the excel file got created with the data.

This is how we can convert csv to excel file using office script in Power Automate.

Convert CSV to Excel in Power Automate [Using Encodian action]

Here we will see how to convert a CSV file to an Excel file using the Convert Excel action from Encodian in Power Automate.

For this, we will create an Automated Cloud flow. The flow will trigger when an email receives in the Outlook inbox with an attachment. Then we will convert the Attachment CSV file (.csv)to an Excel file (.xlsx). Then we will create the Excel file in the SharePoint library.

We will use the ‘Convert Excel’ action from Encodian to convert the CSV file to an Excel file using Power Automate.

To use this action from Encodian, we need API Key; you will get this by filling out the form to set up the free trial. When you will add the action to the flow, use the API Key and configure it.

Before creating the flow get the API key and upload the CSV file in the SharePoint library.

Step 1: Log in to Power Automate and click +Create -> select the Automated Cloud Flow.

Then provide the flow name and select the trigger action as ‘When a new email arrives (V3)’. Click on Create.

After that you can see ‘When a new email arrives (V3)‘ action is added to the flow page. Then Provide the below information:

• Folder: Select the Folder as Inbox.
• Include Attachments: Set the include attachments as Yes.
• Only with Attachments: Set the only with attachments as Yes.

Step 2: We will convert the CSV to excel, so, click on the +New step -> select the Convert Excel action. Then provide the below information.

• Output Format: Provide the output format as XLSX
• File name: Select Attachment name from dynamic content.
• File content: Select the Attachment content from dynamic content.

Step 3: Next, we will create an Excel file in the SharePoint library, so click on the +New step -> select Create File action. Then provide the below information:

• Site address: Select or provide a SharePoint site address.
• Folder path: provide the folder path.
• File name: Provide the file name. In this case, provided the file name like this

UserNames@{guid()}.xlsx

• File content: Provide the file content from dynamic content.

Step 4: To test the flow, click on Test icon, click on the Test icon-> select Manually -> click on Test -> configure the action with connectors, then click on Run flow -> Then click on Done.

Now send an email to the inbox Outlook, once the email is received flow trigger and run successfully.

In the defined SharePoint Library excel file is created with the data.

This is how to convert CSV to Excel files using Convert Excel from Encodian in Power Automate.

Conclusion

In this Power Automate tutorial, we saw how to convert CSV to Excel files using Power Automate.

Here we cover two methods to convert CSV to Excel.

• Using Office script
• Using Encodian action i.e. Convert Excel

You may also like:

• Create an Array from SharePoint list Items using Power Automate
• Get Attachments from a SharePoint list item using Power Automate
• Add Attachments to SharePoint List Items using Power Automate