coldfusion spreadsheet leading zeroes issue
hofar...@houseoffusion.com
Hi All -
I am facing an issue with coldfusion9 spreadsheet functions. It cuts of leading zeroes. This is what I am trying. I am getting array of data and using spreadsheetaddrows. When I am constructing an array, am using quotes so as to preserve the zeroes but with no effect. Adding some characters like "~" helps. But when I am adding space, coldfusion is not preserving the spaces. Is there a better way to handle this issue?
Thanks
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346789
hofar...@houseoffusion.com
This is an excel issue, not a CF issue. If you set the data type of the
column to text it does not happen..
http://www.google.ca/search?hl=en&client=firefox-a&hs=vpO&rls=org.mozilla%3A
en-GB%3Aofficial&biw=1920&bih=1084&q=excel+truncates+leading+zero&btnG=Searc
h&oq=excel+truncates+leading+zero&aq=f&aqi=&aql=&gs_sm=s&gs_upl=0l0l0l18408l
0l0l0l0l0l0l0l0ll0l0
Brook
Hi All -
Thanks
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346790
hofar...@houseoffusion.com
You could also try using a single quote as your leading character. This is
how would would enter it in excel without changing the column formatting.
BC
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346791
hofar...@houseoffusion.com
If I use a single quote as leading character, then thta single quote would
appear in excel sheet right? At what point should I remove the quote? Can
anything be done on database side. I mean can the query be formatted in any
way?
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346797
hofar...@houseoffusion.com
The single quote does not appear. It is a special formatting rule for
excel. It makes the column display as a character field by default. It is
used specifically to handle leading zeros.
Brian Cain
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346803
hofar...@houseoffusion.com
Brain,
I tried using a single quote within coldfusion before sending it to excel.
Excel still displays the quote. This is what I am doing
<cfset col1 = #'query1.col1#>
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346810
hofar...@houseoffusion.com
I tried using a single quote within coldfusion before sending it to excel.
Excel still displays the quote. This is what I am doing
<cfset col1 = "#'query1.col1#">
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346811
hofar...@houseoffusion.com
Shouldn't that be <cfset col1 = "'#query1.col1#"> The ' has to be outside of the # signs.
-----Original Message-----
From: funand learning [mailto:funand...@gmail.com]
Sent: Wednesday, August 17, 2011 11:26 AM
To: cf-talk
Subject: Re: coldfusion spreadsheet leading zeroes issue
I tried using a single quote within coldfusion before sending it to excel.
Excel still displays the quote. This is what I am doing
<cfset col1 = "#'query1.col1#">
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346813
hofar...@houseoffusion.com
I know that it works within excel. Not sure if there is something else
going on when creating a spreadsheet. I imported a tab delimited file that
used the single quote preceding the zeros, and excel did not treat it the
same way as when you use a single quote using excel directly. Sorry that
didn't work.
BTW, Steven has the right string format for the CF syntax.
BC
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346814