asked on

output dynamic table / columns / data

Hi,

I am trying to create a page that will take in a variable table name and output the column headers and data in an excel spreadsheet. The first part is done

<cfset MyNewArray = ArrayNew(1)>
<cfset i =1>

<cfquery datasource="MANIF" name="qryColumnData">
SELECT initcap(lower(REPLACE(column_name, '_', ' '))) COLUMN_NAME, COLUMN_NAME ORIG_COL_NAME
       FROM all_tab_columns utc
       WHERE utc.TABLE_NAME = 'OTIF_DP'
       ORDER BY utc.COLUMN_ID
</cfquery>

<cfquery datasource="MANIF" name="countColumnData">
SELECT count(column_name) total
       FROM all_tab_columns utc
       WHERE utc.TABLE_NAME = 'OTIF_DP'
       ORDER BY utc.COLUMN_ID
</cfquery>

<cfquery datasource="MANIF" name="qryData">
SELECT *
       FROM OTIF_DP
</cfquery>

<CFLOOP query="qryColumnData">
      <cfset MyNewArray[i] = qryColumnData.ORIG_COL_NAME>
      <cfset i = i+1>
</CFLOOP>

<cfheader name="content-disposition" value="inline;filename=AccessToExcelDump.xls">
<cfcontent type="application/msexcel">
<table border="1">
<tr><cfoutput query="qryColumnData">
<th>#qryColumnData.column_name#</th>
</cfoutput>
</tr>

this will give me the column names, howver:

<cfoutput query="qryData">
     <tr>
                  <cfloop index = "LoopCount" from="1" to="#countColumnData.total#">
            <td>#qryData.MyNewArray[LoopCount]#</td>
                  </cfloop>
     </tr>
       </cfoutput>
</table>

I thought that this would output the data but it falls over

I assumed that <td>#qryData.MyNewArray[LoopCount]#</td> would be like putting
<td>#qryData.region_description#</td>

where am I going wrong?

andw928

Replace:

<td>#qryData.MyNewArray[LoopCount]#</td>

With:

<td>#MyNewArray[LoopCount]#</td>

pinaldave

Hi joehodge,

Try to do this: May be you do not need array

Replace following text
<td>#qryData.MyNewArray[LoopCount]#</td>

New code:
<td>#qryColumnData.ORIG_COL_NAME[LoopCount]#</td>

In that case you do not need array.

Regards,
---Pinal

substand

that looks way too complicated for what you seem to want to do... why not use

<cfquery name="q" datasource="yourDSN">
select * from #tablename#
</cfquery>

<table>
<tr>
<cfloop list="#q.ColumnList# index="colname">
<td>#colname#</td>
</cfloop>
</tr>
<cfloop query="q">
<tr>
<cfloop list="#q.columnList#" index="colname">
<td>#evaluate("q." & colname)#</td>
</cfloop>
</tr>
</cfloop>
</table>

(of course, allowing for your cfcontent and such.

joehodge

ASKER

Hi All,

I dont think any of the above work. I've tried the first two (will try the third one now) and I get the following:

<td>#MyNewArray[LoopCount]#</td>

gives me

Load Date        Region      End Market      End Market Desc
LOAD_DATE      REGION      END_MARKET      END_MARKET_DESC
LOAD_DATE      REGION      END_MARKET      END_MARKET_DESC

and so does

<td>#qryColumnData.ORIG_COL_NAME[LoopCount]#</td>

I can get back a dynamic list of column names but cant seem to get the data!

any ideas?

andw928

Does this give the correct information:

<cfdump var='#mynewarray#'>

joehodge

ASKER

hi andw928,

that makes the page hang for a long time and then nothing?

andw928

Replace all that code, with this:

<cfquery datasource="MANIF" name="qryColumnData">
SELECT initcap(lower(REPLACE(column_name, '_', ' '))) COLUMN_NAME, COLUMN_NAME ORIG_COL_NAME
FROM all_tab_columns utc
WHERE utc.TABLE_NAME = 'OTIF_DP'
ORDER BY utc.COLUMN_ID
</cfquery>

<cfquery datasource="MANIF" name="qryData">
SELECT *
FROM OTIF_DP
</cfquery>

<cfheader name="content-disposition" value="inline;filename=AccessToExcelDump.xls">
<cfcontent type="application/msexcel">
<table border="1">
<tr><cfoutput query="qryColumnData">
<th>#qryColumnData.column_name#</th>
</cfoutput>
</tr>

this will give me the column names, howver:

<cfoutput query="qryData">
<tr>
<cfloop query='qrycolumndata'>
<td>#qrycolumndata.orig_col_name#</td>
</cfloop>
</tr>
</cfoutput>
</table>

pinaldave

Hi joehodge,
replace following with :
<cfoutput query="qryData">
<tr>
<cfloop index = "LoopCount" from="1" to="#countColumnData.total#">
<td>#qryData.MyNewArray[LoopCount]#</td>
</cfloop>
</tr>
</cfoutput>

this one:
<cfloop index = "LoopCount" from="1" to="#countColumnData.total#">
#qryColumnData.ORIG_COL_NAME[LoopCount]#
</cfloop>

let me know if that works.

Regards,
---Pinal

joehodge

ASKER

that outputs the column names again Pinal

joehodge

ASKER

I've got the dynamic column names working, its now the actual data that I need to get my mits on.

pinaldave

Hi joehodge,
that is what you are selecting in your query column_name ?
Regards,
---Pinal

joehodge

ASKER

Im not sure if my question was clear so apologies for the confusion,

I can select and output the column names from any table. I then wanted to loop through the data of this table and output it to excel

so the important query is

<cfquery datasource="MANIF" name="qryData">
SELECT *
FROM OTIF_DP
</cfquery>

I want to acheive the following:

<cfheader name="content-disposition" value="inline;filename=AccessToExcelDump.xls">
<cfcontent type="application/msexcel">
<table border="1">
<tr><cfoutput query="qryColumnData">
<th>#qryColumnData.column_name#</th>
</cfoutput>
</tr>

<cfoutput query="qryData">
<tr>
<td>#qryData.Region_Description#</td>
<td>#qryData.Load_Date#</td>
<td>#qryData.Quantity#</td>
<td>#qryData.Late#</td>

</tr>
</cfoutput>
</table>

however, as I will have a number of different tables/data I want to loop through the data dynamically. I thought I could put the column names into an array and replicate the above with:

<cfoutput query="qryData">
<tr>
<cfloop index = "LoopCount" from="1" to="#countColumnData.total#">
<td>#qryData.MyNewArray[LoopCount]#</td>
</cfloop>
</tr>
</cfoutput>

but this falls over. Am I making any sense? please tell me if you need any more info.

thanks

andw928

Replace:

#qryData.MyNewArray[LoopCount]#

With:

#qrydata[mynewarray[loopcount]]#

Tada!

substand

i think i've got it now.

#qryData.MyNewArray[LoopCount]#

should be

#evaluate("qryData." & myNewArray[loopCount])#

andw928

substand, this: #qrydata[mynewarray[loopcount]]# is the exact same thing only without evalaute.

http://livedocs.macromedia.com/wtg/public/coding_standards/performance.html

Performance "Don'ts"

The following are 'negative' recommendations, e.g., "Don't do xyz...".
Don't use evaluate()

Avoid evaluate() unless there is no other way to write your code (and there is almost always another way to write your code)

:)

substand

sorry, i hadn't seen your comment. indeed it is the same thing. I also wasn't aware that evaluate function was slow compared to the other method.

Also the first rule in that optimization article:

"Do not optimize unless you know you have a performance problem!"

Anyway, thanks for the tip.

ASKER CERTIFIED SOLUTION

rob_lorentz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Meps

According to my coldfusion book, #queryname.columnlist# (which is #qryData.MyNewArray[LoopCount]#), will only give you a list of your column names, on top of that it will do it in all caps.

This is why your getting only column names, instead of data.

Just my 2 cents.

joehodge

ASKER

hI,

Thanks for all your help, I tried all the sloutions and unless I did anything wrong I could only get rob_lorentz solution to work. I would like to split the points for all your efforts but Im afraid I will have to give them all to rob_lorentz.

my final code looks like:

<cfquery datasource="MANIF" name="qryData">
SELECT *
FROM OTIF_DP
</cfquery>

<cfheader name="content-disposition" value="inline;filename=AccessToExcelDump.xls">
<cfcontent type="application/msexcel">

<table>
<tr>
<cfoutput>
<cfloop list="#qryData.columnlist#" index="col">
<th>#replace(col, "_", " ", "ALL")#</th>
</cfloop>
</cfoutput>
</tr>
<cfoutput query="qryData">
<tr>
<cfloop list="#qryData.columnlist#" index="col">
<td>#qryData[col][qryData.currentRow]#</th>
</cfloop>
</tr>
</cfoutput>
</table>

and I simply call it from another .cfm page with

<a href="./EXCELTEST.cfm">download the excel file</a>

joehodge

ASKER

the date fields were coming out 00:00:00 format so I modified the above to get

<cfquery datasource="MANIF" name="qryData">
SELECT *
       FROM #p_table_name#
</cfquery>

<cfheader name="content-disposition" value="inline;filename=OracleToExcelDump.xls">
<cfcontent type="application/msexcel">

<table>
<tr>
<cfoutput>
<cfloop list="#qryData.columnlist#" index="col">
<th>#replace(col, "_", " ", "ALL")#</th>
</cfloop>
</cfoutput>
</tr>
<cfoutput query="qryData">
<tr>
<cfloop list="#qryData.columnlist#" index="col">
                    <cfif getMetaData(#qryData[col][qryData.currentRow]#) eq 'class java.sql.Timestamp'>
                    <td>#DateFormat(qryData[col][qryData.currentRow],"dd/mm/yyyy")#</td>
<cfelse>
                   <td>#qryData[col][qryData.currentRow]#</td>
                  </cfif>
</cfloop>
</tr>
</cfoutput>
</table>