JohnLucania
asked on
Download db into Excel wtih cfquery
A datasource has some number of tables.
I want to download **ALL** of the tables with column names into **an** Excel with **separate** sheets.
How to do? If not possible, any alternatives?
I want to download **ALL** of the tables with column names into **an** Excel with **separate** sheets.
How to do? If not possible, any alternatives?
ASKER
>> seperate Excel documents is quite easy
ok, can column names of each table be automatically downloaded?
I am trying to avoid manual coding for the column names.
ok, can column names of each table be automatically downloaded?
I am trying to avoid manual coding for the column names.
I can give you some code that will convert the query into a .csv file in the exact same order of columns as in the query...
let me dig for it and I will paste it here.
let me dig for it and I will paste it here.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
<cfset filename = "filename.xls"> should work, right?
yeah it should...
Hey, that's my CSVFormat() function from CFLib.org.
http://www.cflib.org/udf.cfm?ID=404
Depending on the type of data and datatypes you're dumping to Excel, this function could have some potential problems, but should work in nearly all cases.
http://www.cflib.org/udf.cfm?ID=404
Depending on the type of data and datatypes you're dumping to Excel, this function could have some potential problems, but should work in nearly all cases.
ASKER
I need two more things:
1) need to have column names on the csv/xls file
2) need to download from Web browser (IE 5.5/6.0). It puts the file on the server directory, which makes users unable to download the file.
1) need to have column names on the csv/xls file
2) need to download from Web browser (IE 5.5/6.0). It puts the file on the server directory, which makes users unable to download the file.
For 1, uncomment the two lines that output column headers.
function CSVFormat(query)
{
var returnValue = ArrayNew(1);
var rowValue = '';
var columns = orderColumns(query);
var qualifier = '';
var i = 1;
var j = 1;
if(ArrayLen(Arguments) GTE 2) qualifier = Arguments[2];
if(ArrayLen(Arguments) GTE 3 AND Len(Arguments[3])) columns = Arguments[3];
returnValue[1] = ListQualify(columns, qualifier);
ArrayResize(returnValue, query.recordcount + 1);
columns = ListToArray(columns);
for(i = 0; i LTE query.recordcount; i = i + 1)
{
rowValue = ArrayNew(1);
ArrayResize(rowValue, ArrayLen(columns));
for(j = 1; j LTE ArrayLen(columns); j = j + 1)
rowValue[j] = qualifier & query[columns[j]][i] & qualifier;
returnValue[i + 1] = ArrayToList(rowValue);
}
returnValue = ArrayToList(returnValue, Chr(13));
return returnValue;
}
For #2, use <cfheader>/<cfcontent> to push this newly created file to the user:
<cfheader name="Content-Disposition" value="attachment; filename=#filename#">
<cfcontent file="#ExpandPath(filename )#" type="application/unknown" deletefile="No">
function CSVFormat(query)
{
var returnValue = ArrayNew(1);
var rowValue = '';
var columns = orderColumns(query);
var qualifier = '';
var i = 1;
var j = 1;
if(ArrayLen(Arguments) GTE 2) qualifier = Arguments[2];
if(ArrayLen(Arguments) GTE 3 AND Len(Arguments[3])) columns = Arguments[3];
returnValue[1] = ListQualify(columns, qualifier);
ArrayResize(returnValue, query.recordcount + 1);
columns = ListToArray(columns);
for(i = 0; i LTE query.recordcount; i = i + 1)
{
rowValue = ArrayNew(1);
ArrayResize(rowValue, ArrayLen(columns));
for(j = 1; j LTE ArrayLen(columns); j = j + 1)
rowValue[j] = qualifier & query[columns[j]][i] & qualifier;
returnValue[i + 1] = ArrayToList(rowValue);
}
returnValue = ArrayToList(returnValue, Chr(13));
return returnValue;
}
For #2, use <cfheader>/<cfcontent> to push this newly created file to the user:
<cfheader name="Content-Disposition"
<cfcontent file="#ExpandPath(filename
ASKER
Columns still do not show up.
I have:
<cfquery name="myquery" datasource="GateKeeper">
select * from Demo
</cfquery>
<cffunction name="orderColumns">
<cfargument name="query" required="true">
<!--- Serialize the query --->
<cfwddx action="CFML2WDDX" input="#arguments.query#" output='qWDDX'>
<!--- Get the list of column names --->
<cfset fieldNamesStart = "fieldNames='">
<cfset fieldNamesStartPos = FindNoCase(fieldNamesStart ,qWDDX)+Le n(fieldNam esStart)>
<cfset tmp1 = Right(qWDDX,Len(qWDDX)-fie ldNamesSta rtPos)>
<cfset fieldNamesStop = "'">
<cfset fieldNamesLen = FindNoCase(fieldNamesStop, tmp1)>
<cfset tmp2 = Mid(qWDDX,fieldNamesStartP os,fieldNa mesLen)>
<cfreturn tmp2>
</cffunction>
<cfscript>
function CSVFormat(query)
{
var returnValue = ArrayNew(1);
var rowValue = '';
var columns = orderColumns(query);
var qualifier = '';
var i = 1;
var j = 1;
if(ArrayLen(Arguments) GTE 2) qualifier = Arguments[2];
if(ArrayLen(Arguments) GTE 3 AND Len(Arguments[3])) columns = Arguments[3];
returnValue[1] = ListQualify(columns, qualifier);
ArrayResize(returnValue, query.recordcount + 1);
columns = ListToArray(columns);
for(i = 0; i LTE query.recordcount; i = i + 1)
{
rowValue = ArrayNew(1);
ArrayResize(rowValue, ArrayLen(columns));
for(j = 1; j LTE ArrayLen(columns); j = j + 1)
rowValue[j] = qualifier & query[columns[j]][i] & qualifier;
returnValue[i + 1] = ArrayToList(rowValue);
}
returnValue = ArrayToList(returnValue, Chr(13));
return returnValue;
}
</cfscript>
<cfset filename = "filename.csv">
<cftry>
<cffile action="write" file="#ExpandPath(filename )#" output="#CsvFormat(myquery )#">
<cfcatch>
<Cfoutput>#cfcatch.message #</CFOUTPU T>
<cfabort>
</cfcatch>
</cftry>
<cfheader name="Content-Disposition" value="attachment; filename=#filename#">
<cfcontent file="#ExpandPath(filename )#" type="application/unknown" deletefile="No">
I have:
<cfquery name="myquery" datasource="GateKeeper">
select * from Demo
</cfquery>
<cffunction name="orderColumns">
<cfargument name="query" required="true">
<!--- Serialize the query --->
<cfwddx action="CFML2WDDX" input="#arguments.query#" output='qWDDX'>
<!--- Get the list of column names --->
<cfset fieldNamesStart = "fieldNames='">
<cfset fieldNamesStartPos = FindNoCase(fieldNamesStart
<cfset tmp1 = Right(qWDDX,Len(qWDDX)-fie
<cfset fieldNamesStop = "'">
<cfset fieldNamesLen = FindNoCase(fieldNamesStop,
<cfset tmp2 = Mid(qWDDX,fieldNamesStartP
<cfreturn tmp2>
</cffunction>
<cfscript>
function CSVFormat(query)
{
var returnValue = ArrayNew(1);
var rowValue = '';
var columns = orderColumns(query);
var qualifier = '';
var i = 1;
var j = 1;
if(ArrayLen(Arguments) GTE 2) qualifier = Arguments[2];
if(ArrayLen(Arguments) GTE 3 AND Len(Arguments[3])) columns = Arguments[3];
returnValue[1] = ListQualify(columns, qualifier);
ArrayResize(returnValue, query.recordcount + 1);
columns = ListToArray(columns);
for(i = 0; i LTE query.recordcount; i = i + 1)
{
rowValue = ArrayNew(1);
ArrayResize(rowValue, ArrayLen(columns));
for(j = 1; j LTE ArrayLen(columns); j = j + 1)
rowValue[j] = qualifier & query[columns[j]][i] & qualifier;
returnValue[i + 1] = ArrayToList(rowValue);
}
returnValue = ArrayToList(returnValue, Chr(13));
return returnValue;
}
</cfscript>
<cfset filename = "filename.csv">
<cftry>
<cffile action="write" file="#ExpandPath(filename
<cfcatch>
<Cfoutput>#cfcatch.message
<cfabort>
</cfcatch>
</cftry>
<cfheader name="Content-Disposition"
<cfcontent file="#ExpandPath(filename
ASKER
How do you download this in Excel?
Apologies, you need to swap the following two lines:
returnValue[1] = ListQualify(columns, qualifier);
ArrayResize(returnValue, query.recordcount + 1);
to become:
ArrayResize(returnValue, query.recordcount + 1);
returnValue[1] = ListQualify(columns, qualifier);
The download of the xls file happens with the <cfheader>/<cfcontent> lines at the end.
returnValue[1] = ListQualify(columns, qualifier);
ArrayResize(returnValue, query.recordcount + 1);
to become:
ArrayResize(returnValue, query.recordcount + 1);
returnValue[1] = ListQualify(columns, qualifier);
The download of the xls file happens with the <cfheader>/<cfcontent> lines at the end.
ASKER
>> The download of the xls file happens with the <cfheader>/<cfcontent> lines at the end.
Can you please explain further?
Can you please explain further?
Those two lines send the proper header to the browser, then stream the file from the server's harddrive to the end-user.
ASKER
ok, so do I need to change anything to download them in Excel?
You're better off forcing a save/open dialog, which is what the "application/unknown" is for.
ASKER
I have:
<cfscript>
function CSVFormat(query)
{
var returnValue = ArrayNew(1);
var rowValue = '';
var columns = query.columnlist;
//var columns = orderColumns(query);
var qualifier = '';
var i = 1;
var j = 1;
if(ArrayLen(Arguments) GTE 2) qualifier = Arguments[2];
if(ArrayLen(Arguments) GTE 3 AND Len(Arguments[3])) columns = Arguments[3];
ArrayResize(returnValue, query.recordcount + 1);
returnValue[1] = ListQualify(columns, qualifier);
columns = ListToArray(columns);
for(i = 0; i LTE query.recordcount; i = i + 1)
{
rowValue = ArrayNew(1);
ArrayResize(rowValue, ArrayLen(columns));
for(j = 1; j LTE ArrayLen(columns); j = j + 1)
rowValue[j] = qualifier & query[columns[j]][i] & qualifier;
returnValue[i + 1] = ArrayToList(rowValue);
}
returnValue = ArrayToList(returnValue, Chr(13));
return returnValue;
}
</cfscript>
but, still column names are not showing up.
<cfscript>
function CSVFormat(query)
{
var returnValue = ArrayNew(1);
var rowValue = '';
var columns = query.columnlist;
//var columns = orderColumns(query);
var qualifier = '';
var i = 1;
var j = 1;
if(ArrayLen(Arguments) GTE 2) qualifier = Arguments[2];
if(ArrayLen(Arguments) GTE 3 AND Len(Arguments[3])) columns = Arguments[3];
ArrayResize(returnValue, query.recordcount + 1);
returnValue[1] = ListQualify(columns, qualifier);
columns = ListToArray(columns);
for(i = 0; i LTE query.recordcount; i = i + 1)
{
rowValue = ArrayNew(1);
ArrayResize(rowValue, ArrayLen(columns));
for(j = 1; j LTE ArrayLen(columns); j = j + 1)
rowValue[j] = qualifier & query[columns[j]][i] & qualifier;
returnValue[i + 1] = ArrayToList(rowValue);
}
returnValue = ArrayToList(returnValue, Chr(13));
return returnValue;
}
</cfscript>
but, still column names are not showing up.
Grrr, replace this CSVFormat() function with my original one at cflib.org, linked above.
ASKER
ok, I replaced it and column names show up, but I see a problem there.
The function rearranges column names in alphabetical order, which is ok. But,
it shifts one column to right when there are . or ,.
For example, addresses show up as below:
ADDRESS ASSESSMENTCOMPLETE
55 Bagley Rd. Apt 203
695 Shakespeare No
27 Greenhaven No
8 North Park Circle Apt E Yes
6170 Sylvia Dr. Yes
580 Lindburg Dr. No
14594 Grey Stone No
14547 Polo Club Drive Bldg 8 No
5715 Gateway Lane No
18800 Westwood Dr. #518
407 Hazel Drive No
8551 Woodside Crossing South No
5601 Broadview Apt a6 No
55 Barrett Rd. #222
W. Blvd. No
256 Fournier Dr. No
235 Barberry No
14820 Cherokee Trail No
15902 Galemore Dr No
9565 Columbia Rd No
16117 Muskinghum No
607 Prospect No
which should have been like:
55 Bagley Rd., Apt 203
695 Shakespeare
27 Greenhaven
8 North Park Circle Apt E
6170 Sylvia Dr.
580 Lindburg Dr.
14594 Grey Stone
14547 Polo Club Drive Bldg 8
5715 Gateway Lane
18800 Westwood Dr., #518
407 Hazel Drive
8551 Woodside Crossing South
5601 Broadview Apt a6
55 Barrett Rd., #222
If it does not rearrange the column names w/o shifting a columns when hitting . , and downloadable in Excel, it would be wonderful.
The function rearranges column names in alphabetical order, which is ok. But,
it shifts one column to right when there are . or ,.
For example, addresses show up as below:
ADDRESS ASSESSMENTCOMPLETE
55 Bagley Rd. Apt 203
695 Shakespeare No
27 Greenhaven No
8 North Park Circle Apt E Yes
6170 Sylvia Dr. Yes
580 Lindburg Dr. No
14594 Grey Stone No
14547 Polo Club Drive Bldg 8 No
5715 Gateway Lane No
18800 Westwood Dr. #518
407 Hazel Drive No
8551 Woodside Crossing South No
5601 Broadview Apt a6 No
55 Barrett Rd. #222
W. Blvd. No
256 Fournier Dr. No
235 Barberry No
14820 Cherokee Trail No
15902 Galemore Dr No
9565 Columbia Rd No
16117 Muskinghum No
607 Prospect No
which should have been like:
55 Bagley Rd., Apt 203
695 Shakespeare
27 Greenhaven
8 North Park Circle Apt E
6170 Sylvia Dr.
580 Lindburg Dr.
14594 Grey Stone
14547 Polo Club Drive Bldg 8
5715 Gateway Lane
18800 Westwood Dr., #518
407 Hazel Drive
8551 Woodside Crossing South
5601 Broadview Apt a6
55 Barrett Rd., #222
If it does not rearrange the column names w/o shifting a columns when hitting . , and downloadable in Excel, it would be wonderful.
Ah, well, it seems you're going to need a more robust solution.
<cffunction name="CSVFormat" access="private" hint="query[, delimiter, qualifier, columns]">
<cfargument name="query" type="query" required="Yes">
<cfargument name="delimiter" type="string" required="No" default=",">
<cfargument name="qualifier" type="string" required="No" default="">
<cfargument name="columns" type="string" required="No" default="">
<cfif NOT Len(arguments.columns)>
<cfwddx action="CFML2WDDX" input="#arguments.query#" output="packet">
<cfscript>
fieldNamesStart = 'fieldNames=''';
fieldNamesStartPos = FindNoCase(fieldNamesStart , packet) + Len(fieldNamesStart);
arguments.columns = Mid(packet, fieldNamesStartPos, FindNoCase('''', Right(packet , Len(packet) - fieldNamesStartPos)));
</cfscript>
</cfif>
<cfscript>
var returnValue = ArrayNew(1);
var i = 1;
var j = 1;
arguments.columns = ListChangeDelims(arguments .columns, arguments.delimiter, ',');
returnValue[1] = ListQualify(arguments.colu mns, arguments.qualifier, arguments.delimiter);
ArrayResize(returnValue, arguments.query.recordcoun t + 1);
arguments.columns = ListToArray(arguments.colu mns, arguments.delimiter);
for(i = 1; i LTE arguments.query.recordcoun t; i = i + 1)
{
rowValue = ArrayNew(1);
ArrayResize(rowValue, ArrayLen(arguments.columns ));
for(j = 1; j LTE ArrayLen(arguments.columns ); j = j + 1)
rowValue[j] = arguments.qualifier & arguments.query[arguments. columns[j] ][i] & arguments.qualifier;
returnValue[i + 1] = ArrayToList(rowValue, arguments.delimiter);
}
returnValue = ArrayToList(returnValue, Chr(13));
return returnValue;
</cfscript>
<cfreturn returnValue>
</cffunction>
Now, when you call it, define what you want the delimiter and qualifier to be. I'd recommend tab and double-quote, respectively.
<cffile action="write" file="#ExpandPath(filename )#" output="#CSVFormat(myquery , Chr(9), Chr(34))#">
<cffunction name="CSVFormat" access="private" hint="query[, delimiter, qualifier, columns]">
<cfargument name="query" type="query" required="Yes">
<cfargument name="delimiter" type="string" required="No" default=",">
<cfargument name="qualifier" type="string" required="No" default="">
<cfargument name="columns" type="string" required="No" default="">
<cfif NOT Len(arguments.columns)>
<cfwddx action="CFML2WDDX" input="#arguments.query#" output="packet">
<cfscript>
fieldNamesStart = 'fieldNames=''';
fieldNamesStartPos = FindNoCase(fieldNamesStart
arguments.columns = Mid(packet, fieldNamesStartPos, FindNoCase('''', Right(packet , Len(packet) - fieldNamesStartPos)));
</cfscript>
</cfif>
<cfscript>
var returnValue = ArrayNew(1);
var i = 1;
var j = 1;
arguments.columns = ListChangeDelims(arguments
returnValue[1] = ListQualify(arguments.colu
ArrayResize(returnValue, arguments.query.recordcoun
arguments.columns = ListToArray(arguments.colu
for(i = 1; i LTE arguments.query.recordcoun
{
rowValue = ArrayNew(1);
ArrayResize(rowValue, ArrayLen(arguments.columns
for(j = 1; j LTE ArrayLen(arguments.columns
rowValue[j] = arguments.qualifier & arguments.query[arguments.
returnValue[i + 1] = ArrayToList(rowValue, arguments.delimiter);
}
returnValue = ArrayToList(returnValue, Chr(13));
return returnValue;
</cfscript>
<cfreturn returnValue>
</cffunction>
Now, when you call it, define what you want the delimiter and qualifier to be. I'd recommend tab and double-quote, respectively.
<cffile action="write" file="#ExpandPath(filename
ASKER
I am getting "Local variable returnValue on line 65 must be grouped at the top of the function body."
Sorry, my mistake:
<cffunction name="CSVFormat" access="private" hint="query[, delimiter, qualifier, columns]">
<cfargument name="query" type="query" required="Yes">
<cfargument name="delimiter" type="string" required="No" default=",">
<cfargument name="qualifier" type="string" required="No" default="">
<cfargument name="columns" type="string" required="No" default="">
<cfscript>
var returnValue = ArrayNew(1);
var i = 1;
var j = 1;
</cfscript>
<cfif NOT Len(arguments.columns)>
<cfwddx action="CFML2WDDX" input="#arguments.query#" output="packet">
<cfscript>
fieldNamesStart = 'fieldNames=''';
fieldNamesStartPos = FindNoCase(fieldNamesStart , packet) + Len(fieldNamesStart);
arguments.columns = Mid(packet, fieldNamesStartPos, FindNoCase('''', Right(packet , Len(packet) - fieldNamesStartPos)));
</cfscript>
</cfif>
<cfscript>
arguments.columns = ListChangeDelims(arguments .columns, arguments.delimiter, ',');
returnValue[1] = ListQualify(arguments.colu mns, arguments.qualifier, arguments.delimiter);
ArrayResize(returnValue, arguments.query.recordcoun t + 1);
arguments.columns = ListToArray(arguments.colu mns, arguments.delimiter);
for(i = 1; i LTE arguments.query.recordcoun t; i = i + 1)
{
rowValue = ArrayNew(1);
ArrayResize(rowValue, ArrayLen(arguments.columns ));
for(j = 1; j LTE ArrayLen(arguments.columns ); j = j + 1)
rowValue[j] = arguments.qualifier & arguments.query[arguments. columns[j] ][i] & arguments.qualifier;
returnValue[i + 1] = ArrayToList(rowValue, arguments.delimiter);
}
returnValue = ArrayToList(returnValue, Chr(13));
</cfscript>
<cfreturn returnValue>
</cffunction>
<cffunction name="CSVFormat" access="private" hint="query[, delimiter, qualifier, columns]">
<cfargument name="query" type="query" required="Yes">
<cfargument name="delimiter" type="string" required="No" default=",">
<cfargument name="qualifier" type="string" required="No" default="">
<cfargument name="columns" type="string" required="No" default="">
<cfscript>
var returnValue = ArrayNew(1);
var i = 1;
var j = 1;
</cfscript>
<cfif NOT Len(arguments.columns)>
<cfwddx action="CFML2WDDX" input="#arguments.query#" output="packet">
<cfscript>
fieldNamesStart = 'fieldNames=''';
fieldNamesStartPos = FindNoCase(fieldNamesStart
arguments.columns = Mid(packet, fieldNamesStartPos, FindNoCase('''', Right(packet , Len(packet) - fieldNamesStartPos)));
</cfscript>
</cfif>
<cfscript>
arguments.columns = ListChangeDelims(arguments
returnValue[1] = ListQualify(arguments.colu
ArrayResize(returnValue, arguments.query.recordcoun
arguments.columns = ListToArray(arguments.colu
for(i = 1; i LTE arguments.query.recordcoun
{
rowValue = ArrayNew(1);
ArrayResize(rowValue, ArrayLen(arguments.columns
for(j = 1; j LTE ArrayLen(arguments.columns
rowValue[j] = arguments.qualifier & arguments.query[arguments.
returnValue[i + 1] = ArrayToList(rowValue, arguments.delimiter);
}
returnValue = ArrayToList(returnValue, Chr(13));
</cfscript>
<cfreturn returnValue>
</cffunction>
ASKER
it looks better now, but I see two issues:
-. All dates are showing as 00:00.0. I need the actual dates showing up.
-. If there is 'Address 2' (i.e. Apt, Room #, bldg #, etc), it makes the rest of fields shift one column to right.
How to fix these?
-. All dates are showing as 00:00.0. I need the actual dates showing up.
-. If there is 'Address 2' (i.e. Apt, Room #, bldg #, etc), it makes the rest of fields shift one column to right.
How to fix these?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I don't think seperate sheets can be done easliy, seperate Excel documents is quite easy.
Regards
Plucka