Highlighted

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

Explorer ,
Dec 16, 2015

Copy link to clipboard

Copied

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>


Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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

TOPICS
Getting started

Views

1.3K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

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

Explorer ,
Dec 16, 2015

Copy link to clipboard

Copied

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>


Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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

TOPICS
Getting started

Views

1.3K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Dec 16, 2015 0
Most Valuable Participant ,
Dec 29, 2015

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 29, 2015 0
Explorer ,
Dec 30, 2015

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 30, 2015 0
Explorer ,
Dec 30, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 30, 2015 0
Most Valuable Participant ,
Dec 30, 2015

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 30, 2015 1
Explorer ,
Dec 30, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 30, 2015 0
Most Valuable Participant ,
Dec 30, 2015

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 30, 2015 0
Explorer ,
Jan 02, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 02, 2016 0
Most Valuable Participant ,
Jan 02, 2016

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 02, 2016 1
Adobe Community Professional ,
Jan 03, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 03, 2016 2
Explorer ,
Jan 04, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 04, 2016 0
Explorer ,
Jan 04, 2016

Copy link to clipboard

Copied

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
BodyDate PostedSummaryTitle
1Body12012-10-01Summary1Title1

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 04, 2016 0
Adobe Community Professional ,
Jan 04, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 04, 2016 2
Explorer ,
Jan 04, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 04, 2016 0