Modify http://cflib.org/udf/QueryToCSV2 to use " " or chr(9) (a tab
literal) as the list delimiter.
Alternately, add delimiter as an additional argument, then you can use
it for both CSV and TAB.
Cheers,
.jonah
Here's the one I'm using:
/**
* 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)
* @return Returns a string.
* @author Qasim Rasheed ([email protected])
* @version 1, March 23, 2005
* Modified by jBlossom 08/2011 to create tab delimited output
instead of comma.
*/
function QueryToTAB2(query){
var csv = createobject( 'java', 'java.lang.StringBuffer');
var i = 1;
var j = 1;
var cols = "";
var headers = "";
var endOfLine = chr(13) & chr(10);
if (arraylen(arguments) gte 2) headers = arguments[2];
if (arraylen(arguments) gte 3) cols = arguments[3];
if (not len( trim( cols ) ) ) cols = query.columnlist;
if (not len( trim( headers ) ) ) headers = cols;
headers = listtoarray( headers, " " );
cols = listtoarray( cols, " " );
for (i = 1; i lte arraylen( headers ); i = i + 1)
csv.append( headers[i] & ' ' );
csv.append( endOfLine );
for (i = 1; i lte query.recordcount; i= i + 1){
for (j = 1; j lte arraylen( cols ); j=j + 1){
if (isNumeric( query[cols[j]][i] ) )
csv.append( query[cols[j]][i] & ' ' );
else
csv.append(replace(query[cols[j]][i], ' ', ' ',
'all') & ' ' );
}
csv.append( endOfLine );
}
return csv.toString();
}
On 11/5/11 10:26 PM, Torrent Girl wrote:
>> On 11/4/2011 11:39 PM, Torrent Girl wrote:
>>
>>> the problem is the file will be created on the fly by remote
>> users who will import the data and won't have time to manipulate
>> it by hand.
>>
>> Do you need the time? If not, just send the yyyy-mm-dd format
>> and see if that will open up automatically in Excel as date.
>>
>> I don't know a way to specify a date time automatically in Excel
> >from a CSV file opening. If someone knows, please let us know.
>> --
> Thanks I was able to format the data. It works now.
>
> Any suggestions on creating a TAB file in CF?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348479
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
