ConvertCSV to XLSX Script

ConvertCSV-ExcelOnline

/**
 * Adds CSV data to a specified worksheet in the workbook.
 * 
 * @param {ExcelScript.Workbook} workbook - The target workbook.
 * @param {string} csv - The CSV data as a string.
 */
function main(workbook: ExcelScript.Workbook, csv: string): void {
  // Access the target worksheet.
  let targetSheet = workbook.getWorksheet("Sheet1");

  // Normalize line endings by removing carriage return characters.
  let normalizedCsv = csv.replace(/\r/g, "");

  // Split the CSV data into rows.
  let rows = normalizedCsv.split("\n");

  // Regular expression to match CSV cells, including those enclosed in quotes.
  const csvCellRegex = /(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g;

  rows.forEach((row, rowIndex) => {
    // Skip empty rows.
    if (row.length === 0) {
      return;
    }

    // Extract cells from the row using the regex.
    let cells = row.match(csvCellRegex).map((cell, cellIndex) => {
      // Remove leading commas from cells and unquote if necessary.
      return cell.startsWith(",") ? cell.substring(1) : cell;
    });

    // Adjust for leading blank cell.
    if (cells[0] === '') {
      cells.unshift("");
    }

    // Prepare the data for setting in the worksheet.
    let rowData: string[][] = [cells];

    // Define the range in the worksheet to update.
    let range = targetSheet.getRangeByIndexes(rowIndex, 0, 1, cells.length);

    // Update the worksheet with the row data.
    range.setValues(rowData);
  });

  // Optional: Add additional formatting or table creation here.
}