Downloading database into Excel

[gatetec]

I have the following tables to be downloaded in CFMX. The actual table names are:

Demo
FuncAbility
GatekeeperVisit
HealthStatus
HospitalVisit
Institutation
Insurance
Intake
PhoneTrack
PsychSocialCognitive
Services


What I am trying to do:

?? question ==> need to do cfloop for all tables above mentioned:

<CFQUERY NAME="ExportData" DATASOURCE="Gkeeper">
SELECT *
FROM tablename
</CFQUERY>

<CFSET tempfile = "C:\temp\exptemp.csv">
<CFSET output = "">

?? question ==> there are many filed (column) names, so I like to do cfloop so that I don't need to hard-code all fields.

<CFSET output = output & "field1 field2 field3">

<CFFILE ACTION="WRITE"
FILE="#tempfile#"
OUTPUT="#output#">

<CFLOOP QUERY="GetList">
<CFSET output = "">
?? question ==> I like to do cfloop so that I don't need to hard-code all fields.
<CFSET output = output & "#field1# #field2# #field3#>
<CFFILE ACTION="APPEND"
FILE="#tempfile#"
OUTPUT="#output#">
</CFLOOP>
<CFCONTENT TYPE="application/excel" FILE="#tempfile#">

and then
<CFMAIL>
?? question: e-mail new .XLS files
</CFMAIL>

?? question: remove all .XLS files so that always new .XLS files can be downloaded and e-mailed.

Please advice.

thx much

 

[Sarky78]

Not a full solution, but should give you a start:

<cfset tbls = "table1,table2">

<cfoutput>
  <cfloop list="#tbls#" index="i">
		<cfquery datasource="" name="qryTable">
			select *
			from #i#
		</cfquery>
	
		<cfif qryTable.RecordCount>
			<cfset ColList = qryTable.ColumnList>
			<cfloop list="#ColList#" index="j">
				#j#<br />
				<cfloop query="qryTable">
					#qryTable[j][CurrentRow]#
				</cfloop><br />
			</cfloop>
		</cfif>
	</cfloop>
</cfoutput>

Hope this helps!

Tony

 

[gatetec]

Sarky78,

it pulls out all of the data from the tables, but I am having a tough time to put the outputs onto Excel. Can you pls help on this further?

thx much

 

[Sarky78]

Give this a whirl:

<cfset tbls = "table1,table2">

<cfoutput>
	<cfloop list="#tbls#" index="i">
		<cfquery datasource="your_dataSource" name="qryTable">
			select *
			from #i#
		</cfquery>
	
		<cfif qryTable.RecordCount>
			<cfset ColList = qryTable.ColumnList>
			<cffile action="write" file="#ExpandPath('.')#\#i#.csv" output="#qryTable.ColumnList#">
			<cfloop query="qryTable">
				<cfset NumCols = ListLen(qryTable.ColumnList)>
				<cfset ColData = "">
				<cfset loopColCount = 1>
				<cfloop list="#variables.ColList#" index="j">
					<cfset variables.ColData = variables.ColData & qryTable[j][qryTable.CurrentRow]>
					<cfif variables.loopColCount LT variables.NumCols>
						<cfset variables..ColData = variables.ColData & ",">
					</cfif>
				</cfloop>
				<cffile action="append" file="#ExpandPath('.')#\#i#.csv" output="#variables.ColData#">
			</cfloop>
		</cfif>
	</cfloop>
</cfoutput>

Hope this helps!

Tony