CFFILE append and null values

[aliashippysmom]

Hi all:
My program reads a MS SQL server table and uses CFFILE to make it into an excel spreadsheet so users can download it. Works fine, except for one small problem. The users want dollar amounts with no decimal points for fields which are dollar fields. Again, no problem. But for fields which contain dollar amounts, if they are blank (NULL), they want an n/a on the spreadsheet.
The following code does NOT work.

<cfquery name="getData" datasource="ddd" > 
select * from myFile 
</cfquery>

<!---Write out column headers:--->
<cffile action="write"	file="C:\Websites\files\myfile2.xls"		output= '<table border=1>
		<tr><td valign="top">ID</td>
		    <td valign="top">field1</td>
			<td valign="top">field2</td>
			<td valign="top">field3</td>
</tr>'

<cfoutput query="getData">
<cffile action="append"
		file="C:\Websites\files/myfile2.xls"
		output='	
<tr><td valign="top">#ID#</td>
<td valign="top"><cfif len(field1)>#NumberFormat(field1,"$9")#<cfelse>n/a</cfif></td>
<td valign="top">#field2#</td>
<td valign="top">#field3#</td>
</tr>'

What I get in the spreadsheet for the "field1" value is $0n/a for when there is a null in the field.

If I don't use CFFILE and just output to a TABLE, it works fine!

What am I missing here and I hope this makes sense.
Thank you in advance.

 

[cfSearching]

You are using an if/else, so only one of those conditions will execute. So I do not see how it could produce $0n/a. Unless it just appears that way due to the html.

Appending each line individually generates a lot of i/o operations. Instead try appending the results to a variable within your output loop. Then using a single <cffile action="write"> at the end.

In the mean time, dump that variable to see if it really contains $0n/a, or if it is just a problem with the html.

----------------------------------

http://cfsearching.blogspot.com/

 

[aliashippysmom]

Thanks for your reply. When I dump the variable it is a zero. That's what's so frustrating. I don't know why both results appear, like the <cfif> is missing or something. When I take off the Cffile and just look at the value in an HTML table, it's correct, and is evaluated correctly. Maybe it has something to do with the cffile. I can always preprocess outside the cffile, and that would work. Just wondering what I was doing wrong. Thanks again.

 

[cfSearching]

It is more likely it is the html or how it is interpreted. Are you saying dump the html and it is correct, then write it to a file but when you open the file it is wrong? That sounds "off".


----------------------------------

http://cfsearching.blogspot.com/

 

[cfSearching]

It is more likely it is the html or how it is interpreted.

(.. ie It is much more likely the problem lies with the generated html than some obscure cffile bug.)

----------------------------------

http://cfsearching.blogspot.com/