How to query through an excel document column using cfspreadsheet in ColdFusion?

Question

I need to do some validation on an excel spreadsheet before I upload to database.

How do I query through an excel document column using cfspreadsheet in ColdFusion?

I need to verify that specific columns don't have any null values in any of their rows. I have the number of rows in a cold Fusion variable and the number of columns will always be the same.

Thus far, I've been able to figure out the following (below) about cfspreadsheet.

I could put the code below inside a <cfloop> and increment the column and rows with cfif statements to skip the columns I don't want to check. The loop would end with the total numberOfRows CF variable that I have, but I imagine there's a better way to do this using a query.

Can I do this in a query? If so, how?

<cfspreadsheet
  action="read" 
  format = "html"
  src="#SESSION.theFile#"
  name= "test"
  rows = "3"
  columns = "6"
  >


<cfoutput>
   #test#
</cfoutput>

CFSpreadsheet can return the results in several format. To work with a query object, you need to use the query attribute, not format=html. Once you have a query, you can filter it using a QoQ. — Leigh
– Leigh, Commented Feb 15, 2014 at 2:57
Once I have used the query attribute, how do I execute a query on that query variable? — JoeTavarez
– JoeTavarez, Commented Feb 15, 2014 at 20:13
Use a QoQ. See the previous link. Though cfdump the query variable first to see and understand the query columns you can access. — Leigh
– Leigh, Commented Feb 15, 2014 at 20:16
@Leigh please place this as an answer, so I can mark it answered. — JoeTavarez
– JoeTavarez, Commented Feb 15, 2014 at 20:50

Leigh · Accepted Answer · 2014-02-15 21:53:59Z

1

(From comments...)

CFSpreadsheet can return the results in several format. To work with a query object, you need to use the query attribute, not format=html. Once you have a query, you can filter it using a QoQ. CFDump the query variable first to see and understand the query columns you can access.

answered Feb 15, 2014 at 21:53

Leigh

28.9k10 gold badges58 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

James A Mohler · Accepted Answer · 2014-02-15 00:50:12Z

0

You can do things like

<cfspreadsheet action="read" src="#theFile#" sheetname="courses" 
    name="spreadsheetData"> 
<cfdump var="#speadsheetData#">

Adapted from http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec17cba-7f87.html

answered Feb 15, 2014 at 0:50

James A Mohler

11.1k15 gold badges51 silver badges76 bronze badges

Collectives™ on Stack Overflow

How to query through an excel document column using cfspreadsheet in ColdFusion?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related