Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

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

Convert CSV to Excel - Office Scripts & Power Automate

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.

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 –

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;

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

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

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

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

    //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 (, 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

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

//Paste the data


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


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.


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:

Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).

Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

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

    • Excel Off The Grid says:

      But that doesn’t help you to automate it within Power Automate. You’re then having to go through loads of manual steps on another website.

  1. Atis says:


    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?

    • Excel Off The Grid says:

      There is an issue with your code somewhere.

      let ws = workbook.getWorksheet(sheetName)

      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?

  2. MrL says:

    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? 🙂

    • Excel Off The Grid says:

      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.

  3. Heath says:

    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”

    • Excel Off The Grid says:

      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.

  4. Maria says:

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

    Cannot read property ‘getRange’ of undefined

    Do you know what could be wrong?

  5. Jake says:

    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?

  6. Jake says:

    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.

  7. fuaun says:

    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?

    • Excel Off The Grid says:

      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.

Leave a Reply

Your email address will not be published. Required fields are marked *