Looping through a list, setting each listItem to a column

[ColdFusionKing]

Hello Everybody,

I am trying to read a log file and display the results on a webpage.
Here is what the log file looks.

DateTime User Webpage Visited Status
2003/09/01 11:52:15, "User\testUser1", "/

http://wwwroot/code/article_1.shtml",

"OK"
2003/09/01 11:53:35, "User\testUser2", "/

http://wwwroot/code/article_2.shtml",

"OK"
2003/09/01 12:04:55, "User\testUser3", "/

http://wwwroot/code/article_3.shtml",

"OK"
2003/09/01 12:04:58, "User\testUser4", "/

http://wwwroot/code/article_4.shtml",

"OK"
2003/09/01 12:05:11, "User\testUser5", "/

http://wwwroot/code/article_5.shtml",

"OK"

Here is the code to read the log file
<cffile
action=&quot;read&quot;
file=&quot;C:\myLogFile.log&quot;
variable=&quot;fOutput&quot;>

Take a look at the code below. As you can see I am looping through the list, finding the Carriage Return character.
This is all fine, what I want to be able to do is loop through each individual row which is again a comma seprated list

(2003/09/01 11:52:15, &quot;User\testUser1&quot;, &quot;/

http://wwwroot/code/article_1.shtml&quot;,

&quot;OK&quot append each list item in a temp table column.

<cfset qTmp=QueryNew(&quot;DateTime,User,Document,Status&quot>
<cfloop list=&quot;#fOutput#&quot; index=&quot;lIndex&quot; delimiters=&quot;#Chr(10)#&quot;>
<cfset i = 0>
<cfset tmpstart=listLen(lIndex,',')>
<cfset tmp=QueryAddRow(qTmp,listLen(lIndex,','))>
<cfloop list=&quot;#lIndex#&quot; index=&quot;innerList&quot; delimiters=&quot;,&quot;>
<cfscript>
tmp=QuerySetCell(qTmp,&quot;DateTime&quot;, innerList);
tmp=QuerySetCell(qTmp,&quot;User&quot;, innerList);
tmp=QuerySetCell(qTmp,&quot;Document&quot;, innerList);
tmp=QuerySetCell(qTmp,&quot;Status&quot;, innerList);
</cfscript>
<cfset i = i + 1>
</cfloop>
</cfloop>

<cfquery name=&quot;lquery&quot; dbtype=&quot;query&quot;>
SELECT DISTINCT *
FROM qTmp
</cfquery>

The code above is not right, and I've tried hard to figure out how to loop through the inner comma separated list and assign the value to the query column. Can somebody
please show me how to do this. I know my explaination is not very clear but I hope you know what I'm trying to do.
If you think there is an easier way to do this then please let me know. Please can somebody help
Regards
John

 

[DrStern]

Hi there, I hope this is what you are looking for. I think it works...

<cffile
action=&quot;read&quot;
file=&quot;C:\temp\myLogFile.log&quot;
variable=&quot;fOutput&quot;>

<cfset qTmp=QueryNew(&quot;DateTime,User,Document,Status&quot>

<cfset maxRows = 0>
<cfloop list=&quot;#fOutput#&quot; index=&quot;lIndex&quot; delimiters=&quot;#Chr(10)#&quot;>
<cfset maxRows = maxRows + 1>
</cfloop>

<cfset tmp=QueryAddRow(qTmp,#maxRows#)>
<cfset i = 1>
<cfloop list=&quot;#fOutput#&quot; index=&quot;lIndex&quot; delimiters=&quot;#Chr(10)#&quot;>
<cfset counter = 1>
<cfloop list=&quot;#lIndex#&quot; index=&quot;innerList&quot; delimiters=&quot;,&quot;>
<cfif counter eq 1>
<cfset tmp=QuerySetCell(qTmp,&quot;DateTime&quot;, innerList,#i#)>
<cfelseif counter eq 2>
<cfset tmp=QuerySetCell(qTmp,&quot;User&quot;, innerList, #i#)>
<cfelseif counter eq 3>
<cfset tmp=QuerySetCell(qTmp,&quot;Document&quot;, innerList, #i#)>
<cfelseif counter eq 4>
<cfset tmp=QuerySetCell(qTmp,&quot;Status&quot;, innerList, #i#)>
</cfif>
<cfset counter = counter + 1>
</cfloop>
<cfset i = i + 1>
</cfloop>

<cfquery name=&quot;lquery&quot; dbtype=&quot;query&quot;>
SELECT *
FROM qTmp
</cfquery>

<cfoutput query=&quot;lquery&quot;>
#DateTime#, #User#, #Document#, #Status# <br>
</cfoutput>

See ya'

Dr. Stern

 

[DrStern]

This is Dr. Stern again...
I forgot to change the file's path, so change:
<cffile
action=&quot;read&quot;
file=&quot;C:\temp\myLogFile.log&quot;
variable=&quot;fOutput&quot;>

to

<cffile
action=&quot;read&quot;
file=&quot;C:\myLogFile.log&quot;
variable=&quot;fOutput&quot;>

Sorry...

 

[ColdFusionKing]

Your code does not work if I have a comma inside double quotes

2003/09/01 11:52:15, &quot;User\testUser1&quot;, &quot;/Doc/News/Budgeting,Forecastingand Reporting/management reporting.doc&quot;, &quot;OK&quot;

And I want to escape the comma inside the double quotes. I hope my explaination is making sense