Boost Efficiency: Convert Large Netsuite CSVs to Excel XLSX

May 19, 2023 Adolfo Garza Comments 0 Comment

I faced a challenging project that involved converting large CSV files stored in Netsuite’s file cabinet into XLSX format, specifically in Excel files. These files varied in size, with some exceeding 30 MB and even 100 MB. Initially, I attempted to use SheetJS, a plugin designed for this purpose (https://docs.sheetjs.com/docs/demos/cloud/netsuite/). However, I encountered difficulties as the plugin loaded the entire CSV file into memory, causing errors and performance issues.

To overcome this limitation, I devised an alternative solution for converting the CSV files to Excel XLSX format. My approach involved iterating through the CSV file line by line and constructing the XLSX file from scratch. Leveraging the fact that XLSX files are essentially zip files containing multiple XML files, I successfully accomplished the conversion. As a result, not only were the CSV files successfully converted, but the resulting XLSX files also demonstrated significant reductions in file size. In some cases, file sizes were reduced by up to 50%.

However, it is important to be aware of certain caveats associated with this conversion method. There are instances where the size of the resulting ZIP file becomes too large to compress, leading to a failure in the conversion process and triggering a timeout error. The exact threshold for this issue is not specified, as I have encountered failures with files around 50 MB, while others as large as 200 MB were processed without any problems. Consequently, it is crucial to consider the possibility of encountering heavy files that may not be compatible with this code. For optimal processing power, it is advisable to execute the code as a Schedule/Mass Reduce script.

To utilize the function effectively, ensure you are running it on Suitescript 2.1 and that the following modules are loaded: “N/xml,” “N/file,” and “N/compress.”

Simply provide the function with the File Cabinet path of the CSV file, and it will seamlessly transform it into an Excel file, preserving both the original file name and folder location.
Eg:

1	createExcelFileFromCSV('SuiteScripts/test.csv'); // Will produce a file at 'SuiteScripts/test.xslx'

/**
 * This function converts a CSV file to an Excel file. 
 * It takes the path to the CSV file as a parameter and 
 * returns the ID of the Excel file. The function works 
 * by first creating a new Excel file. Then, it iterates 
 * over the rows of the CSV file and adds each row to 
 * the Excel file. Finally, it saves the Excel file and
 * it returns the ID of the Excel file.
 *
 * @param {string} csvFilePath The path to the CSV file.
 * @returns {string} The ID of the Excel file.
 */
function createExcelFileFromCSV(csvFilePath){  
    purgeExcelFiles(csvFilePath);
	
    let csvData = file.load({ id: csvFilePath });
    let fileName = csvFilePath.split('.').shift().split('/').pop();
    let folderId = csvData.folder;

    let xlsHeader = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?><worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"><sheetPr filterMode="false"><pageSetUpPr fitToPage="false"/></sheetPr><dimension ref="A1:B2"/><sheetViews><sheetView showFormulas="false" showGridLines="true" showRowColHeaders="true" showZeros="true" rightToLeft="false" tabSelected="true" showOutlineSymbols="true" defaultGridColor="true" view="normal" topLeftCell="A1" colorId="64" zoomScale="100" zoomScaleNormal="100" zoomScalePageLayoutView="100" workbookViewId="0"><selection pane="topLeft" activeCell="B2" activeCellId="0" sqref="B2"/></sheetView></sheetViews><sheetFormatPr defaultColWidth="11.60546875" defaultRowHeight="12.8" zeroHeight="false" outlineLevelRow="0" outlineLevelCol="0"/><sheetData>`;
    let xlsFooter = `</sheetData><printOptions headings="false" gridLines="false" gridLinesSet="true" horizontalCentered="false" verticalCentered="false"/><pageMargins left="0.7875" right="0.7875" top="1.05277777777778" bottom="1.05277777777778" header="0.7875" footer="0.7875"/><pageSetup paperSize="1" scale="100" fitToWidth="1" fitToHeight="1" pageOrder="downThenOver" orientation="portrait" blackAndWhite="false" draft="false" cellComments="none" firstPageNumber="1" useFirstPageNumber="true" horizontalDpi="300" verticalDpi="300" copies="1"/><headerFooter differentFirst="false" differentOddEven="false"><oddHeader>&amp;C&amp;&quot;Times New Roman,Regular&quot;&amp;12&amp;A</oddHeader><oddFooter>&amp;C&amp;&quot;Times New Roman,Regular&quot;&amp;12Page &amp;P</oddFooter></headerFooter></worksheet>`
    
    let tempFileObj = file.create({
        name: 'sheet1.xml',
        fileType: 'PLAINTEXT',
        contents: '',
        encoding: file.Encoding.UTF_8
    });
    
    tempFileObj.appendLine({
        value: xlsHeader
    });

    let iterator = csvData.lines.iterator();
    let rowNumber = 1;
    //Headers
    iterator.each(function (line) {
        let parsedLine = CSVParser().parse(line.value, {header:false});
        let obj = parsedLine.data[0];
        let XMLCells = '';
        
        for(let i=0; i < obj.length; i++){
            let XMLCell = createXMLCell(rowNumber,i,obj[i]);
            XMLCells += XMLCell;
        }
        
        let rowXML = `<row r="${rowNumber}" spans="1:1">${XMLCells}</row>`;
        tempFileObj.appendLine({ value: rowXML });
        rowNumber++;
        return true;
    });
    
    tempFileObj.appendLine({ value: xlsFooter });
    
    let archiver = createArchiverObject();
    archiver.add({
        file: tempFileObj,
        directory: 'xl/worksheets/'
    });
    
    let zipFile = archiver.archive({
        name: fileName + '.zip'
    });

    //Save the archive to the same folder
    zipFile.folder = folderId;
    zipFile.conflictResolution = file.NameConflictResolution.OVERWRITE;
    let zipFileId = zipFile.save();
    let zipFileObj = file.load({
        id: zipFileId
    });
    zipFileObj.name = fileName + '.xlsx';
    zipFileObj.conflictResolution = file.NameConflictResolution.OVERWRITE;
    let excelFileId = zipFileObj.save();
    
    if(excelFileId != zipFileId){
        file.delete({id: zipFileId });
    }
    return fileId;
	
	
	//########################
	//### HELPER FUNCTIONS ###
	//########################
	
	/**
	 * This function creates a boilerplate archiver object. 
	 * The archiver object is used to create a ZIP file 
	 * which will be saved as an Excel file.
	 *
	 * @returns {Object} The archiver object.
	 */
	function createArchiverObject(){
		let archiver = compress.createArchiver();
		
		let rels = `<?xml version="1.0" encoding="UTF-8"?>
	<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/><Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>
	</Relationships>`;
		let appxml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
	<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"><Template></Template><TotalTime>0</TotalTime><Application>LibreOffice/7.2.2.2$Windows_X86_64 LibreOffice_project/02b2acce88a210515b4a5bb2e46cbfb63fe97d56</Application><AppVersion>15.0000</AppVersion></Properties>`;
		let corexml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
	<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><dcterms:created xsi:type="dcterms:W3CDTF">2022-11-21T12:59:32Z</dcterms:created><dc:creator></dc:creator><dc:description></dc:description><dc:language>en-US</dc:language><cp:lastModifiedBy></cp:lastModifiedBy><cp:revision>0</cp:revision><dc:subject></dc:subject><dc:title></dc:title></cp:coreProperties>`;
		let contentTypesxml = `<?xml version="1.0" encoding="UTF-8"?>
	<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Default Extension="xml" ContentType="application/xml"/><Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Default Extension="png" ContentType="image/png"/><Default Extension="jpeg" ContentType="image/jpeg"/><Override PartName="/_rels/.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/><Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/><Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/_rels/workbook.xml.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/><Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>
	</Types>`;
		let workbookrels = `<?xml version="1.0" encoding="UTF-8"?>
	<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
	</Relationships>`;
		let stylesxml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
	<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><numFmts count="1"><numFmt numFmtId="164" formatCode="General"/></numFmts><fonts count="4"><font><sz val="10"/><name val="Arial"/><family val="2"/></font><font><sz val="10"/><name val="Arial"/><family val="0"/></font><font><sz val="10"/><name val="Arial"/><family val="0"/></font><font><sz val="10"/><name val="Arial"/><family val="0"/></font></fonts><fills count="2"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill></fills><borders count="1"><border diagonalUp="false" diagonalDown="false"><left/><right/><top/><bottom/><diagonal/></border></borders><cellStyleXfs count="20"><xf numFmtId="164" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="true" applyAlignment="true" applyProtection="true"><alignment horizontal="general" vertical="bottom" textRotation="0" wrapText="false" indent="0" shrinkToFit="false"/><protection locked="true" hidden="false"/></xf><xf numFmtId="0" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="2" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="2" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="43" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="41" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="44" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="42" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="9" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf></cellStyleXfs><cellXfs count="1"><xf numFmtId="164" fontId="0" fillId="0" borderId="0" xfId="0" applyFont="false" applyBorder="false" applyAlignment="false" applyProtection="false"><alignment horizontal="general" vertical="bottom" textRotation="0" wrapText="false" indent="0" shrinkToFit="false"/><protection locked="true" hidden="false"/></xf></cellXfs><cellStyles count="6"><cellStyle name="Normal" xfId="0" builtinId="0"/><cellStyle name="Comma" xfId="15" builtinId="3"/><cellStyle name="Comma [0]" xfId="16" builtinId="6"/><cellStyle name="Currency" xfId="17" builtinId="4"/><cellStyle name="Currency [0]" xfId="18" builtinId="7"/><cellStyle name="Percent" xfId="19" builtinId="5"/></cellStyles></styleSheet>`;
		let workbookxml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
	<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><fileVersion appName="Calc"/><workbookPr backupFile="false" showObjects="all" date1904="false"/><workbookProtection/><bookViews><workbookView showHorizontalScroll="true" showVerticalScroll="true" showSheetTabs="true" xWindow="0" yWindow="0" windowWidth="16384" windowHeight="8192" tabRatio="500" firstSheet="0" activeTab="0"/></bookViews><sheets><sheet name="Sheet1" sheetId="1" state="visible" r:id="rId2"/></sheets><calcPr iterateCount="100" refMode="A1" iterate="false" iterateDelta="0.001"/><extLst><ext xmlns:loext="http://schemas.libreoffice.org/" uri="{7626C862-2A13-11E5-B345-FEFF819CDC9F}"><loext:extCalcPr stringRefSyntax="CalcA1"/></ext></extLst></workbook>`;
		
		archiver.add({
			file: file.create({
				name: '.rels',
				fileType: 'PLAINTEXT',
				contents: rels,
				encoding: file.Encoding.UTF_8
			}),
			directory: '_rels/'
		});
		archiver.add({
			file: file.create({
				name: 'app.xml',
				fileType: 'PLAINTEXT',
				contents: appxml,
				encoding: file.Encoding.UTF_8
			}),
			directory: 'docProps/'
		});
		archiver.add({
			file: file.create({
				name: 'core.xml',
				fileType: 'PLAINTEXT',
				contents: corexml,
				encoding: file.Encoding.UTF_8
			}),
			directory: 'docProps/'
		});
		archiver.add({
			file: file.create({
				name: `[Content_Types].xml`,
				fileType: 'PLAINTEXT',
				contents: contentTypesxml,
				encoding: file.Encoding.UTF_8
			}),
			directory: ''
		});
		archiver.add({
			file: file.create({
				name: 'workbook.xml.rels',
				fileType: 'PLAINTEXT',
				contents: workbookrels,
				encoding: file.Encoding.UTF_8
			}),
			directory: 'xl/_rels/'
		});
		archiver.add({
			file: file.create({
				name: 'styles.xml',
				fileType: 'PLAINTEXT',
				contents: stylesxml,
				encoding: file.Encoding.UTF_8
			}),
			directory: 'xl/'
		});
		archiver.add({
			file: file.create({
				name: 'workbook.xml',
				fileType: 'PLAINTEXT',
				contents: workbookxml,
				encoding: file.Encoding.UTF_8
			}),
			directory: 'xl/'
		});
		
		return archiver;
	}

	/**
	 * Converts a CSV line to an XML cell.
	 *
	 * @param {number} rowNumber The row number of the CSV line.
	 * @param {string} cellValue The value of the CSV cell.
	 * @returns {string} The XML cell.
	 */
	function createXMLCell(rowNumber,columnNumber,textValue){
		let cellTemplate = `<c r="${columnToAlphabet(columnNumber)}${rowNumber}" t="inlineStr"><is><t>${xml.escape({ xmlText : textValue})}</t></is></c>`;
		
		return cellTemplate;
		
		function columnToAlphabet(n){
			let ordA = 'a'.charCodeAt(0);
			let ordZ = 'z'.charCodeAt(0);
			let len = ordZ - ordA + 1;
		  
			let s = "";
			while(n >= 0) {
				s = String.fromCharCode(n % len + ordA) + s;
				n = Math.floor(n / len) - 1;
			}
			return s.toUpperCase();
		}
	}

	/**
	 * Delete any Excel files that were created from CSV files, if they exist.
	 *
	 * @param {string} filePath The path to the CSV file.
	 */
	function purgeExcelFiles(csvFilePath){
		try{
			let fileName = csvFilePath.split('.').shift().split('/').pop();
			let excelFilePath = csvFilePath.substring(0,csvFilePath.length-fileName.length-4) + ".xlsx";
			let excelFile = file.load({ id: excelFilePath });
			file.delete({id: excelFile.id });
        } catch(e){
			
		}
		try{
			let zipFilePath = csvFilePath.substring(0,csvFilePath.length-fileName.length-4) + ".zip";
			let zipFile = file.load({ id: zipFilePath });
			file.delete({id: zipFile.id });
		} catch(e){
			
		}
	}
	
	/**
	 * CSV Parser Library (Baby Parse) which is based on Papa Parse.
	 * Examples of use:
	 * CSVParser().parse((csvString[, config]);
	 * CSVParser().unparse(data[, config])
	 *
	 * @returns {object} The CSVParser object.
	 */
	function CSVParser(){function e(r,n){if(Array.isArray(r)){var i=[];return r.forEach(function(t){"object"==typeof t?i.push(e(t.file,t.config)):i.push(e(t,n))}),i}var i={data:[],errors:[]};if(!/(\.csv|\.txt)$/.test(r))return i.errors.push({type:"",code:"",message:"Unsupported file type.",row:""}),i;try{var a=fs.readFileSync(r).toString();return t(a,n)}catch(s){return i.errors.push(s),i}}function t(e,t){var r=a(t),i=new n(r),s=i.parse(e);return f(r.complete)&&r.complete(s),s}function r(e,t){function r(){"object"==typeof t&&("string"==typeof t.delimiter&&1==t.delimiter.length&&-1==l.BAD_DELIMITERS.indexOf(t.delimiter)&&(o=t.delimiter),("boolean"==typeof t.quotes||t.quotes instanceof Array)&&(f=t.quotes),"string"==typeof t.newline&&(d=t.newline))}function n(e){if("object"!=typeof e)return[];var t=[];for(var r in e)t.push(r);return t}function i(e,t){var r="";"string"==typeof e&&(e=JSON.parse(e)),"string"==typeof t&&(t=JSON.parse(t));var n=e instanceof Array&&e.length>0,i=!(t[0]instanceof Array);if(n){for(var s=0;s<e.length;s++)s>0&&(r+=o),r+=a(e[s],s);t.length>0&&(r+=d)}for(var f=0;f<t.length;f++){for(var l=n?e.length:t[f].length,u=0;l>u;u++){u>0&&(r+=o);var p=n&&i?e[u]:u;r+=a(t[f][p],u)}f<t.length-1&&(r+=d)}return r}function a(e,t){if("undefined"==typeof e||null===e)return"";e=e.toString().replace(/"/g,'""');var r="boolean"==typeof f&&f||f instanceof Array&&f[t]||s(e,l.BAD_DELIMITERS)||e.indexOf(o)>-1||" "==e.charAt(0)||" "==e.charAt(e.length-1);return r?'"'+e+'"':e}function s(e,t){for(var r=0;r<t.length;r++)if(e.indexOf(t[r])>-1)return!0;return!1}var f=!1,o=",",d="\r\n";if(r(),"string"==typeof e&&(e=JSON.parse(e)),e instanceof Array){if(!e.length||e[0]instanceof Array)return i(null,e);if("object"==typeof e[0])return i(n(e[0]),e)}else if("object"==typeof e)return"string"==typeof e.data&&(e.data=JSON.parse(e.data)),e.data instanceof Array&&(e.fields||(e.fields=e.data[0]instanceof Array?e.fields:n(e.data[0])),e.data[0]instanceof Array||"object"==typeof e.data[0]||(e.data=[e.data])),i(e.fields||[],e.data||[]);throw"exception: Unable to serialize unrecognized input"}function n(e){function t(){if(E&&m&&(p("Delimiter","UndetectableDelimiter","Unable to auto-detect delimiting character; defaulted to '"+l.DefaultDelimiter+"'"),m=!1),e.skipEmptyLines)for(var t=0;t<E.data.length;t++)1==E.data[t].length&&""==E.data[t][0]&&E.data.splice(t--,1);return r()&&n(),a()}function r(){return e.header&&0==w.length}function n(){if(E){for(var e=0;r()&&e<E.data.length;e++)for(var t=0;t<E.data[e].length;t++)w.push(E.data[e][t]);E.data.splice(0,1)}}function a(){if(!E||!e.header&&!e.dynamicTyping)return E;for(var t=0;t<E.data.length;t++){for(var r={},n=0;n<E.data[t].length;n++){if(e.dynamicTyping){var i=E.data[t][n];"true"==i||"TRUE"===i?E.data[t][n]=!0:"false"==i||"FALSE"===i?E.data[t][n]=!1:E.data[t][n]=u(i)}e.header&&(n>=w.length?(r.__parsed_extra||(r.__parsed_extra=[]),r.__parsed_extra.push(E.data[t][n])):r[w[n]]=E.data[t][n])}e.header&&(E.data[t]=r,n>w.length?p("FieldMismatch","TooManyFields","Too many fields: expected "+w.length+" fields but parsed "+n,t):n<w.length&&p("FieldMismatch","TooFewFields","Too few fields: expected "+w.length+" fields but parsed "+n,t))}return e.header&&E.meta&&(E.meta.fields=w),E}function o(t){for(var r,n,a,s=[",","    ","|",";",l.RECORD_SEP,l.UNIT_SEP],f=0;f<s.length;f++){var o=s[f],d=0,u=0;a=void 0;for(var p=new i({delimiter:o,preview:10}).parse(t),c=0;c<p.data.length;c++){var h=p.data[c].length;u+=h,"undefined"!=typeof a?h>1&&(d+=Math.abs(h-a),a=h):a=h}u/=p.data.length,("undefined"==typeof n||n>d)&&u>1.99&&(n=d,r=o)}return e.delimiter=r,{successful:!!r,bestDelimiter:r}}function d(e){e=e.substr(0,1048576);var t=e.split("\r");if(1==t.length)return"\n";for(var r=0,n=0;n<t.length;n++)"\n"==t[n][0]&&r++;return r>=t.length/2?"\r\n":"\r"}function u(e){var t=g.test(e);return t?parseFloat(e):e}function p(e,t,r,n){E.errors.push({type:e,code:t,message:r,row:n})}var c,h,m,g=/^\s*-?(\d*\.?\d+|\d+\.?\d*)(e[-+]?\d+)?\s*$/i,y=this,v=0,b=!1,w=[],E={data:[],errors:[],meta:{}};if(f(e.step)){var x=e.step;e.step=function(n){if(E=n,r())t();else{if(t(),0==E.data.length)return;v+=n.data.length,e.preview&&v>e.preview?h.abort():x(E,y)}}}this.parse=function(r){if(e.newline||(e.newline=d(r)),m=!1,!e.delimiter){var n=o(r);n.successful?e.delimiter=n.bestDelimiter:(m=!0,e.delimiter=l.DefaultDelimiter),E.meta.delimiter=e.delimiter}var a=s(e);return e.preview&&e.header&&a.preview++,c=r,h=new i(a),E=h.parse(c),t(),!f(e.complete)||b||y.streamer&&!y.streamer.finished()||e.complete(E),b?{meta:{paused:!0}}:E||{meta:{paused:!1}}},this.pause=function(){b=!0,h.abort(),c=c.substr(h.getCharIndex())},this.resume=function(){b=!1,h=new i(e),h.parse(c),b||(y.streamer&&!y.streamer.finished()?y.streamer.resume():f(e.complete)&&e.complete(E))},this.abort=function(){h.abort(),f(e.complete)&&e.complete(E),c=""}}function i(e){e=e||{};var t=e.delimiter,r=e.newline,n=e.comments,i=e.step,a=e.preview,s=e.fastMode;if(("string"!=typeof t||1!=t.length||l.BAD_DELIMITERS.indexOf(t)>-1)&&(t=","),n===t)throw"Comment character same as delimiter";n===!0?n="#":("string"!=typeof n||l.BAD_DELIMITERS.indexOf(n)>-1)&&(n=!1),"\n"!=r&&"\r"!=r&&"\r\n"!=r&&(r="\n");var f=0,o=!1;this.parse=function(e){function l(){return w.push(e.substr(f)),v.push(w),f=c,y&&p(),u()}function d(t){v.push(w),w=[],f=t,O=e.indexOf(r,f)}function u(e){return{data:v,errors:b,meta:{delimiter:t,linebreak:r,aborted:o,truncated:!!e}}}function p(){i(u()),v=[],b=[]}if("string"!=typeof e)throw"Input must be a string";var c=e.length,h=t.length,m=r.length,g=n.length,y="function"==typeof i;f=0;var v=[],b=[],w=[];if(!e)return u();if(s){for(var E=e.split(r),x=0;x<E.length;x++)if(!n||E[x].substr(0,g)!=n){if(y){if(v=[E[x].split(t)],p(),o)return u()}else v.push(E[x].split(t));if(a&&x>=a)return v=v.slice(0,a),u(!0)}return u()}for(var D=e.indexOf(t,f),O=e.indexOf(r,f);;)if('"'!=e[f])if(n&&0===w.length&&e.substr(f,g)===n){if(-1==O)return u();f=O+m,O=e.indexOf(r,f),D=e.indexOf(t,f)}else if(-1!==D&&(O>D||-1===O))w.push(e.substring(f,D)),f=D+h,D=e.indexOf(t,f);else{if(-1===O)break;if(w.push(e.substring(f,O)),d(O+m),y&&(p(),o))return u();if(a&&v.length>=a)return u(!0)}else{var A=f;for(f++;;){var A=e.indexOf('"',A+1);if(-1===A)return b.push({type:"Quotes",code:"MissingQuotes",message:"Quoted field unterminated",row:v.length,index:f}),l();if(A===c-1)return w.push(e.substring(f,A).replace(/""/g,'"')),v.push(w),y&&p(),u();if('"'!=e[A+1]){if(e[A+1]==t){w.push(e.substring(f,A).replace(/""/g,'"')),f=A+1+h,D=e.indexOf(t,f),O=e.indexOf(r,f);break}if(e.substr(A+1,m)===r){if(w.push(e.substring(f,A).replace(/""/g,'"')),d(A+1+m),D=e.indexOf(t,f),y&&(p(),o))return u();if(a&&v.length>=a)return u(!0);break}}else A++}}return l()},this.abort=function(){o=!0},this.getCharIndex=function(){return f}}function a(e){"object"!=typeof e&&(e={});var t=s(e);return("string"!=typeof t.delimiter||1!=t.delimiter.length||l.BAD_DELIMITERS.indexOf(t.delimiter)>-1)&&(t.delimiter=o.delimiter),"\n"!=t.newline&&"\r"!=t.newline&&"\r\n"!=t.newline&&(t.newline=o.newline),"boolean"!=typeof t.header&&(t.header=o.header),"boolean"!=typeof t.dynamicTyping&&(t.dynamicTyping=o.dynamicTyping),"number"!=typeof t.preview&&(t.preview=o.preview),"function"!=typeof t.step&&(t.step=o.step),"function"!=typeof t.complete&&(t.complete=o.complete),"boolean"!=typeof t.skipEmptyLines&&(t.skipEmptyLines=o.skipEmptyLines),"boolean"!=typeof t.fastMode&&(t.fastMode=o.fastMode),t}function s(e){if("object"!=typeof e)return e;var t=e instanceof Array?[]:{};for(var r in e)t[r]=s(e[r]);return t}function f(e){return"function"==typeof e}var o={delimiter:"",newline:"",header:!1,dynamicTyping:!1,preview:0,step:void 0,comments:!1,complete:void 0,skipEmptyLines:!1,fastMode:!1},l={};return l.parse=t,l.parseFiles=e,l.unparse=r,l.RECORD_SEP=String.fromCharCode(30),l.UNIT_SEP=String.fromCharCode(31),l.BYTE_ORDER_MARK="\ufeff",l.BAD_DELIMITERS=["\r","\n",'"',l.BYTE_ORDER_MARK],l.DefaultDelimiter=",",l.Parser=i,l.ParserHandle=n,l}
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

/**

* This function converts a CSV file to an Excel file.

* It takes the path to the CSV file as a parameter and

* returns the ID of the Excel file. The function works

* by first creating a new Excel file. Then, it iterates

* over the rows of the CSV file and adds each row to

* the Excel file. Finally, it saves the Excel file and

* it returns the ID of the Excel file.

* @param {string} csvFilePath The path to the CSV file.

* @returns {string} The ID of the Excel file.

function createExcelFileFromCSV(csvFilePath){

purgeExcelFiles(csvFilePath);

let csvData = file.load({ id: csvFilePath });

let fileName = csvFilePath.split('.').shift().split('/').pop();

let folderId = csvData.folder;

let xlsHeader = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?><worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"><sheetPr filterMode="false"><pageSetUpPr fitToPage="false"/></sheetPr><dimension ref="A1:B2"/><sheetViews><sheetView showFormulas="false" showGridLines="true" showRowColHeaders="true" showZeros="true" rightToLeft="false" tabSelected="true" showOutlineSymbols="true" defaultGridColor="true" view="normal" topLeftCell="A1" colorId="64" zoomScale="100" zoomScaleNormal="100" zoomScalePageLayoutView="100" workbookViewId="0"><selection pane="topLeft" activeCell="B2" activeCellId="0" sqref="B2"/></sheetView></sheetViews><sheetFormatPr defaultColWidth="11.60546875" defaultRowHeight="12.8" zeroHeight="false" outlineLevelRow="0" outlineLevelCol="0"/><sheetData>`;

let xlsFooter = `</sheetData><printOptions headings="false" gridLines="false" gridLinesSet="true" horizontalCentered="false" verticalCentered="false"/><pageMargins left="0.7875" right="0.7875" top="1.05277777777778" bottom="1.05277777777778" header="0.7875" footer="0.7875"/><pageSetup paperSize="1" scale="100" fitToWidth="1" fitToHeight="1" pageOrder="downThenOver" orientation="portrait" blackAndWhite="false" draft="false" cellComments="none" firstPageNumber="1" useFirstPageNumber="true" horizontalDpi="300" verticalDpi="300" copies="1"/><headerFooter differentFirst="false" differentOddEven="false"><oddHeader>&C&"Times New Roman,Regular"&12&A</oddHeader><oddFooter>&C&"Times New Roman,Regular"&12Page &P</oddFooter></headerFooter></worksheet>`

let tempFileObj = file.create({

fileType: 'PLAINTEXT',

contents: '',

encoding: file.Encoding.UTF_8

});

tempFileObj.appendLine({

value: xlsHeader

});

let iterator = csvData.lines.iterator();

let rowNumber = 1;

//Headers

iterator.each(function (line) {

let parsedLine = CSVParser().parse(line.value, {header:false});

let obj = parsedLine.data[0];

let XMLCells = '';

for(let i=0; i < obj.length; i++){

let XMLCell = createXMLCell(rowNumber,i,obj[i]);

XMLCells += XMLCell;

}

let rowXML = `<row r="${rowNumber}" spans="1:1">${XMLCells}</row>`;

tempFileObj.appendLine({ value: rowXML });

rowNumber++;

return true;

});

tempFileObj.appendLine({ value: xlsFooter });

let archiver = createArchiverObject();

archiver.add({

file: tempFileObj,

directory: 'xl/worksheets/'

});

let zipFile = archiver.archive({

});

//Save the archive to the same folder

zipFile.folder = folderId;

zipFile.conflictResolution = file.NameConflictResolution.OVERWRITE;

let zipFileId = zipFile.save();

let zipFileObj = file.load({

id: zipFileId

});

zipFileObj.name = fileName + '.xlsx';

zipFileObj.conflictResolution = file.NameConflictResolution.OVERWRITE;

let excelFileId = zipFileObj.save();

if(excelFileId != zipFileId){

file.delete({id: zipFileId });

}

return fileId;

//########################

//### HELPER FUNCTIONS ###

//########################

/**

* This function creates a boilerplate archiver object.

* The archiver object is used to create a ZIP file

* which will be saved as an Excel file.

* @returns {Object} The archiver object.

function createArchiverObject(){

let archiver = compress.createArchiver();

let rels = `<?xml version="1.0" encoding="UTF-8"?>

</Relationships>`;

let appxml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"><Template></Template><TotalTime>0</TotalTime><Application>LibreOffice/7.2.2.2$Windows_X86_64 LibreOffice_project/02b2acce88a210515b4a5bb2e46cbfb63fe97d56</Application><AppVersion>15.0000</AppVersion></Properties>`;

let corexml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><dcterms:created xsi:type="dcterms:W3CDTF">2022-11-21T12:59:32Z</dcterms:created><dc:creator></dc:creator><dc:description></dc:description><dc:language>en-US</dc:language><cp:lastModifiedBy></cp:lastModifiedBy><cp:revision>0</cp:revision><dc:subject></dc:subject><dc:title></dc:title></cp:coreProperties>`;

let contentTypesxml = `<?xml version="1.0" encoding="UTF-8"?>

</Types>`;

let workbookrels = `<?xml version="1.0" encoding="UTF-8"?>

</Relationships>`;

let stylesxml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><numFmts count="1"><numFmt numFmtId="164" formatCode="General"/></numFmts><fonts count="4"><font><sz val="10"/><name val="Arial"/><family val="2"/></font><font><sz val="10"/><name val="Arial"/><family val="0"/></font><font><sz val="10"/><name val="Arial"/><family val="0"/></font><font><sz val="10"/><name val="Arial"/><family val="0"/></font></fonts><fills count="2"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill></fills><borders count="1"><border diagonalUp="false" diagonalDown="false"><left/><right/><top/><bottom/><diagonal/></border></borders><cellStyleXfs count="20"><xf numFmtId="164" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="true" applyAlignment="true" applyProtection="true"><alignment horizontal="general" vertical="bottom" textRotation="0" wrapText="false" indent="0" shrinkToFit="false"/><protection locked="true" hidden="false"/></xf><xf numFmtId="0" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="2" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="2" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="43" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="41" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="44" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="42" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="9" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf></cellStyleXfs><cellXfs count="1"><xf numFmtId="164" fontId="0" fillId="0" borderId="0" xfId="0" applyFont="false" applyBorder="false" applyAlignment="false" applyProtection="false"><alignment horizontal="general" vertical="bottom" textRotation="0" wrapText="false" indent="0" shrinkToFit="false"/><protection locked="true" hidden="false"/></xf></cellXfs><cellStyles count="6"><cellStyle name="Normal" xfId="0" builtinId="0"/><cellStyle name="Comma" xfId="15" builtinId="3"/><cellStyle name="Comma [0]" xfId="16" builtinId="6"/><cellStyle name="Currency" xfId="17" builtinId="4"/><cellStyle name="Currency [0]" xfId="18" builtinId="7"/><cellStyle name="Percent" xfId="19" builtinId="5"/></cellStyles></styleSheet>`;

let workbookxml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><fileVersion appName="Calc"/><workbookPr backupFile="false" showObjects="all" date1904="false"/><workbookProtection/><bookViews><workbookView showHorizontalScroll="true" showVerticalScroll="true" showSheetTabs="true" xWindow="0" yWindow="0" windowWidth="16384" windowHeight="8192" tabRatio="500" firstSheet="0" activeTab="0"/></bookViews><sheets><sheet name="Sheet1" sheetId="1" state="visible" r:id="rId2"/></sheets><calcPr iterateCount="100" refMode="A1" iterate="false" iterateDelta="0.001"/><extLst><ext xmlns:loext="http://schemas.libreoffice.org/" uri="{7626C862-2A13-11E5-B345-FEFF819CDC9F}"><loext:extCalcPr stringRefSyntax="CalcA1"/></ext></extLst></workbook>`;

archiver.add({

file: file.create({

fileType: 'PLAINTEXT',

contents: rels,

encoding: file.Encoding.UTF_8

}),

directory: '_rels/'

});

archiver.add({

file: file.create({

fileType: 'PLAINTEXT',

contents: appxml,

encoding: file.Encoding.UTF_8

}),

directory: 'docProps/'

});

archiver.add({

file: file.create({

fileType: 'PLAINTEXT',

contents: corexml,

encoding: file.Encoding.UTF_8

}),

directory: 'docProps/'

});

archiver.add({

file: file.create({

fileType: 'PLAINTEXT',

contents: contentTypesxml,

encoding: file.Encoding.UTF_8

}),

directory: ''

});

archiver.add({

file: file.create({

fileType: 'PLAINTEXT',

contents: workbookrels,

encoding: file.Encoding.UTF_8

}),

directory: 'xl/_rels/'

});

archiver.add({

file: file.create({

fileType: 'PLAINTEXT',

contents: stylesxml,

encoding: file.Encoding.UTF_8

}),

directory: 'xl/'

});

archiver.add({

file: file.create({

fileType: 'PLAINTEXT',

contents: workbookxml,

encoding: file.Encoding.UTF_8

}),

directory: 'xl/'

});

return archiver;

}

/**

* Converts a CSV line to an XML cell.

* @param {number} rowNumber The row number of the CSV line.

* @param {string} cellValue The value of the CSV cell.

* @returns {string} The XML cell.

function createXMLCell(rowNumber,columnNumber,textValue){

let cellTemplate = `<c r="${columnToAlphabet(columnNumber)}${rowNumber}" t="inlineStr"><is><t>${xml.escape({ xmlText : textValue})}</t></is></c>`;

return cellTemplate;

function columnToAlphabet(n){

let ordA = 'a'.charCodeAt(0);

let ordZ = 'z'.charCodeAt(0);

let len = ordZ - ordA + 1;

let s = "";

while(n >= 0) {

s = String.fromCharCode(n % len + ordA) + s;

n = Math.floor(n / len) - 1;

}

return s.toUpperCase();

}

/**

* Delete any Excel files that were created from CSV files, if they exist.

* @param {string} filePath The path to the CSV file.

function purgeExcelFiles(csvFilePath){

try{

let fileName = csvFilePath.split('.').shift().split('/').pop();

let excelFilePath = csvFilePath.substring(0,csvFilePath.length-fileName.length-4) + ".xlsx";

let excelFile = file.load({ id: excelFilePath });

file.delete({id: excelFile.id });

} catch(e){

}

try{

let zipFilePath = csvFilePath.substring(0,csvFilePath.length-fileName.length-4) + ".zip";

let zipFile = file.load({ id: zipFilePath });

file.delete({id: zipFile.id });

} catch(e){

}

/**

* CSV Parser Library (Baby Parse) which is based on Papa Parse.

* Examples of use:

* CSVParser().parse((csvString[, config]);

* CSVParser().unparse(data[, config])

* @returns {object} The CSVParser object.

}