If you are on CF9, you can try using SpreadsheetFormatColumns() to format it
like you want.
something like this (I think) after your cfsrpeeadsheet tag.
<cfset spreadSheetFormatColumn(qryResult, {dataformat="mm/dd/yyy"}, 1) />
Im assuming DOB is the first column. If that isnt the case, change the last
attribute to the right column number.
.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
http://cf4em.com
-----Original Message-----
From: Hong Chen [mailto:ho...@cbmiweb.com]
Sent: Tuesday, February 07, 2012 2:42 PM
To: cf-talk
Subject: cfspreadsheet issue when reads dates from spreadsheet
We use <cfspreadsheet > to upload Excel spreadsheet into database, but
having an issue with reading dates. <cfspreadsheet> only reads last 2 digits
of the year, e.g. the date in the Excel file is 2/2/1999, the
<cfspreadsheet> reads it as 2/2/99. Below is my code:
<cfset strDir=GetDirectoryFromPath(ExpandPath("*.*")) & "/uploadFile">
<cffile action="Upload"
filefield="InputFile"
destination="#strDir#"
nameconflict="Overwrite"
mode="757">
<cfset destFileName = file.ServerDirectory & "\Book1.xls">
<cfspreadsheet action="read"
src ="#destFileName#"
columnnames = "name,dob"
query="qryResult">
<cfdump var="#qryResult#">
The query dumped out like this:
query
DOB NAME
1 2/2/99 John
Anyone can help?
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:349807
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
