Excel Office Script - Unable to copy data less than 100K rows from csv to excel file

[cob2020]

Hi,
It has been a while since the last time I sought for help. I have been testing a power automate flow which only job is to fetch information from a csv file to an excel file... the last activity of the workflow calls the 'run scripts' step which triggers an excel office script from an excel template. The flow works with files =< 6K rows, but fails when the rows exceed that count. Based on documentation, I became aware that this is a limitation, but that there is a way to work around it by copying the data in smaller batches.

The only problem is that the person that suggested the solution and showed how to do it... did it with randomly generated data and not with an actual file.

The video can be watched here => copy_in_batches_video
His script for copying data in batches is here => copy_in_batches_script

This is the script I'm currently using in my flow... so my plead is... does anyone know Excel office script and how to integrate the script below with his script to be able to copy larger files?

function main(workbook: ExcelScript.Workbook, csv: string) {
  let sheet = workbook.getWorksheet("Sheet1");

  /* Convert the CSV data into a 2D array. */
  // Trim the trailing new line.
  csv = csv.trim();

  // Split each line into a row.
  let rows = csv.split("\r\n");
  rows.forEach((value, index) => {
    /*
     * For each row, match the comma-separated sections.
     */
    let row = value.match(/(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g);

    // Remove the preceding comma.
    row.forEach((cell, index) => {
      row[index] = cell.indexOf(",") === 0 ? cell.substr(1) : cell;
    });

    // Create a 2D-array with one row.
    let data: string[][] = [];
    data.push(row);

    // Put the data in the worksheet.
    let range = sheet.getRangeByIndexes(index, 0, 1, data[0].length);
    range.setValues(data);
  });
// Credit to AlexJerabek => https://docs.microsoft.com/en-us/office/dev/scripts/resources/samples/convert-csv
}

 

[Siddharth Rout]

Forget the script for a moment. What exactly are you trying to achieve? Please explain in exact steps what needs to be achieved? For example Open file, Filter data based on a condition, Copy data etc etc.

 

[cob2020]

Thx for replying... let me be brief and as clear as possible... in general I would like to copy the content of a .csv file into an excel file for my users to consume. The .csv file is being generated from an Azure Data Factory pipeline. The pipeline gets the contents of a view and places the contents in a .csv file in a specific container (Azure Data Factory does not offer the option to send information to an excel document, thus the reason for my flow). My flow does the following:
1. Flow is triggered once an Azure Data Factory container is updated with a .csv file
2. The content of the file is retrieved and converted into a string with a 'Compose' step
3. An empty excel file is created to receive the content of the .csv file
4. The 'Run Script' step is called among other things to copy the .csv content into the .xlsx file

The 4th step works for smaller files =< 6K rows, but fails if the file is larger... in my case the file I'm trying to copy is 33K rows and only has 5 columns.
The video and script that I shared shows that it is possible to copy data in smaller chunks/batches, thus my reason for seeking help.
I more or less understand the script, but don't have any idea how to integrate the script I'm using with the script that is able to copy data in batches. I know the larger script will remain intact, because it was written with functions... and the last function that creates the data randomly can be commented out, because it is not going to be used... the key... or where the change needs to be made is on lines 8 through 21 of the larger script, but I have not been successful writing it to make it work.

 

[Siddharth Rout]

So basically it is not a .Csv file per se but a string which holds data in delimited format which you want to write to Excel? Also you want a Script or can VBA code also help?

If you are open to VBA, then one option that I can think of is writing all that data to a CSV file. It is like writing data to a text file and then use Workbooks.Open to open the .csv file and then resave it as .xlsx use .SaveAs. Let Excel do all the dirty work?

 

[Marc L]

The flow works with files =< 6K rows, but fails when the rows exceed that count.

Hi, no issue to import any text file with more than 6K rows directly in Excel or via a VBA procedure …​

​

Your step 2 is a non sense …​

 

[cob2020]

Thx for the replies... when the content in the .csv file is retrieved in the flow it is encoded in base64... I'm using an expression in the compose step to convert it into a string

base64ToString(body('Get_file_content').$content)

I would be interested to know how vba procedures can be executed from a flow.

In the meantime, I will continue to work on the javascript code until it works, at which point I will post the final code for anyone in the future encountering the same issue as me.

 

[Marc L]

​

Try to store each line in an array rather than a whole string …​

 

[cob2020]

Thx to all that replied and tried to help. I was able to use the same code I had. The only thing I tweaked was the regex in line 15 to allow commas as part of the values within the double quotes and also made sure my csv file had commas as the column delimiters (if the input has any other character as the delimiter, it will not work). This script was made by the Microsoft team and this is the link to the post => Convert CSV files to Excel workbooks - Office Scripts

This is the final code used (added some formatting steps after line 28)

function main(workbook: ExcelScript.Workbook, csv: string) {
  /* Convert the CSV data into a 2D array. */
  // Trim the trailing new line.
  csv = csv.trim();

  // Split each line into a row.
  let rows = csv.split("\r\n");
  let data: string[][] = [];
  rows.forEach((value) => {
    /*
     * For each row, match the comma-separated sections.
     * For more information on how to use regular expressions to parse CSV files,
     * see this Stack Overflow post: https://stackoverflow.com/a/48806378/9227753
     */
    let row = value.match(/(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g);

    // Remove the preceding comma.
    row.forEach((cell, index) => {
      row[index] = cell.indexOf(",") === 0 ? cell.substr(1) : cell;
    });
    data.push(row);
  });

  // Put the data in the worksheet.
  let sheet = workbook.getWorksheet("Sheet1");
  let range = sheet.getRangeByIndexes(0, 0, data.length, data[0].length);
  range.setValues(data);

  // Set 1st row to blue
  sheet.getRange("A1:E1").getFormat().getFill().setColor("4472C4");

  // Set font color on 1st row to white
  sheet.getRange("A1:E1").getFormat().getFont().setColor("FFFFFF");

  // Rename worksheet to "MVENDOR"
  let sheet1 = workbook.getWorksheet("Sheet1");
  sheet1.setName("MVENDOR");

  // Auto fit the columns of range all cells on selectedSheet
  sheet.getRange().getFormat().autofitColumns();

  // Replace " with  on selectedSheet
  sheet.replaceAll("\"", "", { completeMatch: false, matchCase: false });
  //
}