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

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.

Power Automate flow

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:


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

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

22 thoughts on “Power Automate CSV to Excel (via Office Script: Easy Method)”

  1. Hey!

    I am just trying to automate some things here, and on the 2nd script I am receiving this error:
    Runtime error: Line 10: Cannot read property ‘getRange’ of undefined

    Is there a way around this error?

    Reply
    • There is an issue with your code somewhere.

      let ws = workbook.getWorksheet(sheetName)
      ws.getRange().clear(ExcelScript.ClearApplyTo.all)

      The only thing the above code does is clear the worksheet, where the worksheet name is based on the sheetName parameter.

      Have you passed a valid sheet name into the script in PowerAutomate?

      Reply
  2. I’m having a problem in that I can’t access the body property of my .csv file.

    I’ve assumed the second action in your flow is “Get file content from path”, which only gives “File content” as the available dynamic content to plug into the first script.

    Where am I going wrong? πŸ™‚

    Reply
    • If you use “File Content” as the dynamic content it should work fine. I believe this is what I selected, but Power Automate changed it to “Body” by itself.

      Reply
  3. Hi, Under the Using office scripts in power automate, during the Script – Paste Array to Excel Sheet step i do not have any dynamic content for “arr”, I have run the flow without the 2nd script and still do not have the dynamic content for “arr”

    Reply
    • Arr is returned from the first script. If you start searching for it in the Dynamic Content box it should appear, even if you can’t find it initially.

      Reply
  4. Hi. Thanks for your post.
    The 2nd script is showing this error:

    Cannot read property ‘getRange’ of undefined
    clientRequestId

    Do you know what could be wrong?

    Reply
  5. This was working well for me at one point, but now I keep getting this error:

    We were unable to run the script. Please try again.
    Office JS error: Line 16: Range setValues: The number of rows or columns in the input array doesn’t match the size or dimensions of the range.

    Any ideas?

    Reply
  6. I keep getting an error on the second script:

    We were unable to run the script. Please try again.
    Office JS error: Line 16: Range setValues: The number of rows or columns in the input array doesn’t match the size or dimensions of the range.

    Any thoughts? The workbook I’m pasting into is completely blank.

    Reply
  7. thanks for this post! it worked perfectly well for me. Until recently the dates randomly converted into the wrong format (i.e. dd/mm/yyyy wrongly converted to mm/dd/yyyy). however, it doesn’t do that to all the dates column. How do i resolve this?

    Reply
    • That sounds like a regional setting in Excel or Power Automate which is automatically converting the dates. But I’m not sure where that setting is.

      Reply

Leave a Comment