Transforming TSV data using ColdFusion & Miller (Commandline FTW!) #cfml

miller-tsv.cfm

<!--- Transforming TSV data using ColdFusion & Miller (cfml) - 2024-09-05
Gist: https://gist.github.com/JamoCA/94f614fa1b2d881f26f26f9926a9b1fa
Blog: https://dev.to/gamesover/transforming-tsv-data-using-coldfusion-miller-command-line-ftw-3kd
Tweet: https://x.com/gamesover/status/1831816238089302139
Miller: https://github.com/johnkerl/miller
--->

<cfscript>
public void function fixTSVPostalCode(string filename, string fileDir) hint="I perform an inline update to a TSV file and set ZIP+4 to ZIP" {
	local.dh = datetimeformat(now(),'yyyymmddHHnnsslll');
	local.bat = [
		"cd /d #arguments.fileDir#",
		"c:\miller\mlr.exe -I --itsv --otsv --ors '\r\n' put '$PostalCode = substr($PostalCode,0,4)' #arguments.fileDir#\#arguments.filename#",
		"REM The next line deletes this BAT file https://stackoverflow.com/a/20333575/693068",
		"(goto) 2>nul & del ""%~f0"""
	];
	filewrite("#arguments.fileDir#\mlr_#local.dh#.bat", arraytolist(local.bat, chr(13) & chr(10)));
	cfexecute(name="#arguments.fileDir#\mlr_#local.dh#.bat", timeout="25");
}
</cfscript>