Last active
April 27, 2018 15:36
-
-
Save JamoCA/b2d0467dd914909f00bf to your computer and use it in GitHub Desktop.
QueryToCSV2 UDF. Added parameters to pass data types for better formatting when converting CSV to Excel.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* 10/4/2010 Forked from http://www.cflib.org/udf/QueryToCSV2 | |
| Convert the query into a CSV format using Java StringBuffer Class. | |
| @param query The query to convert. (Required) | |
| @param headers A list of headers to use for the first row of the CSV string. Defaults to all the columns. (Optional) | |
| @param cols The columns from the query to transform. Defaults to all the columns. (Optional) | |
| @param FieldsAsText struct w/keyname & data type. (Optional) | |
| @param Exportformat "csv" (default) or "tab"-delimited (Optional) | |
| @return Returns a string. | |
| @author Qasim Rasheed (qasimrasheed@hotmail.com) | |
| @version 1, March 23, 2005 | |
| @version 2, June 8, 2015 by James Moberg (sunstarmedia.com) Now requires ColdFusion 7+ | |
| @version 3, December 10, 2015 by James Moberg (sunstarmedia.com) to optionally format query values (date, time, money, text) | |
| Date/Time values use ISO 8601 YYYY-MM-DD format for better Excel compatibility | |
| @version 4, April 27 2018 by James Moberg (sunstarmedia.com) to allow for tab-delimited data (no quotes or comma separators) | |
| https://en.wikipedia.org/wiki/ISO_8601#Dates | |
| Syntax: | |
| QueryToCSV2(Query, headers, cols, list|struct) | |
| DEMO: Text list of headers to treat as text | |
| QueryToCSV2(Query, "", "", "Phone,Mobile,zip") | |
| DEMO: Struct to identify headers & formats | |
| QueryToCSV2(Query, "", "", {dateadded="date", orderTotal="money", zip="text"}) | |
| DEMO: Export as tab-delimited (strips quotes and "," delimiter) | |
| QueryToCSV2(Query, "", "", "", "tab") | |
| */ | |
| function QueryToCSV2(query){ | |
| var csv = createobject( 'java', 'java.lang.StringBuffer'); | |
| var i = 1; | |
| var j = 1; | |
| var cols = ""; | |
| var headers = ""; | |
| var FieldsAsText = ""; | |
| var FieldsAsTextPos = StructNew(); | |
| var endOfLine = chr(13) & chr(10); | |
| var exportDelim = ","; | |
| var delim = ","; | |
| var quoteChar = """"; | |
| var formats = structNew(); | |
| var Exportformat = "csv"; | |
| if (arraylen(arguments) gte 2 AND LEN(TRIM(arguments[2]))) headers = arguments[2]; | |
| if (arraylen(arguments) gte 3 AND LEN(TRIM(arguments[3]))) cols = arguments[3]; | |
| if (arraylen(arguments) gte 4){ | |
| if ( IsSimpleValue(arguments[4]) AND LEN(TRIM(arguments[4]))){ | |
| FieldsAsText = arguments[4]; | |
| arguments[4] = listToArray(arguments[4]); | |
| for (i=1;i LTE ArrayLen(array);i=i+1) { | |
| formats[trim(arguments[4][i])] = "text"; | |
| } | |
| } else if (isStruct(arguments[4]) ){ | |
| formats = arguments[4]; | |
| } | |
| } | |
| if (arraylen(arguments) gte 5 AND ListFindNoCase("text,tab,txt", arguments[5])) { | |
| exportDelim = chr(9); | |
| delim = chr(9); | |
| quoteChar = ""; | |
| } | |
| if (not len( trim( cols ) ) ) cols = arraytoList(query.getMetaData().getColumnLabels()); | |
| if (not len( trim( headers ) ) ) headers = cols; | |
| headers = listtoarray( headers ); | |
| cols = listtoarray( cols ); | |
| for (i = 1; i lte arraylen( headers ); i = i + 1){ | |
| if (i GTE arraylen( headers )){ | |
| delim = ""; | |
| } | |
| csv.append( quoteChar & headers[i] & quoteChar & delim ); | |
| /* reassign format data to indexed-based */ | |
| if (StructCount(Formats) GT 0 AND StructKeyExists(Formats, headers[i])){ | |
| Formats[i] = Formats[headers[i]]; | |
| } | |
| } | |
| csv.append( endOfLine ); | |
| for (i = 1; i lte query.recordcount; i= i + 1){ | |
| delim = exportDelim; | |
| for (j = 1; j lte arrayLen(cols); j=j + 1){ | |
| if (j GTE arrayLen(cols)){ | |
| delim = ""; | |
| } | |
| if (StructKeyExists(Formats, j)){ | |
| if (listfindnocase("dateonly,smalldate,dateonlytext,smalldatetext", Formats[j])){ | |
| if (isValid("date", query[cols[j]][i] ) ){ | |
| csv.append( '#quoteChar##DateFormat(query[cols[j]][i], "yyyy-mm-dd")##quoteChar#' & delim ); | |
| } else { | |
| if (findnocase("text", Formats[j])){ | |
| csv.append( query[cols[j]][i] & delim ); | |
| } else { | |
| csv.append( '#quoteChar##quoteChar#' & delim ); | |
| } | |
| } | |
| } else if (listfindnocase("exceldate,exceldatetext", Formats[j])){ | |
| if (isValid("date", query[cols[j]][i] ) ){ | |
| csv.append( "#DateFormat(query[cols[j]][i], "m/d/yyyy")#" & delim ); | |
| } else { | |
| if (findnocase("text", Formats[j])){ | |
| csv.append( query[cols[j]][i] & delim ); | |
| } else { | |
| csv.append( '#quoteChar##quoteChar#' & delim ); | |
| } | |
| } | |
| } else if (listfindnocase("exceldatetime,exceldatetimetext", Formats[j])){ | |
| if (isValid("date", query[cols[j]][i] ) ){ | |
| csv.append( "#DateFormat(query[cols[j]][i], "m/d/yyyy")# #TimeFormat(query[cols[j]][i], 'h:mm:ss tt')#" & delim ); | |
| } else { | |
| if (findnocase("text", Formats[j])){ | |
| csv.append( query[cols[j]][i] & delim ); | |
| } else { | |
| csv.append( '#quoteChar##quoteChar#' & delim ); | |
| } | |
| } | |
| } else if (listfindnocase("date,datetime,smalldatetime,timestamp,datetext,datetimetext,smalldatetimetext,timestamptext", Formats[j])){ | |
| if (isValid("date", query[cols[j]][i] ) ){ | |
| csv.append( '#quoteChar##DateFormat(query[cols[j]][i], "yyyy-mm-dd")# #TimeFormat(query[cols[j]][i], 'HH:mm:ss')##quoteChar#' & delim ); | |
| } else { | |
| if (findnocase("text", Formats[j])){ | |
| csv.append( query[cols[j]][i] & delim ); | |
| } else { | |
| csv.append( '#quoteChar##quoteChar#' & delim ); | |
| } | |
| } | |
| } else if (listfindnocase("time,timetext", Formats[j])){ | |
| if (isValid("time", query[cols[j]][i] ) ){ | |
| csv.append( '#quoteChar##TimeFormat(query[cols[j]][i], "HH:mm:ss")##quoteChar#' & delim ); | |
| } else { | |
| if (findnocase("text", Formats[j])){ | |
| csv.append( query[cols[j]][i] & delim ); | |
| } else { | |
| csv.append( '#quoteChar##quoteChar#' & delim ); | |
| } | |
| } | |
| } else if (listfindnocase("hourminute,hourminutetext", Formats[j])){ | |
| if (isValid("time", query[cols[j]][i] ) ){ | |
| csv.append( '#quoteChar##TimeFormat(query[cols[j]][i], "HH:mm")##quoteChar#' & delim ); | |
| } else { | |
| if (findnocase("text", Formats[j])){ | |
| csv.append( query[cols[j]][i] & delim ); | |
| } else { | |
| csv.append( '#quoteChar##quoteChar#' & delim ); | |
| } | |
| } | |
| } else if (listfindnocase("money,smallmoney", Formats[j])){ | |
| csv.append( NumberFormat(VAL(query[cols[j]][i]),'9.90') & delim ); | |
| } else if (listfindnocase("text,string,varchar", Formats[j]) OR NOT isNumeric(query[cols[j]][i]) ){ | |
| csv.append( quoteChar & query[cols[j]][i] & quoteChar & delim ); | |
| } else if (listfindnocase("int,integer,tinyint,smallint", Formats[j]) AND isNumeric(query[cols[j]][i])){ | |
| csv.append( VAL(query[cols[j]][i]) & delim ); | |
| } else if (isNumeric(query[cols[j]][i]) AND isValid("integer", query[cols[j]][i]) AND compare(query[cols[j]][i], reReplaceNoCase(query[cols[j]][i], '[^[:digit:]]', '', 'ALL') ) IS 0 ){ | |
| csv.append( VAL(query[cols[j]][i]) & delim ); | |
| } else { | |
| csv.append( query[cols[j]][i] & delim ); | |
| } | |
| } else if (NOT isNumeric(query[cols[j]][i]) ){ | |
| csv.append( quoteChar & query[cols[j]][i] & quoteChar & delim ); | |
| } else { | |
| csv.append( query[cols[j]][i] & delim ); | |
| } | |
| } | |
| csv.append( endOfLine ); | |
| } | |
| return csv.toString(); | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment