Convert CSV to Excel Office Script

Diamond Contributor

May 01, 2023

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.

peiyezhu

Bronze Contributor

May 03, 2023

SergeiBaklan

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

SergeiBaklan
Diamond Contributor
May 03, 2023
peiyezhu , that's not my territory

Forum Discussion

Resources