Power Automate CSV to Excel (via Office Script: Easy Method)

CSV files are a common file format; they are regularly used to export data from IT systems. Excel desktop can convert CSV to Excel with ease. However, in the online world of Office 365, things aren’t quite so simple. The online world provides us with new automation opportunities not available in a desktop environment. However, unfortunately, the Power Automate CSV to Excel is trickier than it should be. But as we may encounter many scenarios where we need to convert CSV to Excel as part of an online automation process, let’s learn how to achieve this with Office Scripts doing the heaving lifting for us.

Table of Contents

• The issue with CSV files
• Office Script to parse a CSV file
• Pushing the array into an Excel workbook
• Power Automate CSV to Excel via Office Scripts
• Conclusion

In this post, we will look at a method for converting CSV into an array, then pushing that array into an Excel worksheet. As this is a two-step process, we can easily change the second step to push the data to other destinations.

In the example, the flow is executed as an instant flow which runs at the press of a button. But it could also be achieved using any of Power Automate’s other triggers for a more automated solution.

The issue with CSV files

There are two significant issues with converting CSV to Excel:

1. There is no single standard of how CSV files are encoded. Mac, Unix, and Windows environments create CSV files in slightly different formats.
2. CSV files are treated as text strings that need to be converted into an array before we can use them.

Neither of those sound like significant issues. Yet, even the example used in the Office Script documentation produces an error when parsing more complex CSV files. What should be simple turns out not to be so.

There are premium connectors in Power Automate to convert CSV to Excel. These connectors generally run faster than the method demonstrated here. Those connectors require additional license fees, so I recommend investigating premium connectors only if this method doesn’t meet your requirements.

Office Script to parse a CSV file

The following Office Script accepts a CSV file as a text string, then works through the string character by character. Any commas or new lines contained within double quotes are treated as text and ignored. But any commas and new lines used outside of quotes are used to split the string into new rows and columns.

The final output of this script is an array.

The following is based on Peter Mortensen’s code at Stack Overflow – https://stackoverflow.com/questions/1293147/example-javascript-code-to-parse-csv-data

function main(workbook: ExcelScript.Workbook,csv: string) {

  //Create variable to hold the array
  let arr: string[][] = [];

  //Create variable to be toggle inside/outside double quotes
  //true = inside, false = outside
  let insideQuote = false;

  //Create variable for the current row and colum number
  let r = 0;
  let c = 0;

  //Loop through each character in CSV String
  for (let i = 0; i < csv.length; i++) {

    //Create variable to hold current (cChr) and next (nChr) characters
    let cChr = csv[i];
    let nChr = csv[i + 1];

    //Create a new row or column arrays if empty
    arr[r] = arr[r] || [];
    arr[r][c] = arr[r][c] || '';

    //If the cChr and nChr are both double quotes, then ignore double quote
    if (cChr == '"' && insideQuote && nChr == '"') {
      arr[r][c] = arr[r][c] + cChr;
      i++;
      continue;
    }

    //If single double quotate switch the insideQuote toggle
    if (cChr == '"') {
      insideQuote = !insideQuote;
      continue;
    }

    //If a comma not inside double quotes, create a new column
    if (cChr == ',' && insideQuote == false) {
      c++;
      continue;
    }

    //If newline CRLF outside double quotes, skip next & create new line.
    if (cChr == '\r' && nChr == '\n' && insideQuote == false) {
      r++;
      c = 0;
      i++;
      continue;
    }

    //If newline LR or CR outside double quotes, create new line
    if ((cChr == '\n' || cChr == '\r') && insideQuote == false) {
      r++;
      c = 0;
      continue;
    }

    //Add unescaped characters to array
    arr[r][c] = arr[r][c] + cChr;
  }

  return {arr};

}

The script above has been saved with the name PA – Convert CSV to Array.

This script may not be the fastest way to parse a CSV file. Other methods involve Regular Expressions, but they can quickly become very complex. The example file provided in the Microsoft documentation uses Regular Expressions (https://docs.microsoft.com/en-us/office/dev/scripts/resources/samples/convert-csv), but this method fails for more complex CSV files.

I’ve opted for code that is simpler to understand and can be easily updated if errors from more complex CSV files are found.

Pushing the array into an Excel workbook

In the previous step, we converted the CSV into an array. Now we need to push that array into an Excel workbook.

The following script takes two parameters:

1. the output of the script above
2. the worksheet name where the data should be pasted.

The named worksheet is cleared before the array is pasted, starting at cell A1.

function main(workbook: ExcelScript.Workbook, strArr: string, sheetName: string) {

//Convert the strArr to an Array
let arr: string[][] = JSON.parse(strArr)

//Declare the worksheet to paste the data to
let ws = workbook.getWorksheet(sheetName)

//Clear the existing worksheet data
ws.getRange().clear(ExcelScript.ClearApplyTo.all)

//Declare the range to paste the data to
let rng = ws.getRange("A1").getAbsoluteResizedRange(arr.length,arr[0].length)

//Paste the data
rng.setValues(arr);

}

The script has been saved with the name PA – Paste Array to Excel Workbook.

Power Automate CSV to Excel via Office Scripts

With the two Office Scripts above available, we can use them within our Power Automate flow. The entire flow is shown below.

Notes

Within the Script – Convert CSV to Array step, a workbook called Blank File.xlsx is used; this workbook does nothing and can be any workbook. Office Scripts are expected to change an Excel workbook; therefore, Power Automate requires a workbook to be named. However, the script accepts a CSV file and returns an array, no wookbook changes are made, but we are still required to name one.

While two Office Scripts are used in this flow, we could combine them into a single script. Instead, they are presented here as two scripts to increase flexibility, so that the array can easily be used with other destinations.

The step named Get CSV File is the Get file contents using path that has been renamed.

Conclusion

Handling CSV files in Excel online is not as simple as we would like it to be. The Power Automate CSV to Excel conversion is not a native connector. However, this post presented a way to achieve CSV to Excel conversion without the need for premium connectors; instead, we have used the standard Power Automate and Office Scripts tools.

Related Posts:

• Introduction to Office Scripts – Toronto Excel Meetup (YouTube)
• Use Office Scripts with Power Automate

---

What next?

Discover how you can automate your work with our Excel courses and tools.