String values

[jrthor2]

If I have a string that looks like this:

1 | mroscoe | 66.202.1.53 | 7/22/2003 | 7:05:47 PM | Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)

How can I get each value in between the | |??

I did some research, and tried using the split function like this:

Set objTextStream = objFSO.OpenTextFile(strFileName, fsoForReading)
Do While Not objTextStream.AtEndOfStream
txt = objTextStream.ReadLine
intLinesReadCount = intLinesReadCount + 1
arrValues = Split(txt,"|")
uname = arrValues(1)


I get the first row data back, but then I get this error:

Microsoft VBScript runtime error '800a0009'

Subscript out of range: '[number: 1]'

thanks

[rdoekes]

The readline function reads a line and puts the cursor in the beginning of the next row. Since your first record works, try this:
Set objTextStream = objFSO.OpenTextFile(strFileName, fsoForReading)
Do While Not objTextStream.AtEndOfStream
txt = objTextStream.ReadLine
arrValues = Split(txt,"|")
uname = arrValues(1)
Loop
So without the counter. I do not know what you do with this counter, so this is a guess...

-Rogier Doekes

[jrthor2]

This is how I got it to work:


Set objTextStream = objFSO.OpenTextFile(strFileName, fsoForReading)
Do While Not objTextStream.AtEndOfStream
txt = objTextStream.ReadLine
intLinesReadCount = intLinesReadCount + 1
arrValues = Split(txt,"|")
for i=0 to ubound(arrValues)
uname = arrValues(1)
next
stat = (Left(txt,1))
If (stat) = "0" then
stat_count0 = stat_count0 + 1
End if
If (stat) = "1" then
stat_count1 = stat_count1 + 1
End If
Response.Write txt & "<br>"
Loop


What I'm trying to do now is get a count for each uname, any suggestions? Example:

jroscoe = 5
mroscoe = 9
admin = 2

[jrthor2]

Can anyone help here?

[rdoekes]

Dim dUnames, arrNames, arrCount

Set dUnames = Server.CreateObject("Scripting.Dictionary")
Do While Not objTextStream.AtEndOfStream
arrValues = Split(objTextStream.ReadLine, "| ")
uname = arrValues(1)

If Not dUnames.Exists(uName) then
dUnames.Add uName, 1
Else
dUnames.Item(uName) = dUnames.Item(uName) + 1
End If
Loop
arrNames = dUnames.Keys
arrCount = dUnames.Items

For i = 0 To Ubound(arrNames)
Response.write arrNames(i) & " | " & arrCount(i) & "<br>"
Next

[jrthor2]

Tried This:

Set objTextStream = objFSO.OpenTextFile(strFileName, fsoForReading)
Set dUnames = Server.CreateObject("Scripting.Dictionary")
Do While Not objTextStream.AtEndOfStream
txt = objTextStream.ReadLine
arrValues = Split(txt, "| ")
uname = arrValues(1)
If Not dUnames.Exists(uName) then
dUnames.Add uName, 1
Else
dUnames.Item(uName) = dUnames.Item(uName) + 1
End If

stat = (Left(txt,1))
If (stat) = "0" then
stat_count0 = stat_count0 + 1
End if
If (stat) = "1" then
stat_count1 = stat_count1 + 1
End If
Response.Write txt & "<br>"
Loop
Response.Write "<center><b>Total Logins: " & (stat_count0 + stat_count1) & "</b></center>"
Response.Write "<center><b>Total Failed Logins: " & stat_count0 & "<br>" & "Total Successful Logins: " & stat_count1 & "</b></center>"
arrNames = dUnames.Keys
arrCount = dUnames.Items
For i = 0 To Ubound(arrNames)
Response.write arrNames(i) & " | " & arrCount(i) & "<br>"
Next
objTextStream.Close
Set objTextStream = Nothing


And got this error:

Microsoft VBScript runtime error '800a0009'

Subscript out of range: '[number: 1]'

On the line that says:

uname = arrValues(1)

[rdoekes]

Take a good look at your source file. Are there any blank lines in there, maybe the last row?

If so, then you need to add this test in there:

...
arrValues = Split(objTextStream.ReadLine, "| ")
If UBound(arrValues) > 0 Then
uname = arrValues(1)
If Not dUnames.Exists(uName) then
dUnames.Add uName, 1
Else
dUnames.Item(uName) = dUnames.Item(uName) + 1
End If
End If
....
This way you filter out the blank lines.The split function will always give an array with 1 cell in there, if it cannot find the delimiter

[jrthor2]

Thanks, there were blank lines in between each line, so that was the problem.

[jrthor2]

Ok, now, how would I get the date value in the array for each user? Example, line 1 looks like this:

1 | mroscoe | 66.202.1.53 | 7/22/2003 | 7:05:47 PM | Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)

How can I get the Date out of that line?

[rdoekes]

Dim sDate

arrValues = Split(objTextStream.ReadLine, "| ")
If UBound(arrValues) > 2 Then sDate = arrValues(3)

[jrthor2]

Ok, but how do I display the date for each name? I need something here, right?


<%
arrNames = dUnames.Keys
arrCount = dUnames.Items
For i = 0 To Ubound(arrNames)
%>

[rdoekes]

the dictionary object counts the entries for each user, regardless of date.

Do you mean you would like to have statistics of the number of times a user logs in per date?

[jrthor2]

I already have the cound for each user, but now I want to show the last date they logged in, so if mroscoe logged in 9 times, I want the date from the last login. The way it is now, it is showing the last date for the last user in the file for all users.

[rdoekes]

You are almost better off by creating a DSN connection to the file, and use good old SQL to get your reporting values;)

http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm#ODBCDriverForText

for creating DSN connection to the file

http://support.microsoft.com/default.aspx?scid=kb;en-us;295297
if you have registry issues

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_98.asp
for creating a schema.ini file in the same directory as the source file, to tell ADO how it should read the file...

And then let SQL do the work...

[rdoekes]

This is what I came up with with regards to a DSN connection to a file:
Schema.ini[theFileName.txt]
Format=Delimited(|)
ColumnNameHeader=False
MaxScanRows=0
Col1=Number Long
Col2=Uname text
Col3=IPAddress text
Col4=LoginDate DateTime

here the asp code <%
Set oConn = Server.CreateObject("ADODB.COnnection")
oConn.Open _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=c:\yourpathtothefile\;" & _
"Extensions=asc,csv,tab,txt"

Set oRs = oConn.Execute("Select uname, Count(*) as totalNumber, Max(LoginDate) as LastLogin from theFileName.txt GROUP BY uname")

Do While Not oRs.EOF
Response.write oRs("uname") & "| " & oRs("totalNumber") & " | " & oRs("LastLogin") & "<br>"
oRs.Movenext
Loop
oRs.Close
Set oRs = Nothing
oConn.Close
Set oConn = Nothing


Any idea why your file has so many blank lines? How is this file populated? If you created the code for this file, i recommend when you add a new line to this file, you ommit the last vbcrlf

[jrthor2]

I get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Text Driver] You tried to execute a query that does not include the specified expression 'Number' as part of an aggregate function.

On this line:

Set oRs = oConn.Execute("Select Number, Username, Count(*) as totalNumber, Max(LoginDate) as LastLogin from login.txt GROUP BY Username")

I only get this if I select Col1 from the text file. If I don't select the first column, then it seems to work fine. I tried putting a | in fromt of the first column, but that didn't help.

I have removed all blank lines.

[rdoekes]

No, these | in front will not work. The error message is standard sql behavior, when you aggregate (COUNT, SUM, AVG, MAX, MIN etc.) you need to use groupings for all other fields.

So you got the whole thing working?

[jrthor2]

Well, it is working as long as I don't select the first column. How can I select the first column and it still work?

Well, I got it to work if I select it using another SQL statement without any Couns, Max's, etc:

Set oRs4 = oConn.Execute("Select ID from login.txt")

So, Yes, now it seems to all be working.

thanks for all of your help!!!!!!!!

[rdoekes]

you're welcome.

If you also would like to select the number column your sql statement will look like this:
Select Number, Username, Count(*) as totalNumber, Max(LoginDate) as LastLogin from login.txt GROUP BY Number, Username

Glad to be of service.

[jrthor2]

One last thing, I can't seem to get working right. If I want to count failed logins, where would I put the sql statement to show the failed logins for each user?

Here is my sql:

Set oRs3 = oConn.Execute("Select Count(*) as Failed from login.txt where ID = '0'")

[rdoekes]

if you, in your .ini file identified the column ID as a long or otherwise numeric field, you should ommit the quotes.Set oRs3 = oConn.Execute("Select Count(*) as Failed from login.txt where ID = 0")

[jrthor2]

No, I set it to text.

I want to display it like this:

Username Failed Successful Total Date

I have the Username and Date working, but how do I get the total failed and successful to show for each person? An id of 0=failed and 1=successful.

[rdoekes]

"SELECT t0.Uname, t1.Failed, t2.Succesful, " & _
" (t1.Failed + t2.Succesful) as TotalLogin, " & _
" Max(LoginDate) as LastLogin " & _
" FROM (login.txt as t0 INNER JOIN " & _
" (SELECT UName, COUNT(*) as Failed FROM " & _
" login.txt WHERE ID = 0 " &_
" GROUP BY Uname) as t1" & _
" ON t0.uname = t1.uname) INNER JOIN " & _
"(SELECT UName, COUNT(*) as Succesful FROM " & _
" login.txt WHERE ID = 1 " &_
" GROUP BY Uname) as t2 ON t0.uname = t2.uname" & _
" GROUP BY t0.uName, t1.failed, t2.succesful"

lightly tested....;)

[jrthor2]

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Text Driver] Too few parameters. Expected 2.

Code:

Set oRs=oConn.Execute("SELECT t0.Uname, t1.Failed, t2.Succesful, " & _
" (t1.Failed + t2.Succesful) as TotalLogin, " & _
" Max(LoginDate) as LastLogin " & _
" FROM (login.txt as t0 INNER JOIN " & _
" (SELECT UName, COUNT(*) as Failed FROM " & _
" login.txt WHERE ID = '0' " &_
" GROUP BY Uname) as t1" & _
" ON t0.uname = t1.uname) INNER JOIN " & _
"(SELECT UName, COUNT(*) as Succesful FROM " & _
" login.txt WHERE ID = '1' " &_
" GROUP BY Uname) as t2 ON t0.uname = t2.uname" & _
" GROUP BY t0.uName, t1.failed, t2.succesful")


It points to line 27, which is the first line.

[rdoekes]

make sure all the column names in the sql statement map to the column definition in the schema.ini file. This error message means that some column names are not corrent.

[jrthor2]

I got your select to work, but it only comes back with 1 row, there should be 4 rows, and the successful logins and failed logins are not correct. This person should have 1 failed login and 6 successful, but it is coming back with 10 successful and 2 failed. The total failed for the file is 2.