One of the disappointing things about how CF handles lists is that an empty element is ignored. So if one of your list items is ',,' which, effectively, is null. Unfortunately, since it ignores the element it screws up exactly what you want to do below.
How I've handled this in the past is that when I read in my text file, if I know for sure that this won't screw it up, I do a : <cfset epafile = Replace(epafile,",,",",#chr(xxx)#,","all") > (xxx should be a character value that you know does not exist in your input file, like a hidden character) Then, in your insert statement, you use something like this for your values: <cfqueryparam value="#listgetAt(line,1,chr(9))#" cfsqltype="cf_sql_varchar" null="#listgetAt(line,1,chr(9)) EQ char(xxx)#"> (again, xxx is same character you used above). This is for sure a 'hack', and there are probably better ways to handle it, but it worked for me before, so I thought I'd share. Of course, you have to know that there are no other double commas in the input file (like inside of a quoted string). If that is going to happen, then this approach won't work. However, you could probably do something similar with a regular expression, although I'm no expert in regular expressions. So, I don't know if this will help or not, but it might be an option for you... By the way, you may have noticed I eliminated a lot of # signs and Quotes that you had in your insert statement. They are unnecessary and just slow down processing. You do not need to use pound signs around a variable when that variable is being referenced inside of a coldfusion function unless you are 'evaluating' the variable. Now that I think about it, I think I came across a function somewhere called 'ListFix'. Do a google on +coldfusion +listfix. That might be a better solution. I think it's at cflib.org. Dave -----Original Message----- From: Imperial, Robert [mailto:[email protected]] Sent: Friday, February 13, 2009 11:59 AM To: cf-newbie Subject: RE: looping a text file for an insert? Hi again ;) I've run into a stumbling block the my insert bombing out due to some null fields and am not sure how to handle or account for this. Any suggestions as to how to handle these without breaking the insert? Here's what I have thus far but of course it hangs when it reaches a row without the full 18 elements in it. <cfset record = 0 > <cffile action="read" file="C:\Inetpub\wwwroot\www\testing\EPARank.txt" attributes="readonly" variable="epafile" /> <cfloop index="line" list="#epafile#" delimiters="#chr(10)##chr(13)#"> <cfset record = record + 1 /> <cfif record neq 1 > <cfquery name="insertNewTest" datasource="#dsdata#"> INSER INTO epadatatest ( PID, First_Name, Middle_Name, Last_Name, Appointing_Dept, Primary_Appt_Ind, Primary_Rank_Ind, Rank_Dept, Rank_Title, Rank_Scheduled_End_Dt, Joint_Indicator, Employee_Email, Department_Chair1, Chair_Email1, Department_Chair2, Chair_Email2, HR_Facilitator, HR_Facilitator_Email ) VALUES ( '#listgetAt('#line#',1, '#chr(9)#')#', '#listgetAt('#line#',2, '#chr(9)#')#', '#listgetAt('#line#',3, '#chr(9)#')#', '#listgetAt('#line#',4, '#chr(9)#')#', '#listgetat('#line#',5, '#chr(9)#')#', '#listgetat('#line#',6, '#chr(9)#')#', '#listgetat('#line#',7, '#chr(9)#')#', '#listgetat('#line#',8, '#chr(9)#')#', '#listgetat('#line#',9, '#chr(9)#')#', '#listgetat('#line#',10, '#chr(9)#')#', '#listgetat('#line#',11, '#chr(9)#')#', '#listgetat('#line#',12, '#chr(9)#')#', '#listgetat('#line#',13, '#chr(9)#')#', '#listgetat('#line#',14, '#chr(9)#')#', '#listgetat('#line#',15, '#chr(9)#')#', '#listgetat('#line#',16, '#chr(9)#')#', '#listgetat('#line#',17, '#chr(9)#')#', '#listgetat('#line#',18, '#chr(9)#')#' ) </cfquery> </cfif> </cfloop> TIA! Bob -----Original Message----- From: Paul Kukiel [mailto:[email protected]] Sent: Friday, February 13, 2009 10:58 AM To: cf-newbie Subject: RE: looping a text file for an insert? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4365 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
