Solved

Coldfusion insert script from an external text file

Posted on 2007-04-03
11
274 Views
Last Modified: 2013-12-24
Here is my problem and I cant seem to figure out why this is happening.  I had this all working but the person that maintains my text file made some changes and took out field number 8 so I had to modify my code:

Here is my code:

<CFFILE ACTION="READ" FILE="#Path#" VARIABLE="datavar">


<CFSET crlf = "~">
<CFLOOP INDEX="orec" LIST="#datavar#" DELIMITERS="#crlf#">

<cfif len(trim(orec)) >

<cfquery name="getjobnumberInfo" datasource="TEST" dbtype="TEST">
      select max(jid) as NaxNum from Jobs
</cfquery>




<CFSCRIPT>
         Jobnumber = ListGetAt(orec, 1,"|");
         Jobtitle = ListGetAt(orec, 2,"|");
         Location = ListGetAt(orec, 3,"|");
         State = ListGetAt(orec, 4,"|");
         Company = ListGetAt(orec, 5,"|");
         Jobfield2 = ListGetAt(orec, 6,"|");
         Website = ListGetAt(orec, 7,"|");
</CFSCRIPT>

HERE IS THE FILE IT COMES FROM:

10000|POSITION|CITY|STATE|COMPANY|JOBFIELD2|URL ~


ERROR IM GETTING:

The web site you are accessing has experienced an unexpected error.
Please contact the website administrator.

The following information is meant for the website developer for debugging purposes.  

Error Occurred While Processing Request  
Invalid list index 7.  
In function ListGetAt(list, index [, delimiters]), the value of index, 7, is not a valid as the first argument (this list has 6 elements). Valid indexes are in the range 1 through the number of elements in the list.  
 
The error occurred in D:\get_jobs.cfm: line 38
 
36 :          Company = ListGetAt(orec, 5,"|");
37 :          Jobfield2 = ListGetAt(orec, 6,"|");
38 :          Website = ListGetAt(orec, 7,"|");
39 : </CFSCRIPT>
40 :                  

 
 
0
Comment
Question by:ostashenp
  • 5
  • 3
  • 3
11 Comments
 
LVL 25

Expert Comment

by:dgrafx
ID: 18844513
obviously - somewhere in the text file there is a list that does not have 7 entries as delimited by |

so you need to catch this error so you can continue

replace your cfscript with:
<cftry><cfset Jobnumber = ListGetAt(orec, 1,"|")><cfcatch><cfset Jobnumber = ""></cfcatch></cftry>
<cftry><cfset Jobtitle = ListGetAt(orec, 2,"|")><cfcatch><cfset Jobtitle = ""></cfcatch></cftry>
<cftry><cfset Location = ListGetAt(orec, 3,"|")><cfcatch><cfset Location = ""></cfcatch></cftry>
<cftry><cfset State = ListGetAt(orec, 4,"|")><cfcatch><cfset State = ""></cfcatch></cftry>
<cftry><cfset Company = ListGetAt(orec, 5,"|")><cfcatch><cfset Company = ""></cfcatch></cftry>
<cftry><cfset Jobfield2 = ListGetAt(orec, 6,"|")><cfcatch><cfset Jobfield2 = ""></cfcatch></cftry>
<cftry><cfset Website = ListGetAt(orec, 7,"|")><cfcatch><cfset Website = ""></cfcatch></cftry>
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18845014

 Coldfusion ignore blanks in its lists.  So, if no entry is made in JobField2, the number of items on the list will be 6 not 7.  Therefore, your code will throw that error.

 The way around this is to preprocess every line to ensure there is a value for every item.   I do this by placing the word "NULL" between empty delimiters.  Like this:

 <cfset orec = replace(orec,"||,"|NULL|","all")>

Of course, when assigning into the variable, you have to take the extra step to look for NULL to get rid of it..

 <cfset Jobnumber = ListGetAt(orec, 1,"|")>
 <cfif  Jobnumber is "NULL">
     <cfset JobNumber  = "">
 </cfif>

Yes, It's a pain!


 (You can replace "NULL" with anything that's unlikely to be in the datafile such as  $E#M#P#T#Y$   - use a variable to make it easier :)

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18845037

dgrafx,  I think your approach will allow the values to be misaligned.  If an element is missing in the 4th position, the 5th value will be assigned to the 4th element and so on..  The error won't occur until the 7th position.  I think your solution would make the 7th item empty while the other items would shift their values into the wrong variables.

0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 25

Expert Comment

by:dgrafx
ID: 18845910
what you say is correct gdemaria

the bottom line though is that this text file is formatted incorrectly.
It's not just a matter of "the 8th element has been removed"
Your "text file maintainer guy" #$%&@ it up ...
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18846137

 Yes, it is entirely possible the text file is corrupt or not propperly structure.

 But don't count out the empty element, it would cause the error.  See this example..

 JOB87|President|Dallas|TX|ACME Corp||www.google.com

 The above data record shows there is no value for JobField2 (the second to last element).  The record is a valid data record, however to coldfusion there are only 6 elements in it.  To Excel, for example, there are 7.   So the work-around I described would be needed to "see" all seven elements.
 
 JOB87|President|Dallas|TX|ACME Corp|NULL|www.google.com

 Now there are 7 elements, the blank one is using a 'place holder'
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 18846378
good point
i was going off of when poster said that it was all working fine when they had 8 fields and now reworked so there are only 7
so i then assumed that this particular problem was a formatting error
0
 
LVL 2

Author Comment

by:ostashenp
ID: 18875435
This whole project is a total nightmare.... Let me try the upload again today and will go from there.
0
 
LVL 2

Author Comment

by:ostashenp
ID: 18878111
Ok, how to I tell it to skip that line in the text file if all 7 elements are not there ?
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 18878276

 Just wrap all your processing in this CFIF statement..

 <cfif listLen(orec,"|") eq 7>

 ......... process the record ...

 </cfif>
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18878301

 can you open your text file and search for ||  (that's two pipes together)

 If you find them, you have empty values in your data and you need to run

 <cfset datavar = replace(datavar,"||","|NULL|","all")>


 If you don't find them, then the CFIF will handle "bad" data records.
0
 
LVL 2

Author Comment

by:ostashenp
ID: 18882592
Thank you very much for your time and patience.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

820 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