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

CSV to Excel: Power Automate & Office Scripts

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. 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. We will use Power Automate and Office Scripts to do the heavy 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 – 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.

Using Office Scripts in Power Automate

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.

Conclusion

Handling CSV files in Excel online is not as simple as we would like it to be. 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.

Want to know more about using Power Automate? Check out these resources:



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


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet 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:

Leave a Reply

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