1

Good day!

I have a problem. I am using a script combined with a power automate flow to automate the task of converting csv files from one folder to xlsx files located in another folder on OneDrive.

In order to do this, I use this script:

/**
 * Convert incoming CSV data into a range and add it to the workbook.
 */
function main(workbook: ExcelScript.Workbook, csv: string) {
  let sheet = workbook.getWorksheet("Sheet1");

  // Remove any Windows \r characters.
  csv = csv.replace(/\r/g, "");

  // Split each line into a row.
  let rows = csv.split("\n");
  /*
   * 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
   */
  const csvMatchRegex = /(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g
  rows.forEach((value, index) => {
    if (value.length > 0) {
        let row = value.match(csvMatchRegex);
    
        // Check for blanks at the start of the row.
        if (row[0].charAt(0) === ',') {
          row.unshift("");
        }
    
        // 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);
    }
  });

  // Add any formatting or table creation that you want.
}

The documentation for the udo of this script can be found on the following website:https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/convert-csv

My problem is that although the code runs correctly and the xlsx file is created, it is created with garbled characters. Does anyone know what could be going on? From already thank you very much

enter image description here

I hope I can solve my problem

enter image description here

Leandro Abraham
  • 27
  • 2
  • CSV files are text files with commas. Excel files are ZIP packages containing XML files. What did the CSV file contain? Was it an actual text file? Or an *Excel* file saved with a `CSV` extension? – Panagiotis Kanavos Jul 05 '23 at 13:08
  • How did you actually load the file in Excel? Did it prompt you to repair the file or was it loaded succesfully? The `PK` characters at the top are part of a ZIP package's header. Either you're looking at a corrupted Excel file *after* the manual repair attempt, or the source file was an `xlsx` file from the start. The script never checked to see what it loaded, so it just copied bytes from that ZIP package into Excel cells – Panagiotis Kanavos Jul 05 '23 at 13:14
  • Hello ! Thanks for the reply! The csv file is taken from a flow that runs before it pulls information from a power bi data model, creates a csv file, and puts it in a folder on OneDrive. It is a pure csv file. Add an actual image of the csv I'm trying to convert. Honestly, I don't know where the "PK" comes from – Leandro Abraham Jul 05 '23 at 13:27

0 Answers0