cfspreadsheet issue when reads dates from spreadsheet
hofar...@houseoffusion.com
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:349804
hofar...@houseoffusion.com
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
Anyone can help?
Thanks!
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349807
hofar...@houseoffusion.com
> something like this (I think) after your cfsrpeeadsheet tag.
> <cfset spreadSheetFormatColumn(qryResult, {dataformat="mm/dd/yyy"}, 1) />
�
Yep, essentially. But it needs to be applied before reading the sheet into a query.
-Leig
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:349809
hofar...@houseoffusion.com
Not sure why I was thinking after.
.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
http://cf4em.com
-----Original Message-----
From: Leigh [mailto:cfsea...@yahoo.com]
Sent: Tuesday, February 07, 2012 5:42 PM
To: cf-talk
-Leig
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349811
hofar...@houseoffusion.com
I also have a download function, in there I have formatted the column with
spreadSheetFormatColumn. If I use the downloaded file to upload, it reads 4
digits year. But if I create a new spreadsheet to upload. It only reads the
last 2 digits of year.
-Leig
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349812
hofar...@houseoffusion.com
Sounds like the same solution in both cases unless I'm missing something.
-Leig
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349813
hofar...@houseoffusion.com
Yeah, the solution is the same. You have to apply a format.
> I also have a download function, in there I have formatted the column with
> spreadSheetFormatColumn. If I use the downloaded file to upload, it reads
> 4 digits year.
> But if I create a new spreadsheet to upload.
> It only reads the last 2 digits of year.
From what I have read on the POI lists, if you do not *explicitly* set a format for the cells (either manually or programatically with CF), Excel assigns its default. For dates it is "m/d/yy". That pattern, is what CF uses to format the query values. So with your downloaded file, it uses whatever format you applied. Whereas with your brand new worksheet, there is no format, so it is falls back on the default "m/d/yy".
�
-Leig
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:349815
hofar...@houseoffusion.com
If I manually format the column, it still reads yy. How can I
programmatically format it before it reads the spreadsheet?
Thanks,
Hong
-----Original Message-----
From: Leigh [mailto:cfsea...@yahoo.com]
Sent: Tuesday, February 07, 2012 8:20 PM
To: cf-talk
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349816
hofar...@houseoffusion.com
> If I manually format the column, it still reads yy.
I am betting Excel is still registering the cell format as "m/d/yy".� Try using "m/d/yyyy;@"
> How can I programmatically format it before it reads the spreadsheet?
I do not think you can convert a spreadsheet object into a query directly. If not, use SpreadSheetRead to load the spreadsheet into memory.� Format the column. Then write it back to disk.
�
-Lei
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:349818
hofar...@houseoffusion.com
It worked after I applied date format for the column manually, will try to
use SpreadSheetRead tomorrow. Thanks.
-----Original Message-----
From: Leigh [mailto:cfsea...@yahoo.com]
Sent: Tuesday, February 07, 2012 9:11 PM
To: cf-talk
Subject: Re: [5] RE: cfspreadsheet issue when reads dates from spreadsheet
�
-Lei
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349819
hofar...@houseoffusion.com
I used SpreadSheetRead to read the spreadsheet into memory and used
SpreadsheetFormatColumns to format the dob column, then used cfspreadsheet
to write a new spreadsheet, last step, I read the new spreadsheet into a
query to insert to DB. It was successful, but hope there is a simple way to
do it. Thanks.
-----Original Message-----
From: Leigh [mailto:cfsea...@yahoo.com]
Sent: Tuesday, February 07, 2012 9:11 PM
To: cf-talk
Subject: Re: [5] RE: cfspreadsheet issue when reads dates from spreadsheet
�
-Lei
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349826
hofar...@houseoffusion.com
> It was successful, but hope there is a simple way to do it.
Unfortunately, I think that is as simple as it gets. AFAIK, CF does not let you specify the formatting of query values. It always uses the cell format information.�
�
-Lei
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:349829
hofar...@houseoffusion.com
I was thinking there were extra read and write action... Anyway my problem
resolved now. Thanks for your help.
-----Original Message-----
From: Leigh [mailto:cfsea...@yahoo.com]
Sent: Wednesday, February 08, 2012 11:56 AM
To: cf-talk
Subject: Re: [5] RE: cfspreadsheet issue when reads dates from spreadsheet
�
-Lei
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349831
hofar...@houseoffusion.com
The only thing I might add is that if you are on CF9, you might consider
using the new in-memory virtual file system (VFS) to write your new
spreadsheet and read it back in (might improve the performance if the
spreadsheet is large).
-Carl
On 2/8/2012 8:34 AM, Hong Chen wrote:
> I used SpreadSheetRead to read the spreadsheet into memory and used
> SpreadsheetFormatColumns to format the dob column, then used cfspreadsheet
> to write a new spreadsheet, last step, I read the new spreadsheet into a
> query to insert to DB. It was successful, but hope there is a simple way to
> do it. 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:349852