Importing excel should allow space in a column when using brackets: The value class coldfusion.sql.QueryColumn cannot be converted to a date

[Guest]

I have followed the learncfinaweek pretty good and don't see any errors in my code.   Below is the error, that needs resolved, and the page with the code.  Any help is appreciated.  It should simply be like this: <cfset blogPost.dateposted = importData['Date Posted'] />  , which allows the column in excel to have a space in between the words.

Property : dateposted - The value class coldfusion.sql.QueryColumn cannot be converted to a date.

Root cause :org.hibernate.HibernateException: Property : dateposted - The value class coldfusion.sql.QueryColumn cannot be converted to a date

<cfimport taglib="../../customTags" prefix="ct" />

<ct:securityCheck redirectPage="#cgi.script_name#"/>

<cfparam name="form.submitted" default="0" />

<cfparam name="form.importFile" default="" />

<cfif form.submitted>

    <!--- Upload File--->

    <cffile action="upload" destination="#getTempDirectory()#" filefield="importFile" nameconflict="makeunique" />

    <!--- Read Spreadsheet --->

    <cfspreadsheet action="read" src="#cffile.serverDirectory#/#cffile.serverfile#" query="importData" headerrow="1" excludeheaderrow="true" />

    <!--- Import Data --->

    <cfloop query="importData">

        <cfset blogPost = entityNew('blogPost') />

        <cfset blogPost.title = importData.title />

        <cfset blogPost.summary = importData.summary />

        <cfset blogPost.body = importData.body />

        <cfset blogPost.dateposted = importData['Date Posted'] />

        <cfset EntitySave(blogPost) />

    </cfloop>

    <cfset ormFlush() />

        <cflocation url="listblogpost.cfm?message=#urlencodedformat('Blog Posts Imported Successfully')#" addtoken="false" />

</cfif>

<cfoutput>

    <ct:layout section="resume">

        <ct:navigation section="blog" active="post"/>

        <div class="span10">

            <h2>Upload Blog Posts</h2>

            <form class="form-horizontal" action="#cgi.script_name#" method="post" enctype="multipart/form-data">

                <div class="control-group">

                    <label class="control-label" for="importFile">Import File</label>

                    <div class="controls">

                        <input type="file" id="importFile" name="importFile" value="#form.importFile#">

                    </div>

                </div>

                <div class="control-group">

                    <div class="controls">

                        <button type="submit" class="btn btn-primary">Upload</button>

                    </div>

                </div>

                <input type="hidden" name="submitted" value="1" />

            </form>

        </div>

    </ct:layout>

</cfoutput>

[Carl Von Stetten]

Are there any values in that column of the spreadsheet that are not valid dates (and by valid, I mean valid within the range that your database supports)?  That would seem to be what the error message is talking about.

-Carl V.

[Guest]

The date in the one row is 10/1/2012, exactly like that.  It should be acceptable.  The only other row is the header.  The header was written like this, Date Posted.  And it should be allowed when the code is written like this: <cfset blogPost.dateposted = importData['Date Posted'] />

The other headers do not have two words and therefore do not have a space in them.  The code without the space is with the dot structure, for example, for the summary: <cfset blogPost.summary = importData.summary />  So i have got it to work if I put the two words together and put it in dot structure.  <cfset blogPost.dateposted = importData.dateposted />  But when I have clients that want to upload a bunch of data this way it would be better to be able to use the brackets successfully.  That way they wouldn't wonder why they have to go through every one of their XLS uploads and make everything one word.

Anymore help to get the bracket code to work is awesome.

[Guest]

Is it worth noting I am using coldfusion11 update 7 on win8 upgraded to win10?

[Carl Von Stetten]

Have you put a <cfdump var="#queryData#"> in your code to see how ColdFusion actually read in that spreadsheet, and what it did to that column name?

-Carl V.

[Guest]

Well, I am figuring out where to put the <cfdump var="#queryData#"> in the code. 

I was able to output a <cfdump var="#blogpost#"> which is where the data ends up.  Unless you can tell me something interesting, the only thing I see with this info is that the id started at 11 instead of 3 (there were only two id's ahead of it, 1 & 2).  And the following info was put in from the struct and not the brackets.  So, I am figuring out how to put in a dump queryData to show me what the brackets did.

component learncfinaweek.chapter1solution.com.entity.blogPost
PROPERTIES	id 11 title Title1 summary Summary1 body Body1 dateposted 2012-10-01 00:00:00.0 createdDateTime [empty string] modifiedDateTime [empty string] deleted [empty string] comments array [empty] categories array [empty]
METHODS

[Carl Von Stetten]

Put <cfdump var="#queryData#"> right after the <cfspreadsheet> call, so you can see how ColdFusion interpreted the data into a query.  What did it actually use for a column name for that "Date Posted" column?  That is the critical thing we need to know before we figure out what your ORM problem is.

[Guest]

This is what I got back when using <cfdump var="#importData#">  That is what you meant, Right?

Also, the order was rearranged from the xls document, which originally is Title, Summary, Body, Date Posted.

What do you think?

query
	Body	Date Posted	Summary	Title
1	Body1	10/1/12	Summary1	Title1

Sorry

A problem has occured. Our developers have been notified.

[Carl Von Stetten]

OK.  So you confirmed that ColdFusion is retrieving the data and putting it into a column named "Date Posted" (with the space).  So apparently your database (or Hibernate ORM)  doesn't like the format of the date "10/1/12" that you are passing in.  What is the underlying DBMS?  Have you confirmed that "10/1/12" is a recognized date format for that DBMS?

[Guest]

Carl,

According to this: MySQL :: MySQL 5.7 Reference Manual :: 11.3.1 The DATE, DATETIME, and TIMESTAMP Types

MySQL wants the date in the format of 'YYYY-MM-DD'

I changed the date to 'YYYY-MM-DD' in the xls file by changing to the UK format within excel.

It is still giving an error.  ?

[BKBK · Accepted solution]

DavidSCarlisle55 wrote:

I changed the date to 'YYYY-MM-DD' in the xls file by changing to the UK format within excel.

It is still giving an error.  ?

Odd. Then there can only be one solution to the puzzle: what you are reading is a column-name rather than the date value stored in the column.

See what happens when you replace that line with

<cfset blogPost.dateposted = importData['Date Posted'][currentRow] />

or, perhaps better,

<cfset blogPost.dateposted = parseDatetime(importData['Date Posted'][currentRow]) />

[Guest]

AhHA!

Bingo.  It is Working.  Both of them.  Thank You Both very much!  I am learning CF.

<cfset blogPost.dateposted = importData['Date Posted'][currentRow] />

or, perhaps better,

<cfset blogPost.dateposted = parseDatetime(importData['Date Posted'][currentRow]) />

[BKBK]

DavidSCarlisle55,

What you have here is luck in misfortune. At least, the error brings to your attention the value '10/1/12'. As a date value it is ambiguous.

Which one of the following is it?

January 10, 2012?

January 12, 2010?

October 1, 2012?

January 10, 1912?

January 12, 1910?

October 1, 1912?

In fact, in your last but one post the date actually starts with the year: 2012-10-01.

It appears that the datatype of the column datePosted in the blogPost table is timestamp. But the tag <cfspreadsheet action="read"> seems to generate a query, importData, in which the date is stored in the Excel format mm/dd/yy.

To avoid the confusion, you should convert the date into the appropriate format (timestamp) before storing it in the blogPost table. You could, for example, replace the line

   <cfset blogPost.dateposted = importData['Date Posted'] />

with something like

   <cfset blogPost.dateposted = parseDatetime(importData['Date Posted']) />

[Guest]

BKBK,

I put in the parseDateTime like this: <cfset blogPost.dateposted = parseDatetime(importData['Date Posted']) /> .

It is still giving an error.  So, I tried it with different date formats as well.  And am still getting the error message even with the UK format 'YYYY-MM-DD'.

query
	Body	Date Posted	Summary	Title
1	Body1	2012-10-01	Summary1	Title1

Sorry

  A problem has occured. Our developers have been notified

Also, here is a portion of cfdump from the bloglist showing that the date did show up with the UK format and it is showing the time as zeros.

dateposted 2012-10-01 00:00:00.0