admin管理员组文章数量:1317898
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
Share Improve this question edited Jan 23 at 7:43 zonbi inu 317 bronze badges asked Jan 22 at 15:52 Hdd328Hdd328 1 2 |1 Answer
Reset to default 0In 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
版权声明:本文标题:javascript - how to handle a blank value with excel script converting CSV to worksheet - Stack Overflow 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.betaflare.com/web/1742035444a2417261.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
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