Skip to main content
December 17, 2015
Answered

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

  • December 17, 2015
  • 1 reply
  • 2402 views

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>


This topic has been closed for replies.
Correct answer BKBK

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.  ?


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]) />

1 reply

Carl Von Stetten
Legend
December 29, 2015

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.

December 30, 2015

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.

January 2, 2016

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.


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
BodyDate PostedSummaryTitle
1Body1 10/1/12 Summary1 Title1

Sorry

A problem has occured. Our developers have been notified.