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.

Using power automate convert csv to excel file

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
Using power automate convert csv file  to excel file
  • Create an Excel file in the SharePoint library and renamed it as Empty workbook.xlsx.
Using power automate convert csv file to excel file
  • 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.
power automate script to convert csv to excel
  • 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.
Microsoft power automate script to convert csv to excel
  • 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.
Microsoft power automate script to convert csv to excel file

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);
}
Power Automate script to convert csv to excel file

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");
}
Power Automate script to convert csv file to excel file

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().
See also  How to Show Zero Values in Power BI Bar Chart?

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

Microsoft Flow script to convert csv file to excel file

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

Microsoft Flow to convert csv file to excel file

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.

power automate convert csv to excel

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

Microsoft power automate convert csv to excel

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.
Microsoft power automate convert csv to excel file

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.
convert csv to excel power automate

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.
convert csv to excel Microsoft power automate

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'])
convert csv to excel file Microsoft power automate

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'))
convert csv file to excel file Microsoft power automate

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.
How to convert csv file to excel file Microsoft power automate

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.
How to convert csv file to excel file power automate

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.
convert csv file to excel file power automate

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.

See also  How to Show Zero Values in Power BI Bar Chart?

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

MS power automate convert csv file to excel file

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

convert csv file to excel file in Microsoft power automate

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.

Using power automate convert csv to excel

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.

power automate convert csv to excel

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

Microsoft power automate convert csv to excel

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.
Microsoft power automate convert csv to excel file

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.
Microsoft power automate convert csv file to excel file

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.
power automate convert csv file to excel file

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.

See also  How to Sort a Power Apps Dropdown Control?

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

MS power automate convert csv file to excel file

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

MS power automate convert csv to excel

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:

>