CODEWITHSUNDEEP
coldfusion-10import-from-excel
Pradeep Subramani
Pradeep Subramani

Reputation: 29

Date format issue while uploading and saving the excel sheet into db using coldfusion cfspreadsheet

I uploaded and parsed through an excel sheet using cfspreadsheet and inserted all the values into database and the problem is that [my excel sheet has a date range from 1/2/2016 to 29/02/2016,from 1/2/16 to 12/2/2016 it is right aligned in excelsheet and remaining is left aligned...]. The value is being inserted into the db but I cannot able to fetch the values from 1-12 using where condition in db. I tried manually changing the date format in my excel sheet..what happened is

..02 January 2016
02 February 2016
.
.
02 December 2016
13/02/2016
14/02/2016
.
.

up to 02/12/2016[mm/dd/yy] it is displaying dd as month notation and from 13/02/2016[dd/mm/yyy] it is displaying properly, I tried with all formatting options, and I need to implement it in code. I think while downloading the data itself the date is not getting formatted properly, I tried with different excel sheet but of no use...and I tried converting these dates into specific format using coldfusion code and but it doesn't works for me..

<cfset sheet = spreadSheetRead("D:\file123\final1231.xls")>
<cfset SpreadSheetFormatColumn(sheet,{dataFormat="yyyy-mm-dd"},3)>
<cfset SpreadSheetWrite(sheet,"D:\file123\final1232",true)>

can any one help me in this?

Upvotes: 0

Views: 531

Answers (1)

Viv
Viv

Reputation: 326

What looks to me is that locale is set to 'English (United States)' in your excel and that is why for first 12 rows your excel is treating values as valid date but starting 13th row the data is invalid and hence excel is treating it as string (hence left aligned). Try manually changing excel locale to 'English (India)' or similar and you will see data aligned properly.

Also Cold Fusion internal date-time format is American (mm/dd/yyyy) and by default will interpret date and time into American format. I think while inserting data you can create date with something like below to make sure what data gets inserted:

#createDate(listLast(dt,"/"),listFirst(listRest(dt,"/"),"/"),listFirst(dt,"/"))#

Here dt is the data column that you want to insert. You may need to put some checks, based on the data, to avoid any run time errors.

Upvotes: 0

Related Questions