How to Convert CSV to Excel Using Power Automate? [With & Without Using Premium Connector]

When working in Power Automate, you might often need to convert a CSV file into an Excel file to make the data easier to use or share. Excel files are better for tasks like reporting and analysis, and with Power Automate, you can automate this conversion process quickly and easily.

In this tutorial, I will show you how to convert a CSV file to Excel using Power Automate, both with and without using the Premium Connector.

Convert CSV to Excel Using Power Automate

Imagine you work in a company where daily reports are sent to your Outlook inbox as CSV attachments. Your task is to automatically convert these CSV files into Excel format using Power Automate, format the data, and save the resulting Excel files to a SharePoint document library.

For this example, I use the below csv data:

Convert CSV to Excel Without Using Premium Connector

First, we will create an Office Script to convert a CSV to Excel without using the premium connector. Then, we will create a flow in Power Automate.

Create Office Script using Excel Online

We must create Office Script because Power Automate can’t directly take the CSV data and format it neatly into an Excel table. Office Script bridges this gap by:

• Reading the CSV Data: It processes the raw CSV lines.
• Inserting the Data into Excel: It organizes the data into rows and columns.
• Formatting the Table: It automatically converts the data into a properly structured Excel table.

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

1. Go to Excel Online and open a workbook. Ensure you use a workbook stored in OneDrive or SharePoint, as Office Scripts are supported only in online workbooks. For this example, I used SharePoint:

2. Click on the Automate tab in the ribbon at the top of the Excel interface (If you don’t see the Automate tab, it might need to be enabled by your organization admin). Then, click Record Actions under the Automate tab, and it will generate the script for us.

3. For this example, my CSV contains the four columns; we will write (1,2,3,4) in the first row for each column (A1:D1). Then come to send rows (2, 3,4,5), the second row for each column.

4. Then, format the data as a table using the Insert tab. After that, click the Stop button on the right side of the Excel table.

5. Click the Edit icon to check if the script has been generated. The script below includes the code for inserting data into a range and creating a new table from the 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);
}

6. Our script currently inserts two rows at once. However, we need to insert one row from the CSV data array at a time and track which data is entered into. To achieve this, we replaced the script with the following 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("EmployeeData");
}

Where:

• The main function accepts two inputs: workbook ( Represents the Excel workbook where the script will run) and lineCSV ( An array of CSV lines).
• let selectedSheet = workbook.getActiveWorksheet();: This gets the currently active worksheet in the Excel workbook where the data will be written.
• const alllines = lineCSV;: The function assigns the input lineCSV (array of CSV lines) to a variable named alllines for easier reference.
• let counter = 1;: This variable keeps track of the row number where data will be written in the Excel sheet, starting from the first row.
• for (let line of alllines) { … }: This function loops through each line in the alllines array.
• if (line.includes(“,”)) { … }: It checks if the line contains a comma. If not, it skips the line because it’s not valid CSV data.
• let items = line.split(“,”);: The line (e.g., “John,123,Smith,Doe”) is split into an array of values using the comma as a separator (e.g., [“John”, “123”, “Smith”, “Doe”]).
• selectedSheet.getRange(“A” + counter + “:D” + counter).setValues([[items[0], items[1], items[2], items[3]]]);: The function takes the split values and writes them into columns A to D of the current row (counter) in Excel.
• counter++;: After writing the current line’s data, the counter increases by 1 to move to the next row for the next line of data.
• workbook.addTable(selectedSheet.getRange(“A1:D” + counter), true).setName(“EmployeeData”);: After all the lines are processed, the function creates a table using the range from A1 to the last row (D + counter). The table is named ‘EmployeeData.’

After that, rename the script and save the script:

Make sure you delete the first and second rows in the Excel.

Create a Flow in Power Automate

1. Create an automated cloud flow. Give the flow name and select the trigger When a new email arrives (V3). Provide the below parameters:

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

2. Add Compose action, and in the input parameters, add a break line below the screenshot:

3. Add Compose action and in the input, parameter provides expression base64ToString() function in the function add Attachments content from the dynamic content of When a new email arrives (V3):

It will automatically add a for each loop.

base64ToString(item()?['contentBytes'])

4. Inside the for each loop, add a Compose action and provide the below expression:

split(outputs('Compose_1'),outputs('Compose'))

5. Add the Get File Content action from the SharePoint Connector. Select the SharePoint Site Address and the File Identifier for the “Empty Employee Data.xlsx” file.

6. Add the Create File action from the SharePoint Connector and provide below parameters:

• Site Address: Your SharePoint site.
• Folder Path: The destination folder is in the SharePoint library.
• File Name: Use dynamic content for a unique name:

Employee Data@{formatDateTime(utcNow(),'ddMMyyyy')}.xlsx

• File Content: Use the output of Get File Content.

7. Add the Run script from SharePoint library action from Excel Online (Business) and provide the below parameters:

• Location: Your SharePoint site.
• Library: Select the document library.
• Workbook: Use the Id from the Create File action.
• Script Location: Your SharePoint or OneDrive site where the script is stored.
• Script Name: Choose the script you created earlier.
• lineCSV: Switch to array mode and select the output from Compose (split lines).

Now, it’s time to save the flow. Click on Test -> Select Manually. Send an email to the configured inbox with a CSV file attachment.

 After the flow runs successfully, Check the SharePoint library to confirm that the Excel file has been created and formatted correctly.

Convert CSV to Excel Using Premium Connector

In the previous example, we saw how to convert a CSV file to an Excel file without using the Premium Connector. However, the long process involved creating scripts and performing multiple actions. Now, let me show you how to Convert CSV to Excel using the Premium Connector with the Encodian Convert Excel action, simplifying the process significantly.

To use Encodian actions, you need an API key. To get the API key, go to this link and fill out the form to activate the free trial. It will generate an API key for you.

Now follow the below steps:

1. Create an automated cloud flow. Give the flow name and select the trigger When a new email arrives (V3). Provide the below parameters:

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

2. Add the Convert – Excel action from Encodian and provide the below parameters:

• 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.

It will automatically add a for each loop.

3. Inside the for each loop, add create file action from SharePoint Connector and provide the below parameters:

• Site Address: Your SharePoint site.
• Folder Path: The destination folder is in the SharePoint library.
• File Name: Use dynamic content for a unique name:

Employee Data@{formatDateTime(utcNow(),'ddMMyyyy')}.xlsx

• File Content: Use the output of Get File Content.

Now, it’s time to save the flow. Click on Test -> Select Manually. Send an email to the configured inbox with a CSV file attachment.

 After the flow runs successfully, Check the SharePoint library to confirm that the Excel file has been created and formatted correctly.

Conclusion

This tutorial covered two methods to convert a CSV file into an Excel file using Power Automate. First, we explored achieving this without the Premium Connector by creating and using an Office Script to process and format the CSV data into an Excel table. This method involves additional steps like scripting and configuring multiple actions.

Additionally, we learned how to simplify the process using the Premium Connector with the Encodian Convert Excel action, which makes the conversion faster and easier with fewer steps.

You may also like:

• Create a CSV Table in Power Automate
• Convert CSV to Array in Power Automate
• Convert Word Files to PDF Using Power Automate
• Convert SharePoint List Item to PDF Using Power Automate
• Convert XML to CSV Using Power Automate

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.