Link to home
Start Free TrialLog in
Avatar of JohnLucania
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?
Avatar of Plucka
Plucka
Flag of Australia image

JohnLucania,

I don't think seperate sheets can be done easliy, seperate Excel documents is quite easy.

Regards
Plucka
Avatar of JohnLucania
JohnLucania

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.
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.

SOLUTION
Avatar of jimmy282
jimmy282
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
<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.
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.
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">

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)+Len(fieldNamesStart)>
<cfset tmp1 = Right(qWDDX,Len(qWDDX)-fieldNamesStartPos)>
<cfset fieldNamesStop = "'">
<cfset fieldNamesLen = FindNoCase(fieldNamesStop,tmp1)>
<cfset tmp2 = Mid(qWDDX,fieldNamesStartPos,fieldNamesLen)>
<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#</CFOUTPUT>
               <cfabort>
     </cfcatch>
</cftry>

<cfheader name="Content-Disposition" value="attachment; filename=#filename#">
<cfcontent file="#ExpandPath(filename)#" type="application/unknown" deletefile="No">
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.
>> The download of the xls file happens with the <cfheader>/<cfcontent> lines at the end.

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.
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.
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.
Grrr, replace this CSVFormat() function with my original one at cflib.org, linked above.
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.

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.columns, arguments.qualifier, arguments.delimiter);
    ArrayResize(returnValue, arguments.query.recordcount + 1);
    arguments.columns = ListToArray(arguments.columns, arguments.delimiter);
    for(i = 1; i LTE arguments.query.recordcount; 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))#">
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.columns, arguments.qualifier, arguments.delimiter);
    ArrayResize(returnValue, arguments.query.recordcount + 1);
    arguments.columns = ListToArray(arguments.columns, arguments.delimiter);
    for(i = 1; i LTE arguments.query.recordcount; 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>
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?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial