Formatting Excel Output Properly

[tturner33]

I am trying to export data from a SQL table into an Excel spread sheet and everything works great.... except my job numbers (field JobNum) is coming into Excel as a Date. For example, JobNum 03-4009 comes in as 03/01/4009. If I try to reformat it in Excel, it gives me the number 770358. The data type in the SQL table is char, not sure why Excel is not seeing it as text. Any suggestions are greatly appreciated. My code is below. Thanks.

-Tom

<cfset crlf = #chr(13)# & #chr(10)#>

	<CFQUERY NAME="m_qryListJobs" DATASOURCE="#Application.DSN#">
				SELECT     	JobList.JobID AS JobID, 
				    JobList.JobNum AS JobNum, 
					JobList.ClientNum AS ClientNum, 
					JobList.ClientName AS ClientName, 
					JobList.ProjectDescription AS ProjectDescription, 
					JobList.ProfileCode AS ProfileCode, 
					JobList.Active AS Active, 
					Tasks.TaskID AS TaskID, 
					Tasks.Task AS Task, 
					Tasks.Description AS Description, 
					Tasks.EstComplete AS EstComplete, 
					Tasks.Phase AS Phase, 
					Tasks.Manager AS Manager, 
					JobList.EstComplete AS JobEstComplete, 
					JobList.Phase AS JobPhase, 
					JobList.Manager AS JobManager, 
					Tasks.Active AS TaskActive, 
					JobList.ProposalNum AS jlProposalNum, 
					Tasks.ProposalNum AS tProposalNum, 
					JobList.PercentComplete AS JobComplete, 
					Tasks.PercentComplete AS TaskComplete
				FROM         
					JobList INNER JOIN Tasks ON JobList.JobNum = Tasks.JobID
		<CFIF #IsDefined("mActiveJobs")#>
		WHERE JobList.Active = 1
		</CFIF>

		<CFIF #IsDefined('mSort')#>
			ORDER BY #mSort#, JobNum, Task
		<CFELSE>
			ORDER BY JobNum, Task
		</CFIF>
	</CFQUERY>

<CFCONTENT type="application/msexcel" reset="yes">
<CFHEADER name="Content-Disposition" value="attachment; filename=ProjectExport.xls">
 	<CFIF #IsDefined("mActiveJobs")#>
		<CFOUTPUT>Project#Chr(9)#Client Name#Chr(9)#Project Description#Chr(9)#Phase#Chr(9)#Due Date#Chr(9)#% C#Chr(9)#Mgr#Chr(9)#P ###Chr(9)##crlf#</CFOUTPUT>
		<CFOUTPUT QUERY="m_qryListJobs"  GROUP="JobNum">		
		#JobNum##Chr(9)##ClientName##Chr(9)##ProjectDescription##Chr(9)##JobPhase##Chr(9)##DateFormat(JobEstComplete, "mm/dd/yyyy")##Chr(9)##JobComplete##Chr(9)##JobManager#<cfif #jlProposalNum# IS NOT 0>#Chr(9)##jlProposalNum#</cfif><CFIF #Task# GT 0><CFOUTPUT><CFIF #TaskActive# IS NOT 0>#crlf##Chr(9)#Task #Task##Chr(9)##Description##Chr(9)##Chr(9)##DateFormat(EstComplete, "mm/dd/yyyy")##Chr(9)##TaskComplete##Chr(9)##Manager#<cfif #tProposalNum# IS NOT 0>#Chr(9)##tProposalNum#</cfif></CFIF></CFOUTPUT></CFIF>#crlf#
		</CFOUTPUT>
<cfelse><CFOUTPUT>Client ###Chr(9)#Project ###Chr(9)#Client Name#Chr(9)#Project Description</CFOUTPUT>
<CFOUTPUT QUERY="m_qryListJobs">#ClientNum##Chr(9)##JobNum##Chr(9)##ClientName##Chr(9)##ProjectDescription##crlf#</CFOUTPUT>
</cfif>

 

[TruthInSatire]

have you tried adding a single quote to the beginning?

'03-4009 should force excel to see it as a text field not date.

 

[tturner33]

I have read about using the single quote but not sure where to insert it? If I insert it before the # signs, it throws an error. If I insert it after the # signs, it displays the data with an ' in front of it. I'm getting closer! Thanks for the help.

<CFOUTPUT QUERY="m_qryListJobs">#ClientNum##Chr(9)##JobNum##Chr(9)##ClientName##Chr(9)##ProjectDescription##crlf#</CFOUTPUT>

 

[TruthInSatire]

what error do you get when you do #Chr(9)#'#JobNum#