Forum Discussion
Convert CSV to Excel Office Script
Perhaps you mean this Convert CSV files to Excel workbooks - Office Scripts | Microsoft Learn template in which sheet name is defined explicitly ( as "Sheet1"). Be sure all csv files have sheet with such name.
As variant most probably script could be modified to select the sheet not by name but by position, e.g. first sheet in the file.
I think that selecting the sheet not by the name "Sheet1" but by position would fix this. How would that code be written?
- convert an Excel sheet to csv?
or Csv 2 sheet?The idea of this template is to convert set of csv files into Excel ones with help of Power Automate and using Excel on web.
Good.
I have found a javascript lib SheetJs which can merge csv files too.
<html><head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <script src="/js/onerror.js"></script> <script type="text/javascript" src="./static/ImportSheetsToDb/xlsx.full.min.js"></script> <title>online tools-merge csv files</title> </head> <body> <h3>merge csv files to an Excel sheet</h3> <input type="file" id="uploadInput" multiple > <!-- <input type="file" id="input_dom_element" multiple > <input type="button" id="btn" value="test"> --> <script> async function readExcelFileFromUploadInput(input) { // Get data as an ArrayBuffer //const file = input.files[0]; let rows = []; let firstRow=true //let iSkipRows=inputSkipRows.value let iSkipRows=1 const files = input.files; for (let i = 0; i < files.length; i++) { const file = files[i]; let filename=file.name const data = await file.arrayBuffer(); const workbook = XLSX.read(data); // console.log(workbook) // const ws = wb.Sheets[wb.SheetNames[0]]; // Do something with the worksheet data workbook.SheetNames.forEach(sheetName => { // Convert sheet to array of rows const sheetRows = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName],{header:1}); // Add filename and sheet name columns to each row if(firstRow==true){ sheetRows.forEach(row => { /* row.filename = filename; row.sheetName = sheetName; */ // row.unshift(sheetName) // row.unshift(filename) rows.push(row); }); firstRow=false }else{ sheetRows.forEach((row,rowIndex) => { /* row.filename = filename; row.sheetName = sheetName; */ if(rowIndex>(iSkipRows-1)){ // row.unshift(sheetName) // row.unshift(filename) rows.push(row); } }); } }); }; return rows; } // Parse and load first worksheet //const wb = XLSX.read(data); // const wb = read(data); //const ws = wb.Sheets[wb.SheetNames[0]]; // Convert worksheet to array of objects //const rows = XLSX.utils.sheet_to_json(ws, { header: 1 }); // const rows = utils.sheet_to_json(ws, { header: 1 }); /* rows.forEach(row => { /* row.filename = filename; row.sheetName = sheetName; row.unshift(sheetName) row.unshift(filename) rows.push(row); }); */ //return rows; //} async function mergeCsv(){ const input = document.getElementById('uploadInput'); const rows = await readExcelFileFromUploadInput(input); //alert(JSON.stringify(rows)); const ws = XLSX.utils.aoa_to_sheet(rows); //console.log(ws); //alert(ws) let worksheet=ws const outputFilename = 'consolidatedff.xlsx'; const workbook = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(workbook, worksheet); XLSX.writeFile(workbook, outputFilename); } uploadInput.onchange=function(){ mergeCsv() } </script> </body> </html>http://e.anyoupin.cn/EData/?p=tools.ceshi.index/csv2Sheet
test url
Instead of
let sheet = workbook.getWorksheet("Sheet1");you may use
const sheet = workbook.getWorksheets()[0]to return very first sheet (numbering starts from zero). You may check the sheet name by
console.log(sheet.getName())Just in case, equivalent syntax is
let sheet = workbook.getWorksheets()[0];SergeiBaklan I replaced you suggestion now I am getting a different error? I am getting an error with line 8 csv = csv.replace("/\r/g, """);
Error is Line 8 Cannot read properties of undefined (reading 'replace')I did a quick test, in general script if take sheet by number works
/** * Convert incoming CSV data into a range and add it to the workbook. */ function main(workbook: ExcelScript.Workbook, csv: string) { csv = `1, 2, 3 4, 5, 6 7, 8, 9`; //let sheet = workbook.getWorksheet("Sheet1"); const sheet = workbook.getWorksheets()[0] // 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. }Lates error you have means script can't find csv (thus replace() on it has value 'undefined' ). You may reproduce such error with above script if to comment definition of csv like
/** * Convert incoming CSV data into a range and add it to the workbook. */ function main(workbook: ExcelScript.Workbook, csv: string) { /* csv = `1, 2, 3 4, 5, 6 7, 8, 9`; */ //let sheet = workbook.getWorksheet("Sheet1"); const sheet = workbook.getWorksheets()[0] // Remove any Windows \r characters. csv = csv.replace(/\r/g, ""); //....With that I may assume that the problem is in Power Automate flow which doesn't return csv content to the script. First I'd check the configuration - folder with csv files has exactly the same name as used in the flow, in that flow are actual csv files. Next, run flow , after it is finished click in history on that run and check inputs and outputs for steps on that run.
