Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Perform validation on cfloop query

Posted on 2012-08-16
Medium Priority
Last Modified: 2012-08-16
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>
            <cfset errors = "The file was not an Excel file.">
            <cffile action="delete" file="#theFile#">
        <cfset errors = "The file was not properly uploaded.">    

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

<!--- otherwise go ahead and upload the data to your database --->
    <cfif data.recordCount is 1>
        This spreadsheet appeared to have no data.
      <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] />
			 	<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>
			<!--- 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">
	    <!--- debug. display data in table --->
		<cfquery name="getData" datasource="#yourDSN#">
				SELECT Name, Address, Zip, Phone 
				FROM   YourTable
				ORDER BY Name
	    <cfdump var="#getData#">

Open in new window

Question by:earwig75

Accepted Solution

SamuelShaw earned 2000 total points
ID: 38301488
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">
FROM data
WHERE (happydate LIKE '[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]')

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.
LVL 53

Expert Comment

ID: 38302117
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.

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses
Course of the Month11 days, 17 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question