Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

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?
0
JohnLucania
Asked:
JohnLucania
  • 11
  • 9
  • 3
  • +1
2 Solutions
 
PluckaCommented:
JohnLucania,

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

Regards
Plucka
0
 
JohnLucaniaAuthor Commented:
>> 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.
0
 
jimmy282Commented:
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.

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jimmy282Commented:
<cfquery name="myquery" datasource="your_datasource">
select * from tablename
</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 = 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];
  // no columns required
 
  //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>
0
 
JohnLucaniaAuthor Commented:
<cfset filename = "filename.xls"> should work, right?
0
 
jimmy282Commented:
yeah it should...
0
 
JeffHowdenCommented:
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.
0
 
JohnLucaniaAuthor Commented:
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.
0
 
JeffHowdenCommented:
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">

0
 
JohnLucaniaAuthor Commented:
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">
0
 
JohnLucaniaAuthor Commented:
How do you download this in Excel?
0
 
JeffHowdenCommented:
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.
0
 
JohnLucaniaAuthor Commented:
>> The download of the xls file happens with the <cfheader>/<cfcontent> lines at the end.

Can you please explain further?
0
 
JeffHowdenCommented:
Those two lines send the proper header to the browser, then stream the file from the server's harddrive to the end-user.
0
 
JohnLucaniaAuthor Commented:
ok, so do I need to change anything to download them in Excel?
0
 
JeffHowdenCommented:
You're better off forcing a save/open dialog, which is what the "application/unknown" is for.
0
 
JohnLucaniaAuthor Commented:
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.
0
 
JeffHowdenCommented:
Grrr, replace this CSVFormat() function with my original one at cflib.org, linked above.
0
 
JohnLucaniaAuthor Commented:
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.

0
 
JeffHowdenCommented:
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))#">
0
 
JohnLucaniaAuthor Commented:
I am getting "Local variable returnValue on line 65 must be grouped at the top of the function body."
0
 
JeffHowdenCommented:
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>
0
 
JohnLucaniaAuthor Commented:
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?
0
 
JeffHowdenCommented:
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 11
  • 9
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now