Skip to main content
Known Participant
August 11, 2012
Answered

CFspreadsheet issue

  • August 11, 2012
  • 2 replies
  • 1931 views

Hello, I have a question about CFspreadsheet....

I'm reading a spreadsheet and inserting the data into  a SQL server  DB....everything works fine unless 1 of my columns in the spreadsheet is blank. I'm getting the following error:

Element PHONE is undefined in EXCELQUERYBYNAME.

I tried the put a cfif isdefined to see if that would solve it but no luck....same error message. How can I handle this issue? My cfquery is below:

<cfquery name="insert_Sh_data" datasource="#datasource#">

    INSERT into contactrecord

       (

       contact_date,

       fname,

       lname,

       address,

       city,

       state,

       zip,

       county,

       <cfif isDefined(excelQueryByName.phone)>phone,</cfif>

       <cfif isDefined(excelQueryByName.cell)>cell,</cfif>

       <cfif isDefined(excelQueryByName.fax)>fax,</cfif>

       <cfif isDefined(excelQueryByName.title)>title,</cfif>

       <cfif isDefined(excelQueryByName.organization)>organization,</cfif>

       <cfif isDefined(excelQueryByName.email)>email,</cfif>

       <cfif isDefined(excelQueryByName.general_comments)>general_comments,</cfif>

       <cfif isDefined(excelQueryByName.fname2)>fname2,</cfif>

       <cfif isDefined(excelQueryByName.lname2)>lname2,</cfif>

        mailing,

        input_by,

        input_date,

        input_time,

        input_by_id,

        country,

        sh_rating

        ) 

                          

        Values(              

        '#Trim(excelQueryByName.contact_date)#',

        '#Trim(excelQueryByName.fname)#',

        '#Trim(excelQueryByName.lname)#',

        '#Trim(excelQueryByName.address)#',

        'Trim(excelQueryByName.city)#',

        '#Trim(excelQueryByName.state)#',

        '#Trim(excelQueryByName.zip)#',

        '#Trim(excelQueryByName.county)#',

        <cfif isDefined(excelQueryByName.phone)>'#Trim(excelQueryByName.phone)#',</cfif>

        <cfif isDefined(excelQueryByName.cell)>'#Trim(excelQueryByName.cell)#',</cfif>

         <cfif isDefined(excelQueryByName.fax)>'#Trim(excelQueryByName.fax)#',</cfif>

         

         <cfif isDefined(excelQueryByName.title)>'#Trim(excelQueryByName.title)#',</cfif>

         <cfif isDefined(excelQueryByName.organization)>'#Trim(excelQueryByName.organization)#',</cfif>

         <cfif isDefined(excelQueryByName.email)>'#Trim(excelQueryByName.email)#',</cfif>

         <cfif isDefined(excelQueryByName.general_comments)>'#Trim(excelQueryByName.general_comments)#',</cfif>

         <cfif isDefined(excelQueryByName.fname2)>'#Trim(excelQueryByName.fname2)#',</cfif>

         <cfif isDefined(excelQueryByName.lname2)>'#Trim(excelQueryByName.lname2)#',</cfif>

         '#Trim(excelQueryByName.mailing)#',

         '#Trim(excelQueryByName.input_by)#',

         '#Trim(excelQueryByName.input_date)#',

         '#Trim(excelQueryByName.input_time)#',

         '#Trim(excelQueryByName.input_by_id)#',

         '#Trim(excelQueryByName.country)#',

         '#Trim(excelQueryByName.sh_rating)#'

         )

</cfquery>

Thanks in Advance.

    This topic has been closed for replies.
    Correct answer

    The error "Element PHONE is undefined in EXCELQUERYBYNAME." indicates that the 'phone' column doesn't exist in the query object. Which I guess means the phone column doesn't exist in the Excel spreadsheet.

    What do you see when you dump EXCELQUERYBYNAME? Can you see the phone column?

    In theory you could use:

    <cfif StructKeyExists(EXCELQUERYBYNAME, "phone")>phone,</cfif>

    Even if the object isn't actually a Struct.

    2 replies

    Inspiring
    August 16, 2012

    You need to put your parameter in quotes when using isDefined, like so:

    cfif isDefined("excelQueryByName.phone")>phone,</cfif>

    Inspiring
    August 11, 2012

    Instead of checking to see if the field is defined, check to see if the length is greater than 0.

    pmlink360Author
    Known Participant
    August 11, 2012

    I changed it to

    <cfif len(excelQueryByName.phone) GT 0>phone,</cfif>

    same error....

    Correct answer
    August 13, 2012

    The error "Element PHONE is undefined in EXCELQUERYBYNAME." indicates that the 'phone' column doesn't exist in the query object. Which I guess means the phone column doesn't exist in the Excel spreadsheet.

    What do you see when you dump EXCELQUERYBYNAME? Can you see the phone column?

    In theory you could use:

    <cfif StructKeyExists(EXCELQUERYBYNAME, "phone")>phone,</cfif>

    Even if the object isn't actually a Struct.