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?
JohnLucaniaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.