Perform validation on cfloop query

Posted on 2012-08-16
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 =[currentRow] />
                                                     <cfset = data.happydate[currentRow] />
		  	<cfset variables.address = data.address[currentRow] />
		  	<cfset =[currentRow] />
		  	<cfset =[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="" cfsqltype="cf_sql_varchar">
					, <cfqueryparam value="#variables.address#" cfsqltype="cf_sql_varchar">
					, <cfqueryparam value="" cfsqltype="cf_sql_varchar">
					, <cfqueryparam value="" 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
    LVL 2

    Accepted Solution

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

    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 51

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them ( . I did keep the main js functions but made sever…
    I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
    This video discusses moving either the default database or any database to a new volume.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now