Perform validation on cfloop query

I am using a method similar to the one below that allows someone to upload a spreadsheet and then write the data from that spreadsheet to a table.

I'd like to perform some validation on the spreadsheet before it actually writes any data to the table but since it is in a loop it writes the data before it hits a problem. I'd like to validate everything BEFORE anything is written to the table. My comments in the code below explain where I have the validation now... but again it isn't working properly. Could someone assist?

<cfset showForm = true>
<cfif structKeyExists(form, "xlsfile") and len(form.xlsfile)>

    <!--- Destination outside of web root --->
    <cfset dest = getTempDirectory()>

    <cffile action="upload" destination="#dest#" filefield="xlsfile" result="upload" nameconflict="makeunique">

    <cfif upload.fileWasSaved>
        <cfset theFile = upload.serverDirectory & "/" & upload.serverFile>
        <cfif isSpreadsheetFile(theFile)>
            <cfspreadsheet action="read" src="#theFile#" query="data" headerrow="1">
            <cffile action="delete" file="#theFile#">
            <cfset showForm = false>
        <cfelse>
            <cfset errors = "The file was not an Excel file.">
            <cffile action="delete" file="#theFile#">
        </cfif>
    <cfelse>
        <cfset errors = "The file was not properly uploaded.">    
    </cfif>
        
</cfif>

<cfif showForm>
    <cfif structKeyExists(variables, "errors")>
        <cfoutput>
        <p>
        <b>Error: #variables.errors#</b>
        </p>
        </cfoutput>
    </cfif>
    
    <form action="test.cfm" enctype="multipart/form-data" method="post">
        
         <input type="file" name="xlsfile" required>
         <input type="submit" value="Upload XLS File">
        
    </form>

<!--- otherwise go ahead and upload the data to your database --->
<cfelse>
    <cfif data.recordCount is 1>
        <p>
        This spreadsheet appeared to have no data.
        </p>
    <cfelse>
      <cfloop query="data" startRow="2">
		  	<!--- extract values from each column --->
		  	<cfset variables.name = data.name[currentRow] />
                                                     <cfset variables.name = data.happydate[currentRow] />
		  	<cfset variables.address = data.address[currentRow] />
		  	<cfset variables.zip = data.zip[currentRow] />
		  	<cfset variables.phone = data.phone[currentRow] />
		
	<!--- MY VALIDATION IS BELOW HERE BELOW IS AN EXAMPLE --->
			 	<CFIF IsDate(variables.happydate) IS "No">
		 		<div class="errortext">
<p>ERROR: One of your dates is not correct.</p>
 <p>Please enter your dates in the format MM/DD/YY </p>
		 		</div>
<!--- MY VALIDATION IS ABOVE HERE ABOVE IS AN EXAMPLE --->
	
			<!--- insert values into db table ---->
			<cfquery name="addRow" datasource="#yourDSN#">
				INSERT INTO YourTable ( Name, Address, Zip, Phone )
				VALUES (
					<cfqueryparam value="#variables.name#" cfsqltype="cf_sql_varchar">
					, <cfqueryparam value="#variables.address#" cfsqltype="cf_sql_varchar">
					, <cfqueryparam value="#variables.zip#" cfsqltype="cf_sql_varchar">
					, <cfqueryparam value="#variables.phone#" cfsqltype="cf_sql_varchar">
				)
    		</cfquery>
    	</cfloop>
	
	    <!--- debug. display data in table --->
		<cfquery name="getData" datasource="#yourDSN#">
				SELECT Name, Address, Zip, Phone 
				FROM   YourTable
				ORDER BY Name
    	</cfquery>
	    <cfdump var="#getData#">
	 </cfif>
</cfif>

Open in new window

earwig75Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SamuelShawCommented:
You could try a query of queries to perform a check on the date field. The other way would be to loop through all the information before inserting and have a boolean variable that tracks whether or not everything is correct.
<cfquery dbtype="query" name="invalid_rows">
SELECT name
FROM data
WHERE (happydate LIKE '[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]')
</cfquery>

Open in new window

Obviously, that isn't a full check to see if the date is valid, but it at least checks to see if there is something that looks like a date in the field. Of course, you may have to alter the LIKE expression depending on the format of the date as it comes over from cfspreadsheet.
<cfset is_valid = true>
<cfloop query="data"><cfset is_valid = is_valid AND IsDate("happydate")></cfloop>

Open in new window

This is the other method. It may be a little more time-consuming, but it's more robust. If is_valid is false, display an error; otherwise, insert your information.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_agx_Commented:
Either of those should work fine for moderate sized imports.

IF you're comfortable w/sql, another approach I've used is to load everything into a "staging" table first, with a unique session ID. I then validate all records at once using sql.  If everything's valid, I do a single INSERT/SELECT to transfer the data into the main table.  Then delete the temp data from the staging table.  The advantage of this approach is it offers complete import control. You can also use the staging table to generate a detailed error report about problem records.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.