javascript - how to handle a blank value with excel script converting CSV to worksheet - Stack Overflow

admin管理员组

文章数量:1327581

I have a flow, that runs a script, that converts a CSV document to an excel document for use elsewhere. The CSV often has a comment field that is blank (example: john,doe,8,,SC), the blank value would be the comment field. However when the script runs it will just write the SC into the comments field shifting all columns over one. I need this field to either return null or blank and not shift the columns. Here is my current code :

function main(workbook: ExcelScript.Workbook, lineCSV: string[]) {
    let selectedSheet = workbook.getActiveWorksheet();
    const alllines = lineCSV;
    let counter = 1;

    for (let line of alllines) {
        if (line.includes(",")) {
            // Split line by commas, ignoring commas within quotes
            let items = line.match(/(".*?"|[^",]+)(?=\s*,|\s*$)/g);

            // Replace empty items with "empty"
            if (items) {
                for (let i = 0; i < items.length; i++) {
                    if (items[i] === "") {
                        items[i] = "null";
                    }
                }
            } else {
                items = [];
            }

            // Ensure the items array has exactly 25 elements (A to Y)
            while (items.length < 25) {
                items.push("null");
            }

            // Create a new array to ensure each column is correctly mapped
            let rowValues: string[] = new Array(25).fill("null");
            for (let i = 0; i < items.length; i++) {
                rowValues[i] = items[i];
            }

            // Set range dynamically based on the number of items
            selectedSheet.getRange("A" + counter + ":Y" + counter).setValues([rowValues]);
            counter++;
        }
    }

    // Add a new table at range A1:Y based on the number of rows processed
    workbook.addTable(selectedSheet.getRange("A1:Y" + counter), true).setName("Data");
}

I've been trying things for hours with no luck. any insight would be great! thanks!

expected the values to stay in the expected columns and not shift over since one value is blank

I have a flow, that runs a script, that converts a CSV document to an excel document for use elsewhere. The CSV often has a comment field that is blank (example: john,doe,8,,SC), the blank value would be the comment field. However when the script runs it will just write the SC into the comments field shifting all columns over one. I need this field to either return null or blank and not shift the columns. Here is my current code :

function main(workbook: ExcelScript.Workbook, lineCSV: string[]) {
    let selectedSheet = workbook.getActiveWorksheet();
    const alllines = lineCSV;
    let counter = 1;

    for (let line of alllines) {
        if (line.includes(",")) {
            // Split line by commas, ignoring commas within quotes
            let items = line.match(/(".*?"|[^",]+)(?=\s*,|\s*$)/g);

            // Replace empty items with "empty"
            if (items) {
                for (let i = 0; i < items.length; i++) {
                    if (items[i] === "") {
                        items[i] = "null";
                    }
                }
            } else {
                items = [];
            }

            // Ensure the items array has exactly 25 elements (A to Y)
            while (items.length < 25) {
                items.push("null");
            }

            // Create a new array to ensure each column is correctly mapped
            let rowValues: string[] = new Array(25).fill("null");
            for (let i = 0; i < items.length; i++) {
                rowValues[i] = items[i];
            }

            // Set range dynamically based on the number of items
            selectedSheet.getRange("A" + counter + ":Y" + counter).setValues([rowValues]);
            counter++;
        }
    }

    // Add a new table at range A1:Y based on the number of rows processed
    workbook.addTable(selectedSheet.getRange("A1:Y" + counter), true).setName("Data");
}

I've been trying things for hours with no luck. any insight would be great! thanks!

expected the values to stay in the expected columns and not shift over since one value is blank

• javascript
• excel
• power-automate

Share Improve this question Follow edited Jan 23 at 7:43 zonbi inu 3177 bronze badges asked Jan 22 at 15:52 Hdd328Hdd328 1 2

• According to your example, john,doe,8,,SC, it provides a comprehensive indication of all the columns. The double ,, clearly marks an empty placeholder for the comment or whatever else is missing in the line. You simply need to have lines with equal number of ,. Isn't it the case? If so, nothing can shift in you split the line string correctly, using , as a separator. If you start with that and apply your regular expressing to array elements, you won't have this problem. – Sergey A Kryukov Commented Jan 22 at 16:41
• I posted a code sample to clarify it as an answer, please see. If I miss something, let me know, I'll be able to fix/update it. – Sergey A Kryukov Commented Jan 22 at 16:52

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

In my comment to the question, I've explained that you invite mistakes by applying a regular expression to an entire line.

Instead, you should split the line in per-column cell fragments first:

// ...
for (let line of alllines)
    const cells = line.split(",");
    for (let cell of cells)
       processCell(cell);

const processCell = content => {
    // only here, apply some regular expressions
    // or anything like that
    // always work with a single cell
}; //

That will immediately simplify things and fix your problem without digging into the problems of multiple matches with your overcomplicated regular expression.

本文标签： javascripthow to handle a blank value with excel script converting CSV to worksheetStack Overflow