Excel Office Script - Unable to copy files <100K rows #2
Description
Hi Sudhi... I need your help. 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:
- Flow is triggered once an Azure Data Factory container is updated with a .csv file
- The content of the file is retrieved and converted into a string with a 'Compose' step
- An empty excel file is created to receive the content of the .csv file
- 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.
I watched your video about the script that makes it possible to copy data in smaller chunks/batches, thus my reason for seeking your help.
I more or less understand the script, but don't have any idea how to integrate the script I'm using with your script. I know for the most part that your script will remain intact... I've attempted to integrate both, but I have not been successful writing it to make it work. Any help would be greatly appreciated.
`
function main(workbook: ExcelScript.Workbook, csv: string) {
const sheet = workbook.getActiveWorksheet();
// Credit: https://www.codegrepper.com/code-examples/javascript/random+text+generator+javascript
/* 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) => {
let row = value.match(/(?:,|\n|^)("(?:(?:"")[^"])"|[^",\n]|(?:\n|$))/g);
let data: (string | number | boolean)[][] = [];
console.log(Generating data...)
data.push(row);
console.log(`Calling update range function...`);
const updated = updateRangeInChunks(sheet.getRange("B1"), data);
if (!updated) {
console.log(`Update did not take place or complete. Chech and run again.`)
}
});
return;
}
function updateRangeInChunks(
startCell: ExcelScript.Range,
values: (string | boolean | number)[][],
cellsInChunk: number = 10000
): boolean {
const startTime = new Date().getTime();
console.log(Cells per chunk setting: ${cellsInChunk});
if (!values) {
console.log(Invalid input values to update.);
return false;
}
if (values.length === 0 || values[0].length === 0) {
console.log(Empty data -- nothing to update.);
return true;
}
const totalCells = values.length * values[0].length;
console.log(Total cells to update in the target range: ${totalCells});
if (totalCells <= cellsInChunk) {
console.log(No need to chunk -- updating directly);
updateTargetRange(startCell, values);
return true;
}
const rowsPerChunk = Math.floor(cellsInChunk / values[0].length);
console.log("Rows per chunk " + rowsPerChunk);
let rowCount = 0;
let totalRowsUpdated = 0;
let chunkCount = 0;
for (let i = 0; i < values.length; i++) {
rowCount++;
if (rowCount === rowsPerChunk) {
chunkCount++;
console.log(Calling update next chunk function. Chunk#: ${chunkCount});
updateNextChunk(startCell, values, rowsPerChunk, totalRowsUpdated);
rowCount = 0;
totalRowsUpdated += rowsPerChunk;
console.log(${((totalRowsUpdated / values.length) * 100).toFixed(1)}% Done);
}
}
console.log(Updating remaining rows -- last chunk: ${rowCount})
if (rowCount > 0) {
updateNextChunk(startCell, values, rowCount, totalRowsUpdated);
}
let endTime = new Date().getTime();
console.log(Completed ${totalCells} cells update. It took: ${((endTime - startTime) / 1000).toFixed(6)} seconds to complete. ${((((endTime - startTime) / 1000)) / cellsInChunk).toFixed(8)} seconds per ${cellsInChunk} cells-chunk.);
return true;
}
/**
- A Helper function that computes the target range and updates.
*/
function updateNextChunk(
startingCell: ExcelScript.Range,
data: (string | boolean | number)[][],
rowsPerChunk: number,
totalRowsUpdated: number
) {
const newStartCell = startingCell.getOffsetRange(totalRowsUpdated, 0);
const targetRange = newStartCell.getResizedRange(rowsPerChunk - 1, data[0].length - 1);
console.log(Updating chunk at range ${targetRange.getAddress()});
const dataToUpdate = data.slice(totalRowsUpdated, totalRowsUpdated + rowsPerChunk);
try {
targetRange.setValues(dataToUpdate);
} catch (e) {
throw Error while updating the chunk range: ${JSON.stringify(e)};
}
return;
}
/**
- A Helper function that computes the target range given the target range's starting cell and selected range and updates the values.
*/
function updateTargetRange(
targetCell: ExcelScript.Range,
values: (string | boolean | number)[][]
) {
const targetRange = targetCell.getResizedRange(values.length - 1, values[0].length - 1);
console.log(Updating the range. ${targetRange.getAddress()});
try {
targetRange.setValues(values);
} catch (e) {
throwError while updating the whole range: ${JSON.stringify(e)};
}
return;
}
`