Re: coldfusion spreadsheet leading zeroes issue
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# On Tue, Aug 16, 2011 at 4:47 PM, Brian Cain bcc9...@gmail.com wrote: 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 On Tue, Aug 16, 2011 at 3:22 PM, funand learning funandlrnn...@gmail.com wrote: 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? On Tue, Aug 16, 2011 at 2:41 PM, Brian Cain bcc9...@gmail.com wrote: 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 On Tue, Aug 16, 2011 at 12:56 PM, Brook Davies cft...@logiforms.com wrote: 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=enclient=firefox-ahs=vpOrls=org.mozilla%3A en-GB%3Aofficialbiw=1920bih=1084q=excel+truncates+leading+zerobtnG=Searc hoq=excel+truncates+leading+zeroaq=faqi=aql=gs_sm=sgs_upl=0l0l0l18408l 0l0l0l0l0l0l0l0ll0l0 Brook -Original Message- From: fun and learning [mailto:funandlrnn...@gmail.com] Sent: August-16-11 10:35 AM To: cf-talk Subject: coldfusion spreadsheet leading zeroes issue 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:346810 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
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:346811 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: coldfusion spreadsheet leading zeroes issue
Shouldn't that be cfset col1 = '#query1.col1# The ' has to be outside of the # signs. -Original Message- From: funand learning [mailto:funandlrnn...@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 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: coldfusion spreadsheet leading zeroes issue
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 On Wed, Aug 17, 2011 at 11:46 AM, DURETTE, STEVEN J (ATTASIAIT) sd1...@att.com wrote: Shouldn't that be cfset col1 = '#query1.col1# The ' has to be outside of the # signs. -Original Message- From: funand learning [mailto:funandlrnn...@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:346814 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: coldfusion spreadsheet leading zeroes issue
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=enclient=firefox-ahs=vpOrls=org.mozilla%3A en-GB%3Aofficialbiw=1920bih=1084q=excel+truncates+leading+zerobtnG=Searc hoq=excel+truncates+leading+zeroaq=faqi=aql=gs_sm=sgs_upl=0l0l0l18408l 0l0l0l0l0l0l0l0ll0l0 Brook -Original Message- From: fun and learning [mailto:funandlrnn...@gmail.com] Sent: August-16-11 10:35 AM To: cf-talk Subject: coldfusion spreadsheet leading zeroes issue 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:346790 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: coldfusion spreadsheet leading zeroes issue
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 On Tue, Aug 16, 2011 at 12:56 PM, Brook Davies cft...@logiforms.com wrote: 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=enclient=firefox-ahs=vpOrls=org.mozilla%3A en-GB%3Aofficialbiw=1920bih=1084q=excel+truncates+leading+zerobtnG=Searc hoq=excel+truncates+leading+zeroaq=faqi=aql=gs_sm=sgs_upl=0l0l0l18408l 0l0l0l0l0l0l0l0ll0l0 Brook -Original Message- From: fun and learning [mailto:funandlrnn...@gmail.com] Sent: August-16-11 10:35 AM To: cf-talk Subject: coldfusion spreadsheet leading zeroes issue 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:346791 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: coldfusion spreadsheet leading zeroes issue
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? On Tue, Aug 16, 2011 at 2:41 PM, Brian Cain bcc9...@gmail.com wrote: 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 On Tue, Aug 16, 2011 at 12:56 PM, Brook Davies cft...@logiforms.com wrote: 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=enclient=firefox-ahs=vpOrls=org.mozilla%3A en-GB%3Aofficialbiw=1920bih=1084q=excel+truncates+leading+zerobtnG=Searc hoq=excel+truncates+leading+zeroaq=faqi=aql=gs_sm=sgs_upl=0l0l0l18408l 0l0l0l0l0l0l0l0ll0l0 Brook -Original Message- From: fun and learning [mailto:funandlrnn...@gmail.com] Sent: August-16-11 10:35 AM To: cf-talk Subject: coldfusion spreadsheet leading zeroes issue 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:346797 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: coldfusion spreadsheet leading zeroes issue
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 On Tue, Aug 16, 2011 at 3:22 PM, funand learning funandlrnn...@gmail.comwrote: 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? On Tue, Aug 16, 2011 at 2:41 PM, Brian Cain bcc9...@gmail.com wrote: 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 On Tue, Aug 16, 2011 at 12:56 PM, Brook Davies cft...@logiforms.com wrote: 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=enclient=firefox-ahs=vpOrls=org.mozilla%3A en-GB%3Aofficialbiw=1920bih=1084q=excel+truncates+leading+zerobtnG=Searc hoq=excel+truncates+leading+zeroaq=faqi=aql=gs_sm=sgs_upl=0l0l0l18408l 0l0l0l0l0l0l0l0ll0l0 Brook -Original Message- From: fun and learning [mailto:funandlrnn...@gmail.com] Sent: August-16-11 10:35 AM To: cf-talk Subject: coldfusion spreadsheet leading zeroes issue 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:346803 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
