• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

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


TOPICS
Getting started

Views

1.8K

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
community guidelines

correct answers 1 Correct answer

Community Expert , Jan 04, 2016 Jan 04, 2016

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'][

...

Votes

Translate

Translate
Guide ,
Dec 29, 2015 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.

Votes

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
community guidelines
Explorer ,
Dec 30, 2015 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.

Votes

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
community guidelines
Explorer ,
Dec 30, 2015 Dec 30, 2015

Copy link to clipboard

Copied

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

Votes

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
community guidelines
Guide ,
Dec 30, 2015 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.

Votes

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
community guidelines
Explorer ,
Dec 30, 2015 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

Votes

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
community guidelines
Guide ,
Dec 30, 2015 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.

Votes

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
community guidelines
Explorer ,
Jan 02, 2016 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.

Votes

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
community guidelines
Guide ,
Jan 02, 2016 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?

Votes

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
community guidelines
Explorer ,
Jan 04, 2016 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.  ?

Votes

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
community guidelines
Community Expert ,
Jan 04, 2016 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]) />

Votes

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
community guidelines
Explorer ,
Jan 04, 2016 Jan 04, 2016

Copy link to clipboard

Copied

LATEST

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

Votes

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
community guidelines
Community Expert ,
Jan 03, 2016 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']) />

Votes

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
community guidelines
Explorer ,
Jan 04, 2016 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

Votes

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
community guidelines
Resources
Documentation