CODEWITHSUNDEEP
excelcoldfusioncoldfusion-9
dknighton
dknighton

Reputation: 85

Importing Excel with Coldfusion - Receiving strange date format mm\/dd\/yyyy

I am using ColdFusion 9 to import data from an Excel (2007, xls) spreadsheet. One of the columns being imported is a date field. In most cases, the date comes across fine, however in some instances it appears that something strange is happening with the date format. It comes across as mm\/dd\/yyyy. I don't know what is adding those additional \'s. The specific error is obvious: 

"11\/15\/2012 is an invalid date or time string."

Looking directly at the spreadsheet, and within the cells themselves, the date appears formatted correctly as mm/dd/yyyy.

Does anyone know what might be causing this?

Follow-Up

Thanks for the helpful answers. In the end, the best solution was from CoderSeven. I used Replace(dateString, '\', '', 'all') to remove the offending slash and process the data through. This proved to be the best solution because I am processing spreadsheets from potentially tens of thousands of individuals, and while I control the format of the spreadsheet they use, I cannot control whether they type or cut-and-paste the data into the fields. Ultimately I would love to know where that extra slash came from, but this solution worked well.

Upvotes: 2

Views: 718

Answers (2)

mawburn
mawburn

Reputation: 2346

This isn't really an answer as to why this is happening, but it is a work-around.

Just use Replace(yourdate, '\', '').

Personally, I tend to take the stance of:

If a MS product does something weird, the best solution is to just make sure you can handle that weirdness instead of trying to figure out where that weirdness is coming from.

Not that MS products are bad, it just usually saves you alot of time an effort.

Upvotes: 1

Stepan1010
Stepan1010

Reputation: 3136

"11\/15\/2012 is an invalid date or time string." One problem might be that the dates on your spreadsheet are not strings they are excel time values - which are basically integers. Perhaps converting these cells to a text format using: =TEXT(C7,"mm/dd/yyyy")

might solve the problem. Just to be safe I would change to format to text as well. Good Luck.

Upvotes: 0

Related Questions