convertExcelFile: ColdFusion UDF to convert an Excel file (XLS/XLSX) to CSV/TSV/XML/JSON/SQL/HTML/MHT/DOC/PDF using TotalExcelConverter (command line)

convertExcelFile.cfm

<!---
2024-07-02 convertExcelFile: ColdFusion UDF to convert an Excel file (XLS/XLSX)
to CSV/TSV/XML/JSON/SQL/HTML/MHT/DOC/PDF using TotalExcelConverter (command line)
For use with Windows; Requires https://www.coolutils.com/CommandLine/TotalExcelConverter
Blog: https://dev.to/gamesover/importing-data-from-a-pre-95-excel-2-worksheet-xls-file-56md
Tweet: https://x.com/gamesover/status/1829591479100342454
--->

<cfscript>
public struct function convertExcelFile(
	required string inputPath,
	string outputPath="",
	string convertTo="",
	string delimiter="",
	string table="myTable",
	string additionalParams="",
	numeric timeout=60
) hint="I convert an Excel file (XLS/XLSX) to CSV/TSV/XML/JSON/SQL/HTML/MHT/DOC/PDF using TotalExcelConverter (command line)" {
	local.timestart = gettickcount();
	local.exePath = "c:\TotalExcelConverter\ExcelConverter.exe";
	if (!fileExists(local.exePath)){
		throw(message="textConverter.convertExcel: TotalExcelConverter is not installed.");
	}
	if (!fileExists(arguments.inputPath)){
		throw(message="textConverter.convertExcel: Input file '#arguments.inputPath#' doesn't exist.");
	}
	if (!len(arguments.outputPath) && len(arguments.convertTo)){
		arguments.outputPath = arguments.inputPath.replaceAll("\.#listlast(arguments.inputPath,".")#$", ".#lcase(arguments.convertTo)#");
	}
	if (!len(arguments.convertTo) && listlen(arguments.outputPath,".") gt 1){
		arguments.convertTo = lcase(listlast(arguments.outputPath,"."));
	}
	if (!listfind("csv,tsv,txt,xml,json,sql,html,mht,doc,pdf", lcase(arguments.convertTo))){
		throw(message="textConverter.convertExcel: Invalid ConvertTo'#arguments.convertTo#'.");
	}
	local.config = [
		"args": [
			"name": local.exePath,
			"arguments": [
				"""#arguments.inputPath#""",
				"""#arguments.outputPath#"""
			],
			"timeout": (val(arguments.timeout) gt 0) ? val(arguments.timeout) : 60
		],
		"duration": 0
	];

	local.c = (arguments.convertTo eq "tsv") ? "csv" : arguments.convertTo;
	arrayappend(local.config.args.arguments, "-C #ucase(local.c)#");

	if (arguments.convertTo eq "tsv"){
		arrayappend(local.config.args.arguments, "-td T");
	} else if (arguments.convertTo eq "csv"){
		if (listfindnocase(",|csv", arguments.delimiter, "|") || !len(arguments.delimiter)){
			arrayappend(local.config.args.arguments, "-td CSV");
		} else if (listfindnocase("#chr(9)#|T|TAB", arguments.delimiter, "|")){
			arrayappend(local.config.args.arguments, "-td T");
		} else if (listfindnocase(" |space", arguments.delimiter, "|")){
			arrayappend(local.config.args.arguments, "-td S");
		} else if (listfindnocase(";|semi|semicolon", arguments.delimiter, "|")){
			arrayappend(local.config.args.arguments, "-td Semi");
		} else if (len(arguments.delimiter)){
			arrayappend(local.config.args.arguments, "-td Other");
			arrayappend(local.config.args.arguments, "-tdo #asc(arguments.delimiter)#");
		}
	}

	if (arguments.convertTo eq "sql"){
		arrayappend(local.config.args.arguments, "-table #arguments.table#");
	}

	if (len(trim(arguments.additionalParams))){
		arrayappend(local.config.args.arguments, trim(arguments.additionalParams));
	}

	arrayappend(local.config.args.arguments, "-fo");

	local.args = duplicate(local.config.args);
	local.args.arguments = arraytolist(local.args.arguments, " ");

	cfexecute(attributecollection = local.args);

	local.config.duration = javacast("long", gettickcount() - local.timestart);
	return local.config;
}
</cfscript>