473,321 Members | 1,916 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Read Log and assign Logout time

171 100+
Hi All
I am back with another problem. I have a system log (csv file) which logs the user name, login time and the position name only.I need to read through this file and assign the logout time for each user. The logout time for the previous user will be the next users login time in the same position.
Hope I have explained what I am trying to achieve.
Please help. I have attached a sample Excel file with the expected results for your kind consideration

Regards
Attached Files
File Type: xlsx LogSample.xlsx (8.5 KB, 249 views)
Apr 14 '16 #1
22 3593
twinnyfo
3,653 Expert Mod 2GB
rajeevs,

I do see some issues with your Data, but I think if you resolve those issues, the solution below should work fine.

The "Login" data does not look like a true date, so this might cause some problems when we try to compare these values. If they were true Date/Time, comparison should work fine.

So, how I would approach this problem would be to have the user log in. You set a variable with date/time data type and store the value of the time the user logged in. This value is then saved to that user's Login field.

Second, since you know the position of the person, you query all your records for other users at that position, but you use an aggregate query, looking for the Max value in the Login field (which ALSO does not equal the current user who just logged in).

Hopefully this table has an index (which it most definitely should), because you can't just update and aggregate query. Then you update the record you found with the login time you just saved. Skeleton below:

Expand|Select|Wrap|Line Numbers
  1. Private Sub UserLogIn()
  2.     Dim - any variables you need
  3.  
  4.     Establish the time the user logged in
  5.  
  6.     Save that value to the current User's record
  7.  
  8.     Search for the most recent user in that position
  9.  
  10.     Update that user's record
  11. End Sub
This should give you a good framework to begin with, as you continue to work on this, we will be glad to assist with any snags you might run across.

Hope this hepps!
Apr 18 '16 #2
rajeevs
171 100+
Hi twinnyfo
Thank you for the reply.
The time can be converted to date/time because the csv file name will be a date which is the log date. So I can change the login time as date/time field. I doubt which field should be indexed. Do I need to have a sequential number for each record and use that as the index or the position name as index?
You have mentioned that look for the max value for login but what I need is the next login time for the same position but with a different user name and that will be the logout time of the previous user. Please advise
Apr 18 '16 #3
zmbd
5,501 Expert Mod 4TB
rajeevs:
+ The CSV file would be appreciated too so that we can see the raw data. Once we have that...

Would be best if you'd just cut and paste this information within a post as a [code] block

I understand if there are personal details contained therein; however, one should be able to open the file up in notepad or excel etc.. and change names to JohA Doe, JohB Doe, etc... and SSN or TaxID can be altered to 000-00-0001, (...)-0002, etc...

+ Do you have any code at this point? Would you post it please?

If you haven't start yet, then please take a moment to write down your work flow.
For some reason, the physical act of writing down the work flow helps the human brain resolve the logic needed to accomplish a task.
Next, convert that informal work flow to a programing diagram.... I prefer NS-Charts (PDF File); however, any method is more helpful than winging it IMHO.

+ I Agree with TwinnyFo - you really should have an index/Primary Key field (I hate composite primary keys - my opinion though :) ) I do this with a CSV file I import from my instruments in the lab to the database.
[PK][LogInTime][LogOutTime][FK_User][etc....]

As to how to hold the data pointer, that depends on the raw data stream. I have a case where the instrument sample data export doesn't always write the data out in the same order (same header, just the moisture might be the first record or the third record for a given sample) this is the type of information your raw data file will provide.

sorry, not much more detail here... very limited time today :)

-z
Apr 24 '16 #4
rajeevs
171 100+
Hi zmbd
I will post the sample file with more details and try to write the work flow as you have suggested by tomorrow. At present here it is night and the data is in my office PC. I thank you for the detailed reply and your kind suggestions

Regards
Apr 25 '16 #5
rajeevs
171 100+
Dear zmbd
I was trying something with the data. But I feel that I am not getting the results what I am looking for. The code as below:
Expand|Select|Wrap|Line Numbers
  1. Public Function AddDetails()
  2. Dim SRs As Recordset
  3. Dim RRs As Recordset
  4. dim PRs as recordset
  5. Dim strFilter As String
  6. Dim strInputFileName As String
  7. Dim PrevRec As String
  8. 'Browse and open Cleaned Excel file to read
  9. strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
  10. strInputFileName = ahtCommonFileOpenSave(InitialDir:="PathtoOpen", Filter:=strFilter, FilterIndex:=3, DialogTitle:="Choose an Excel file...")
  11.  
  12. 'Clear old data from tmp table and read in data from selected excel file
  13. If Len(strInputFileName) > 0 Then
  14.    DoCmd.RunSQL "Delete * from tmp"
  15.    DoCmd.TransferSpreadsheet acImport, , "Tmp", strInputFileName, True, "Tmp!"
  16. Else
  17.    MsgBox "Task aborted"
  18.    Exit Function
  19. End If
  20.  
  21. 'Input Date range to process
  22. StartDate = CDate(InputBox("Begin update from Date: ", "Import Data", Format(DateAdd("d", -1, Now()), "dd mmm yyyy")))
  23. EndDate = CDate(InputBox("Update until: ", "Import Data", Format(DateAdd("d", -1, Now()), "dd mmm yyyy")))
  24.  
  25.  
  26. Screen.MousePointer = 11
  27.  
  28. 'Open data file for selected date range to be imported
  29. Set SRs = CurrentDb.OpenRecordset("Select * from tmp where Time Between #" & Format(StartDate, "mm/dd/yy") & " 00:00:00# and #" & Format(EndDate, "mm/dd/yy") & " 23:59:59# order by Time")
  30. 'Table that stores imported data
  31. Set RRs = CurrentDb.OpenRecordset("Select * from Detail order by Date")
  32. 'List of positions
  33. Set PRs = CurrentDb.OpenRecordset("Select * from Positions")
  34.  
  35. 'Checks and exits if the date range has been previously imported
  36. If Not RRs.EOF Then
  37.    RRs.MoveLast
  38.    If RRs!Date >= StartDate And RRs!Date <= EndDate Then
  39.       MsgBox "Data until this date imported previously"
  40.       GoTo exitFunction
  41.    End If
  42. End If
  43. PrevRec = SRs!Time & " " & SRs!Position
  44. Do While Not SRs.EOF
  45.    'Going through each record
  46.  
  47.  
  48.    If SRs!Operation = "OPERATOR_EVENT" And Len(Trim(SRs!Position)) > 2 Then 
  49.       'if login entry and position available add a record
  50.       RRs.AddNew
  51.       RRs!Date = Format(SRs!Time, "dd/mm/yy")
  52.       RRs!Staff = Trim(UCase(SRs!User))
  53.       RRs!Position = Trim(SRs!Position)
  54.       RRs!On = SRs!Time
  55.       RRs.Update
  56.       'Added due to missed logout entries
  57.  
  58.  
  59.       RRs.FindFirst "Position = '" & Trim(SRs!Position) & "' and [Off] is Null" 'search that staff's record that does not have a logoff time
  60. FindNull:
  61.       If Not RRs.NoMatch Then
  62.          If RRs!Staff <> SRs!User And SRs!Operation = "OPERATOR_EVENT" Then
  63.             'Update logoff time if does not exist and login person on same position is different
  64.             RRs.Edit
  65.             RRs!Off = SRs!Time
  66.             RRs!Position = RRs!Position 
  67.             RRs.Update
  68.             PRs.FindFirst "Positions = '" & Trim(RRs!Position) & "'" 'Search positions table if exists, if not then add
  69.             If PRs.NoMatch Then
  70.                PRs.AddNew
  71.                PRs!Positions = Trim(RRs!Position)
  72.                PRs.Update
  73.             End If
  74.          Else
  75.             RRs.FindNext "Position = '" & Trim(SRs!Position) & "' and [Off] is Null" 'search that staff's record that does not have a logoff time
  76.             GoTo FindNull
  77.          End If
  78.       End If
  79.  
  80.  
  81.  
  82.    Else
  83.       'MsgBox "Halt for nothing"
  84.    End If
  85.  
  86.    SRs.MoveNext
  87. Loop
  88.  
  89. RRs.FindFirst "Date = #" & Format(StartDate, "mm/dd/yy") & "#"
  90.  
  91. MsgBox "Import Completed", vbInformation + vbOKOnly, "Finish"
  92. exitFunction:
  93. PRs.Close
  94. Set PRs = Nothing
  95. RRs.Close
  96. Set RRs = Nothing
  97. SRs.Close
  98. Set SRs = Nothing
  99. 'UpdateDetails
  100. Screen.MousePointer = 0
  101.  
  102. End Function
I am attaching the sample file also. There are 3 tables in my DB. I Import all the records from the Excel file to tmp and then assigning logout time based on the criteria mentioned earlier. Please let me know am I doing the correct way?
Attached Files
File Type: xlsx 01-05-16.xlsx (16.9 KB, 307 views)
May 8 '16 #6
zmbd
5,501 Expert Mod 4TB
rajeevs:
Normally, what I would do is use the VBA Standard Text File I/O Statements, then something like Line Input #zFileIndex, zlinein followed by a split and parse the array. I've imported over 500 files at in a single batch using this method... it can take 5 or 10 minutes to import that many files; HOWEVER, in your case I'm thinking that we might look at actually trying to use the CSV as a "RecordSet"

This would have the advantage in that we can filter down the incoming data to just those records that have your "login" and "logout" events.

Now IF you could kindly type in your CSV file in to a post...
Say, just the header row and then say the next 10 lines....
Easy enough, open in Notepad, copy and paste in the post, select the pasted text and format it using the [CODE/] tool.... While in notepad you can easily change names or sensitive bits to something generic.

Once we have the actual RAW data, I can then look at the logic behind the import.

I have some code that will do this; however, it was only proof of concept stuff and I need to tweak it a tad for the ACE provider... (hello connection strings... fiddly little things :) )

Unfortunately, I cannot open your original files here at work for various reasons and we're short-staffed again today, the Gremlins took down another employee... this weather is just playing havoc with the staff this year; thus, this may have to wait until this evening for a bit of review.

NEVER FEAR Z IS HERE!

:)
May 9 '16 #7
rajeevs
171 100+
Dear zmbd
Thank you for the reply and support.
I will paste the records as you mentioned. But I will explain what I am looking for.
The file has header like
User Trainee Operation Position Time
There will be operation like "OPERATOR_EVENT","LOGON_EVENT","LOGOFF_EVENT"
In the users list there will be 3 letter initials denotes the users and some generic user initial which end with numeric values.
In the Trainee Field you will find 3 letter initials as trainees but not for all entries.
When there is a 3 letter initial and operation is "OPERATOR_EVENT" that will be considered as a login time for that user in that position. This user will sometime logout the position with the generic user initial(User Field end with numeric value) and that can be considered as the the logout time for the first user. Or if another 3 letter user in the same position "OPERATOR_EVENT" is found then that can be the previous user logout time.The generic user initial is used when there is no other person logging in for the time being. May be after some time another person will login that position.
If the Operation is "LOGON_EVENT" then that entry will be treated as a separate entry for the user and add that to the same table but there is a field in the table named as "SOLO" which is always set as Yes. For these entries the Field need to update as "No" and the logout for this entry will be the Operation "LOGOFF_EVENT" with the same user initial.
This is the logic I am looking for when we read through each line in the CSV or XLS file.
I have a temp tbl where I import the XLS file first. Then I need to run the function which will read the entries in Temp Table and add to another table which has a structure like
Date User Trainee SOLO Position Login(On) LogOff(Off)
There could be some entries in the CSV/XLS file which may not have a logoff time towards the end of the day which will be available in the next day's log.
Hope I have explained. If it was unclear please ask me again.
The csv file as below
Expand|Select|Wrap|Line Numbers
  1. User    Trainee    Operation    Position    Time
  2. ah3        OPERATOR_EVENT    AMR    01-05-16 01:58:44
  3. nbb        OPERATOR_EVENT    AMR    01-05-16 01:59:26
  4. al2        OPERATOR_EVENT    AMR    01-05-16 02:41:21
  5. man        OPERATOR_EVENT    AMR    01-05-16 03:55:55
  6. man    rhd    LOGON_EVENT    AMR    01-05-16 04:02:14
  7. ah4        OPERATOR_EVENT    AMR    01-05-16 04:48:17
  8. man        LOGOFF_EVENT    AMR    01-05-16 04:57:33
  9. art        OPERATOR_EVENT    AMR    01-05-16 06:15:08
  10. man        OPERATOR_EVENT    AMR    01-05-16 07:27:31
  11. art        OPERATOR_EVENT    AMR    01-05-16 08:29:42
  12. ams        OPERATOR_EVENT    AMR    01-05-16 10:00:07
  13. ams        OPERATOR_EVENT    AMR    01-05-16 10:10:13
  14. al2        OPERATOR_EVENT    AMR    01-05-16 10:10:14
  15. ah4        OPERATOR_EVENT    AMR    01-05-16 10:10:15
  16. art        OPERATOR_EVENT    AMR    01-05-16 11:17:44
  17. ams        OPERATOR_EVENT    AMR    01-05-16 11:49:42
  18. ah3        OPERATOR_EVENT    AMR    01-05-16 12:41:47
  19. ah3        OPERATOR_EVENT    AMR    01-05-16 13:08:40
  20. ams        OPERATOR_EVENT    AMR    01-05-16 13:08:55
  21. ams        OPERATOR_EVENT    AMR    01-05-16 14:01:22
  22. ah3        OPERATOR_EVENT    AMR    01-05-16 14:36:58
  23. ah3        OPERATOR_EVENT    AMR    01-05-16 16:04:56
  24. ams        OPERATOR_EVENT    AMR    01-05-16 16:05:08
  25. ams        OPERATOR_EVENT    AMR    01-05-16 16:56:07
  26. ah3        OPERATOR_EVENT    AMR    01-05-16 17:27:35
  27. are        OPERATOR_EVENT    ARR    01-05-16 00:56:33
  28. van        OPERATOR_EVENT    ARR    01-05-16 01:45:52
  29. moe        OPERATOR_EVENT    ARR    01-05-16 02:27:39
  30. han        OPERATOR_EVENT    ARR    01-05-16 03:27:44
  31. van        OPERATOR_EVENT    ARR    01-05-16 04:59:55
  32. moe        OPERATOR_EVENT    ARR    01-05-16 05:56:46
  33. vuu        OPERATOR_EVENT    ARR    01-05-16 06:51:10
  34. vuu        OPERATOR_EVENT    ARR    01-05-16 07:27:50
  35. van        OPERATOR_EVENT    ARR    01-05-16 07:58:57
  36. vuu        OPERATOR_EVENT    ARR    01-05-16 08:26:25
  37. han        OPERATOR_EVENT    ARR    01-05-16 09:15:41
  38. grt        OPERATOR_EVENT    ARR    01-05-16 09:43:45
  39. dic        OPERATOR_EVENT    ARR    01-05-16 10:58:18
  40. grt        OPERATOR_EVENT    ARR    01-05-16 12:25:51
  41. grt    pai    LOGON_EVENT    ARR    01-05-16 12:26:06
  42. grt        LOGOFF_EVENT    ARR    01-05-16 13:27:45
  43. kgb        OPERATOR_EVENT    ARR    01-05-16 13:29:15
  44. grt        OPERATOR_EVENT    ARR    01-05-16 14:24:20
  45. pai        OPERATOR_EVENT    ARR    01-05-16 14:24:32
  46. grt        OPERATOR_EVENT    ARR    01-05-16 14:24:50
  47. grt    pai    LOGON_EVENT    ARR    01-05-16 14:25:03
  48. zie        OPERATOR_EVENT    COD    01-05-16 15:14:57
  49. dic        OPERATOR_EVENT    COD    01-05-16 15:28:25
  50. grt        LOGOFF_EVENT    ARR    01-05-16 16:00:52
  51. dic        OPERATOR_EVENT    ARR    01-05-16 16:01:06
  52. mcn        OPERATOR_EVENT    ARR    01-05-16 16:25:22
  53. xan        OPERATOR_EVENT    ARR    01-05-16 17:43:29
  54. xan    las    LOGON_EVENT    ARR    01-05-16 17:45:29
  55. las    vhj    OPERATOR_EVENT    ARR    01-05-16 17:47:17
  56. vhj        LOGOFF_EVENT    ARR    01-05-16 18:55:50
  57. vhj    sti    LOGON_EVENT    ARR    01-05-16 18:56:06
  58. sti    xan    OPERATOR_EVENT    ARR    01-05-16 18:56:42
  59. ah4        OPERATOR_EVENT    ARR    01-05-16 18:57:49
  60. sti    xan    OPERATOR_EVENT    ARR    01-05-16 19:06:47
  61. ah4        OPERATOR_EVENT    ARR    01-05-16 19:09:06
  62. al1        OPERATOR_EVENT    ARR    01-05-16 19:12:13
  63. xan        LOGOFF_EVENT    ARR    01-05-16 19:56:13
  64. msl        OPERATOR_EVENT    ARR    01-05-16 19:56:23
  65. hek        OPERATOR_EVENT    ARR    01-05-16 20:29:49
  66. hek    las    LOGON_EVENT    ARR    01-05-16 21:39:40
  67. las    vhj    OPERATOR_EVENT    ARR    01-05-16 21:56:05
  68. las    xan    OPERATOR_EVENT    ARR    01-05-16 22:55:31
  69. are        OPERATOR_EVENT    DIR    01-05-16 00:05:07
  70. fth        OPERATOR_EVENT    DIR    01-05-16 00:55:34
  71. han        OPERATOR_EVENT    DIR    01-05-16 01:43:35
  72. vuu        OPERATOR_EVENT    COD    01-05-16 02:56:31
  73. van        OPERATOR_EVENT    DIR    01-05-16 02:57:14
  74. cr4        OPERATOR_EVENT    DIR    01-05-16 03:46:40
  75. fjp        OPERATOR_EVENT    COD    01-05-16 03:56:41
  76. moe        OPERATOR_EVENT    COD    01-05-16 04:30:17
  77. ree        OPERATOR_EVENT    COD    01-05-16 05:26:40
  78. han        OPERATOR_EVENT    COD    01-05-16 05:56:19
  79. vuu        OPERATOR_EVENT    DIR    01-05-16 06:50:00
  80. vuu        OPERATOR_EVENT    DIR    01-05-16 06:51:31
  81. van        OPERATOR_EVENT    COD    01-05-16 06:56:59
  82. han        OPERATOR_EVENT    DIR    01-05-16 07:27:10
  83. van        OPERATOR_EVENT    DIR    01-05-16 08:58:21
  84. cr4        OPERATOR_EVENT    DIR    01-05-16 09:12:37
  85. dic        OPERATOR_EVENT    DIR    01-05-16 09:42:59
  86. mcn        OPERATOR_EVENT    DIR    01-05-16 10:20:39
  87. grt        OPERATOR_EVENT    DIR    01-05-16 11:24:46
  88. mcn        OPERATOR_EVENT    DIR    01-05-16 11:56:27
  89. cr4        OPERATOR_EVENT    DIR    01-05-16 12:25:05
  90. kgb        OPERATOR_EVENT    DIR    01-05-16 12:28:45
  91. mcn        OPERATOR_EVENT    DIR    01-05-16 13:28:55
  92. dic        OPERATOR_EVENT    DIR    01-05-16 14:25:35
  93. mcn        OPERATOR_EVENT    DIR    01-05-16 14:58:23
  94. kgb        OPERATOR_EVENT    DIR    01-05-16 15:28:03
  95. grt        OPERATOR_EVENT    DIR    01-05-16 16:27:01
  96. grt    pai    LOGON_EVENT    DIR    01-05-16 16:38:49
  97. grt        LOGOFF_EVENT    DIR    01-05-16 17:24:07
  98. dic        OPERATOR_EVENT    DIR    01-05-16 17:24:26
  99. msl        OPERATOR_EVENT    DIR    01-05-16 17:42:04
  100. hek        OPERATOR_EVENT    DIR    01-05-16 18:57:01
  101. hek        OPERATOR_EVENT    DIR    01-05-16 19:05:00
  102. cr5        OPERATOR_EVENT    DIR    01-05-16 19:05:00
  103. hek    sti    LOGON_EVENT    DIR    01-05-16 19:57:56
  104. ah4        OPERATOR_EVENT    DIR    01-05-16 19:57:58
  105. sti    vhj    OPERATOR_EVENT    DIR    01-05-16 19:58:20
  106. cr5        OPERATOR_EVENT    DIR    01-05-16 19:59:58
  107. vhj        LOGOFF_EVENT    DIR    01-05-16 20:55:46
  108. xan        OPERATOR_EVENT    DIR    01-05-16 20:55:54
  109. xan    las    LOGON_EVENT    DIR    01-05-16 20:58:17
  110. xan        LOGOFF_EVENT    DIR    01-05-16 21:37:27
  111. cr4        OPERATOR_EVENT    DIR    01-05-16 21:37:40
  112. cr2        OPERATOR_EVENT    DPN    01-05-16 01:45:25
  113. fjp        OPERATOR_EVENT    DPN    01-05-16 01:45:39
  114. ree        OPERATOR_EVENT    DPN    01-05-16 03:27:23
  115. bry        OPERATOR_EVENT    DPN    01-05-16 04:30:51
  116. fjp        OPERATOR_EVENT    DPN    01-05-16 05:52:24
  117. ree        OPERATOR_EVENT    DPN    01-05-16 07:26:09
  118. nic        OPERATOR_EVENT    DPN    01-05-16 08:59:49
  119. cra        OPERATOR_EVENT    DPN    01-05-16 09:43:55
  120. zie        OPERATOR_EVENT    DPN    01-05-16 11:26:24
  121. cr2        OPERATOR_EVENT    DPN    01-05-16 11:39:39
  122. cr2        OPERATOR_EVENT    DPN    01-05-16 14:00:36
  123. ada        OPERATOR_EVENT    DPN    01-05-16 14:00:43
  124. cra        OPERATOR_EVENT    COD    01-05-16 14:01:02
  125. meh        OPERATOR_EVENT    DPN    01-05-16 14:05:34
  126. zie        OPERATOR_EVENT    DPN    01-05-16 15:29:40
  127. ada        OPERATOR_EVENT    DPN    01-05-16 16:30:00
  128. zie        OPERATOR_EVENT    DPN    01-05-16 16:57:27
  129. kay        OPERATOR_EVENT    DPN    01-05-16 17:42:02
  130. fre        OPERATOR_EVENT    DPN    01-05-16 18:52:41
  131. aar        OPERATOR_EVENT    DPN    01-05-16 19:55:40
  132. cr2        OPERATOR_EVENT    DPN    01-05-16 20:45:22
  133. rmv        OPERATOR_EVENT    DPS    01-05-16 00:00:05
  134. duf        OPERATOR_EVENT    COD    01-05-16 00:04:20
  135. rdb        OPERATOR_EVENT    COD    01-05-16 00:07:26
  136. duf        OPERATOR_EVENT    COD    01-05-16 00:21:06
  137. lau        OPERATOR_EVENT    DPS    01-05-16 00:54:40
  138. bek        OPERATOR_EVENT    DPS    01-05-16 01:19:08
  139. rmv        OPERATOR_EVENT    COD    01-05-16 01:25:41
  140. ree        OPERATOR_EVENT    DPS    01-05-16 01:43:34
  141. nic        OPERATOR_EVENT    COD    01-05-16 01:45:44
  142. nic    nai    LOGON_EVENT    COD    01-05-16 01:46:03
  143. al2        OPERATOR_EVENT    DPS    01-05-16 01:53:50
  144. nic        LOGOFF_EVENT    COD    01-05-16 01:56:04
  145. nai        OPERATOR_EVENT    COD    01-05-16 01:56:49
  146. nic        OPERATOR_EVENT    COD    01-05-16 02:01:12
  147. nic    nai    LOGON_EVENT    COD    01-05-16 02:01:42
  148. ree        OPERATOR_EVENT    DPS    01-05-16 02:02:39
  149. al3        OPERATOR_EVENT    DPS    01-05-16 02:10:12
  150. nai    bry    OPERATOR_EVENT    COD    01-05-16 02:26:42
  151. bry        LOGOFF_EVENT    COD    01-05-16 02:38:12
  152. vuu        OPERATOR_EVENT    COD    01-05-16 02:55:32
  153. ah4        OPERATOR_EVENT    DPS    01-05-16 02:56:17
  154. nic        OPERATOR_EVENT    DPS    01-05-16 02:57:14
  155. nic    nai    LOGON_EVENT    DPS    01-05-16 02:58:00
  156. nic        LOGOFF_EVENT    DPS    01-05-16 04:32:09
  157. ree        OPERATOR_EVENT    DPS    01-05-16 04:32:22
  158. nic        OPERATOR_EVENT    DPS    01-05-16 05:05:43
  159. nic    nai    LOGON_EVENT    DPS    01-05-16 05:07:39
  160. nic        LOGOFF_EVENT    DPS    01-05-16 06:31:05
  161. bry        OPERATOR_EVENT    DPS    01-05-16 06:31:54
  162. van        OPERATOR_EVENT    COD    01-05-16 06:59:43
  163. nic        OPERATOR_EVENT    COD    01-05-16 07:27:57
  164. nic    nai    LOGON_EVENT    COD    01-05-16 07:28:19
  165. nic        LOGOFF_EVENT    COD    01-05-16 07:58:14
  166. fjp        OPERATOR_EVENT    COD    01-05-16 07:58:26
  167. nic        OPERATOR_EVENT    DPS    01-05-16 08:00:16
  168. nic    nai    LOGON_EVENT    DPS    01-05-16 08:01:33
  169. nic        LOGOFF_EVENT    DPS    01-05-16 08:31:57
  170. bry        OPERATOR_EVENT    DPS    01-05-16 08:32:07
  171. ree        OPERATOR_EVENT    COD    01-05-16 09:00:37
  172. fjp        OPERATOR_EVENT    COD    01-05-16 09:24:21
  173. meh        OPERATOR_EVENT    DPS    01-05-16 09:42:57
  174. ada        OPERATOR_EVENT    COD    01-05-16 09:45:13
  175. kgb        OPERATOR_EVENT    COD    01-05-16 10:44:17
  176. ada        OPERATOR_EVENT    DPS    01-05-16 11:24:17
  177. al2        OPERATOR_EVENT    DPS    01-05-16 11:39:46
  178. ada        OPERATOR_EVENT    DPS    01-05-16 11:39:46
  179. kgb        OPERATOR_EVENT    COD    01-05-16 11:39:46
  180. grt        OPERATOR_EVENT    COD    01-05-16 11:58:55
  181. cra        OPERATOR_EVENT    DPS    01-05-16 11:59:26
  182. mcn        OPERATOR_EVENT    COD    01-05-16 12:25:25
  183. mcn        OPERATOR_EVENT    COD    01-05-16 12:41:53
  184. cra        OPERATOR_EVENT    DPS    01-05-16 12:41:53
  185. al2        OPERATOR_EVENT    DPS    01-05-16 12:41:54
  186. al2        OPERATOR_EVENT    DPS    01-05-16 12:42:10
  187. cra        OPERATOR_EVENT    DPS    01-05-16 12:42:11
  188. mcn        OPERATOR_EVENT    COD    01-05-16 12:42:11
  189. zie        OPERATOR_EVENT    COD    01-05-16 12:52:52
  190. meh        OPERATOR_EVENT    DPS    01-05-16 12:59:12
  191. cra        OPERATOR_EVENT    COD    01-05-16 13:29:10
  192. zie        OPERATOR_EVENT    DPS    01-05-16 13:31:13
  193. dic        OPERATOR_EVENT    COD    01-05-16 13:35:05
  194. cra        OPERATOR_EVENT    COD    01-05-16 13:56:40
  195. cra        OPERATOR_EVENT    COD    01-05-16 14:00:59
  196. ada        OPERATOR_EVENT    DPS    01-05-16 14:25:42
  197. al2        OPERATOR_EVENT    DPS    01-05-16 14:37:12
  198. ada        OPERATOR_EVENT    DPS    01-05-16 14:37:12
  199. cra        OPERATOR_EVENT    COD    01-05-16 14:37:12
  200. zie        OPERATOR_EVENT    COD    01-05-16 14:57:38
  201. dic        OPERATOR_EVENT    COD    01-05-16 15:29:06
  202. meh        OPERATOR_EVENT    COD    01-05-16 15:58:14
  203. cra        OPERATOR_EVENT    DPS    01-05-16 15:59:05
  204. meh        OPERATOR_EVENT    COD    01-05-16 16:04:46
  205. al2        OPERATOR_EVENT    DPS    01-05-16 16:04:46
  206. cra        OPERATOR_EVENT    DPS    01-05-16 16:04:46
  207. al2        OPERATOR_EVENT    DPS    01-05-16 16:04:48
  208. meh        OPERATOR_EVENT    COD    01-05-16 16:04:49
  209. cra        OPERATOR_EVENT    DPS    01-05-16 16:04:49
  210. ada        OPERATOR_EVENT    COD    01-05-16 16:59:15
  211. al2        OPERATOR_EVENT    DPS    01-05-16 17:27:53
  212. ada        OPERATOR_EVENT    COD    01-05-16 17:27:54
  213. cra        OPERATOR_EVENT    DPS    01-05-16 17:27:54
  214. al2        OPERATOR_EVENT    DPS    01-05-16 17:30:15
  215. wei        OPERATOR_EVENT    COD    01-05-16 17:41:31
  216. hup        OPERATOR_EVENT    DPS    01-05-16 17:41:49
  217. wei    sti    LOGON_EVENT    COD    01-05-16 17:57:52
  218. sti    xan    OPERATOR_EVENT    COD    01-05-16 17:58:06
  219. xan        LOGOFF_EVENT    COD    01-05-16 18:27:51
  220. wei        OPERATOR_EVENT    COD    01-05-16 18:28:05
  221. wei        OPERATOR_EVENT    COD    01-05-16 19:07:36
  222. aar        OPERATOR_EVENT    COD    01-05-16 19:08:59
  223. acc        OPERATOR_EVENT    COD    01-05-16 19:12:10
  224. wei        OPERATOR_EVENT    COD    01-05-16 19:12:44
  225. kay        OPERATOR_EVENT    DPS    01-05-16 19:25:16
  226. hup        OPERATOR_EVENT    COD    01-05-16 19:54:51
  227. fre        OPERATOR_EVENT    DPS    01-05-16 19:58:06
  228. hup        OPERATOR_EVENT    COD    01-05-16 20:46:41
  229. al2        OPERATOR_EVENT    DPS    01-05-16 20:46:42
  230. fre        OPERATOR_EVENT    DPS    01-05-16 20:46:43
  231. aar        OPERATOR_EVENT    DPS    01-05-16 20:49:02
  232. kay        OPERATOR_EVENT    COD    01-05-16 20:55:58
  233. vhj        OPERATOR_EVENT    COD    01-05-16 20:57:45
  234. fre        OPERATOR_EVENT    COD    01-05-16 21:21:57
  235. kay        OPERATOR_EVENT    DPS    01-05-16 21:26:58
  236. aar        OPERATOR_EVENT    COD    01-05-16 21:40:22
  237. xan        OPERATOR_EVENT    COD    01-05-16 22:27:42
  238. wei        OPERATOR_EVENT    DPS    01-05-16 22:49:59
  239. acc        OPERATOR_EVENT    COD    01-05-16 22:55:19
  240. aar        OPERATOR_EVENT    COD    01-05-16 23:03:52
  241. ah2        OPERATOR_EVENT    MIN    01-05-16 04:01:20
  242. art        OPERATOR_EVENT    MIN    01-05-16 04:01:33
  243. nbb        OPERATOR_EVENT    MIN    01-05-16 04:55:38
  244. nbb    rhd    LOGON_EVENT    MIN    01-05-16 04:58:23
  245. nbb        LOGOFF_EVENT    MIN    01-05-16 06:00:53
  246. mwb        OPERATOR_EVENT    MIN    01-05-16 06:01:06
  247. nbb        OPERATOR_EVENT    MIN    01-05-16 06:58:11
  248. nbb    rhd    LOGON_EVENT    MIN    01-05-16 06:58:29
  249. nbb        LOGOFF_EVENT    MIN    01-05-16 08:00:32
  250. mwb        OPERATOR_EVENT    MIN    01-05-16 08:00:48
  251. nbb        OPERATOR_EVENT    MIN    01-05-16 08:52:10
  252. nbb    rhd    LOGON_EVENT    MIN    01-05-16 08:57:50
  253. nbb        LOGOFF_EVENT    MIN    01-05-16 09:49:46
  254. mwb        OPERATOR_EVENT    MIN    01-05-16 09:50:00
  255. man        OPERATOR_EVENT    MIN    01-05-16 10:01:01
  256. ah2        OPERATOR_EVENT    MIN    01-05-16 10:10:01
  257. ah2        OPERATOR_EVENT    MIN    01-05-16 11:02:22
  258. fis        OPERATOR_EVENT    MIN    01-05-16 11:02:29
  259. ah2        OPERATOR_EVENT    MIN    01-05-16 11:58:30
  260. ah2        OPERATOR_EVENT    MIN    01-05-16 13:08:54
  261. fis        OPERATOR_EVENT    MIN    01-05-16 13:09:08
  262. ah2        OPERATOR_EVENT    MIN    01-05-16 14:00:35
  263. ah2        OPERATOR_EVENT    MIN    01-05-16 16:03:54
  264. fis        OPERATOR_EVENT    MIN    01-05-16 16:04:06
  265. fis        OPERATOR_EVENT    MIN    01-05-16 16:04:49
  266. fis        OPERATOR_EVENT    MIN    01-05-16 16:05:17
  267. ah2        OPERATOR_EVENT    MIN    01-05-16 16:55:52
  268.  
May 9 '16 #8
zmbd
5,501 Expert Mod 4TB
YIKES that's quite the logic mapping...

+ Your log creator is sadistic.

+ IMHO, From a business stand point, the log would have a difficult time passing even the most basic of regulatory audits.

It's going to take me a while to digest this information. I think I have it and then re-read your post and the target shifts.

+Another thing, I've had a chance to look at the file in the OP and the description therein more closely at home... the raw data and your subsequent explanation is substantially different than originally described.

+ Finally, what I can tell you:
[Date] and [Time] are reserved words and though they may not give you issues now, they will most likely give you issues down the road...
Problem names and reserved words in Access
May 10 '16 #9
zmbd
5,501 Expert Mod 4TB
1) This is the distilled logic I've pulled from your explanation
I am using Open and Close as general Classification of event
I am using "Dedicated" to indicate that the same user and position are required
Alpha refers to only alphabetical entry - user specific
AlphaNum refers an entry with both alphabetical and numeric entry - not user specific

Expand|Select|Wrap|Line Numbers
  1. [User    ][Trainee][Event ][Position][class          ][last Class      ]
  2. [        ][Alpha  ][OpE   ][same    ][Opens position ][                ]
  3. [AlphaNum][       ][OpE   ][same    ][               ][Closes position ]
  4. [Alpha   ][       ][OpE   ][same    ][               ][Closes position ]
  5. [Alpha   ][       ][LogOn ][same    ][Opens Dedicated][                ]
  6. [Alpha   ][       ][LogOff][same    ][               ][Closes Dedicated]
So we have five conditions that trigger four classes of events
Two of the four classes of events are dedicated to each other.

Here's the spoiler in the logic:
The generic user initial is used when there is no other person logging in for the time being. May be after some time another person will login that position.
Using the above logic, the "AA#" will close the oldest open non-dedicated position (line 3).
May 10 '16 #10
rajeevs
171 100+
Dear zmbd
May I put it in some simple logic because I am confused with the earlier post from you. I will PM you now
May 10 '16 #11
rajeevs
171 100+
Hi
The data is actually from an application log.
Imagine it as few physical computers are connected together and running the same application. The PCs are the positions. Some positions (or PCs) run 24 Hrs and some of them not 24 hrs. Whenever a user login to the position (PC) that event is logged by the application in the event log as "Operator_event" with the user's initial, position name and time. But when he leave the position there is no logout recorded by the application. Instead, if it is a 24 Hr position another person will come and login and the first person will be logged out. And that event is logged again as "Operator_event" with new person's initial,position and current time. If it is not a 24 hr position then the first user may logout from the position by using the alpha numeric generic (there is a set of alphanumeric initials available)initial and that event is also logged as "Operator_event" with the same user's initial, position name and current time.
The event name in the log "LOGON_EVENT" is happening when there is a trainee sitting with the actual person, the application allows multi user login input. At the same time or within few seconds the event log creates "Operator_event" entry also for the instructor.When the trainee and instructor leaves the application, the event log creates the entry named as "LOGOFF_EVENT" with the Instructor name, trainee name, position and logged out time.
This log closes everyday midnight and saves in a server folder as csv. So there could be some positions which will have next user "Operator_event" happens only the next day.
I am trying to tabulate this data for further analysis in an access table with a structure as DateOfLog, User, Trainee, SOLO(Yes/No), Login, Logout.

Please excuse me if this was a long and detailed one. I thought this way I can express my logic
Thank you for all the support
May 11 '16 #12
zmbd
5,501 Expert Mod 4TB
Ok,
Your table structure
[DateOfLog][User][Trainee][SOLO][LogIN][LogOUT]

Would you consider adding [PC_Seat]? This way in the records, you can search for both the User and the Position. This may provide something unique for your events so that you can properly fill in the [LogOUT] times. Even it is nothing more than locating the first record
WHERE ([PC_Seat]=[POSTITION]) AND (LogOUT is NULL))
for those cases where the user is entering "AA#" for their initial. You also need this information so that you can find which pc to logout from and to login to along with how to slot the [Trainee] to the [User] for the [Position]

From there I think it's a nested select-case. The outer to determine the event. The LOGON/LOGOFF might be able to be handled within the outer conditional. The inner conditional for the Operator_event will have to determine if the Initials are alphanumeric or just alpha in nature, trainee, etc... and act appropriately.

(I tend to avoid nested if-then logic :) )
Expand|Select|Wrap|Line Numbers
  1. The following is pseudo/logic-code
  2. SELECT CASE (EVENTNAME)
  3.    CASE "LOGON_EVENT"
  4.       'Logic to handle creating the new record
  5.       'is there a trainee to deal with
  6.    CASE "LOGOUT_EVENT"
  7.       'Logic to find the PC_Seat and User and update 
  8. record
  9.       'is there a trainee to deal with
  10.    CASE "Operator_Event"
  11.       >Function to check for numeric value in userintils 
  12.       SELECT CASE (FNC(userintils))
  13.          CASE alphanumeric
  14.             'logic to find the first PC_Seat record that matches and doesn't have a logout time and update
  15.             'logic to create the new record for the user and PC_Seat
  16.             'is there a trainee to deal with
  17.          CASE Alpha
  18.             'logic to find the first matching PC_Seat without a logout time. Prompt the User to logout the prior user using your alphanumeric
  19.             'logic to create new record for the User and PC_Seat
  20.             'is there a trainee to deal with
  21.       end select (userintils)
  22. END select (EVENTNAME)
I still think that using the ADODB method to connect the CSV/XLS file is the better way to go... I believe one can also use the SQL IN Clause for DAO; however, I haven't tried that method on either type of file. I may try that later tonight depending on the kids :)

I also use a variation of NeoPAs Select a File or Folder using the FileDialog Object that I've used since, well, a very long time, I use a little simpler version I pulled out of a textbook so many years ago; however, because one can use the for each method with the .selecteditems property of the object with a multi select option set to true, one can step thru the selected files without additional code and return the whole path with the file's name, which can be split for the ADODB connection string or maybe better yet for the IN clause.

-Z
May 13 '16 #13
rajeevs
171 100+
Dear zmbd
Thank you for the reply and code. I think I mentioned already that the table has a field "POSITION". I will try the logic and let you know the result, even I am not so good in coding.
There is one piece I forgot to mention that the during the time period between on and off of a person, he should not have another position in his time.
eg:
if AAA is on position DPS from 01:00 to 02:30 then there should not be a different position(eg. AMR) for AAA in between those 01:00 to 02:30.
If you use my sample data and try to import using the code I have posted earlier this is what is happening.
I am waiting for your reply
May 13 '16 #14
zmbd
5,501 Expert Mod 4TB
You would simply add the check:
WHERE ([USER]=(operator loging) AND (LogOUT is NULL)) to the logic. More than likely this check would have to be in the LOGON_EVENT and in the OPERATOR_EVENT of the outer conditional.

In the Inner conditional of the CASE Operator_Event > Case ALPHA, is where one would put the logic to check for that user having an unclosed session. I would think that this restriction will play havoc with tracking your PC_Seats that have 24Hour sessions!
May 13 '16 #15
rajeevs
171 100+
Dear zmbd
I am totally confused. I think my explanation was not so clear.
So can we do one step at a time?
I have a small piece of code which is doing the first steps I want with some issues. Can we first fix that? I have attached an Excel sheet which shows part of my raw data and the result of my code.
I am concentrating only "OPERATOR_EVENT".
The results need to be as per the expected column in the Excel sheet.
Please let me know where I am going wrong? I have commented in the code to have a better understanding of what I am trying
Expand|Select|Wrap|Line Numbers
  1. Public Function Logouts()
  2. Dim RawTbl As Recordset
  3. Dim MstrTbl As Recordset
  4. Dim StartDate As Date
  5. Dim EndDate As String
  6.  
  7. StartDate = CDate(InputBox("Begin update from Date: ", "Update Data", Format(DateAdd("d", -1, Now()), "dd mmm yyyy")))
  8. EndDate = StartDate 'one day's log process
  9. Screen.MousePointer = 11
  10.  
  11. Set RawTbl = CurrentDb.OpenRecordset("Select * from Raw where Time Between #" & Format(StartDate, "mm/dd/yy") & " 00:00:00# and #" & Format(EndDate, "mm/dd/yy") & " 23:59:59# order by Time")
  12. 'This Table stores imported data (from csv/xls. The structure of this tbl as fieldnames User, Trainee,Position(PCName),Event and a time field
  13. 'The times in this time field is considered as each user's login time to the corresponding position if the EventName in the Event field is"Operator_Event"
  14.  
  15. Set MstrTbl = CurrentDb.OpenRecordset("Select * from Logs order by Date")
  16. 'This tbl will be used to add records from the raw tbl with login and logout time for each user in a position
  17.  
  18. 'Checks and exits if the date range has been previously imported
  19. If Not MstrTbl.EOF Then
  20.    MstrTbl.MoveLast
  21.    If MstrTbl!Date >= StartDate And MstrTbl!Date <= EndDate Then
  22.       MsgBox "Data until this date imported previously"
  23.       GoTo exitFunction
  24.    End If
  25. End If
  26.  
  27. Do While Not RawTbl.EOF
  28.  
  29.    If RawTbl!Event = "OPERATOR_EVENT" Then
  30.    'To go through only "OPERATOR_EVENT" records only and add a record to MstrTbl
  31.  
  32.       MstrTbl.AddNew
  33.       MstrTbl!Date = Format(RawTbl!Time, "dd/mm/yy")
  34.       MstrTbl!Staff = UCase(Trim(RawTbl!User))
  35.       MstrTbl!Position = Trim(RawTbl!Position)
  36.       MstrTbl!Login = RawTbl!Time
  37.       MstrTbl.Update
  38.  
  39.      'Next step is to go through each record in MstrTbl and update the LogOut time
  40.      'This is where my problem starts, actually all the records have no logout time
  41.  
  42.       MstrTbl.FindFirst "Position = '" & Trim(RawTbl!Position) & "' and [LogOut] is Null"
  43.  
  44. UpdateLogOut:
  45.       If Not MstrTbl.NoMatch Then
  46.          If MstrTbl!Staff <> RawTbl!User Then
  47.             'Update logoff time if does not exist and login person on same position is different
  48.             MstrTbl.Edit
  49.             MstrTbl!LogOut = RawTbl!Time
  50.             MstrTbl!Position = Trim(MstrTbl!Position)
  51.             MstrTbl.Update
  52.  
  53.             End If
  54.          Else
  55.  
  56.             MstrTbl.FindNext "Position = '" & Trim(RawTbl!Position) & "' and [Off] is Null"
  57.  
  58.             GoTo UpdateLogOut
  59.          End If
  60.       End If
  61.  
  62.    RawTbl.MoveNext
  63. Loop
  64.  
  65. 'MstrTbl.FindFirst "Date = #" & Format(StartDate, "mm/dd/yy") & "#"
  66.  
  67. exitFunction:
  68.  
  69. MstrTbl.Close
  70. Set MstrTbl = Nothing
  71. RawTbl.Close
  72. Set RawTbl = Nothing
  73.  
  74. Screen.MousePointer = 0
  75. MsgBox "Process Completed", vbInformation + vbOKOnly, "Updates"
  76. End Function
May 14 '16 #16
rajeevs
171 100+
Sorry I missed the excel sheet. Attaching now
Attached Files
File Type: xlsx ExpectedResults.xlsx (11.0 KB, 261 views)
May 14 '16 #17
zmbd
5,501 Expert Mod 4TB
The example data in Post#8

That is your actual raw data?
That is to say, if I cut and paste it, AS IS, in to a text file (say using the standard notepad.exe) and save it as
"PC_Seat_Log.CSV"
OTHER THAN THE NAME OF THE FILE, would I have a reasonable copy of your raw data?

>INCLUDING the header row:
[User][Trainee][Operation][Position][Time]

I'll take a closer look here later, this weekend I am tied up with family outings for most of the weekend :)

1) I don't think we need the temporary table
2) From your xlxs file I think I have part of the puzzle.
3) HOW are you determining the Log File's date for that first column in the expected results?

Have a good weekend
May 14 '16 #18
rajeevs
171 100+
Dear zmbd
That is from the actual raw data. The original raw data has no headers and lot of other records as well. I do a clean up by opening it in Excel and name the file as the date of the file and save in Excel.
From there I import to my Access Temp table. Then I try to run the code to assign the logout times based on the criteria I mentioned.
And the date can be determined from the time string. But in the actual data the time is logged as something like "00:00:05.276_589". So while doing the cleanup in Excel I add date to the time.
May 14 '16 #19
rajeevs
171 100+
Dear zmbd
I would like to mention that the actual raw data doesnt have any header row and it is delimited with tabs.
May 14 '16 #20
zmbd
5,501 Expert Mod 4TB
rajeevs, thank you for the update.

So what we are actually working with is a slightly modified CSV file that the header rows are added to and the date adjusted accordingly.

So, for anyone following along, for the public thread it might be best to focus on the format as given in POST#8 as that is the data we're going to actually import/work with and not the "Raw" output of your system. Once we get this generic data to work, I think the expansion to the full modified CSV file should be possible.

It will take me little bit to review things.
May 16 '16 #21
zmbd
5,501 Expert Mod 4TB
Z: {Updated 2016-05-25: Had a prior revision of the database in the zip. It works just fine if you've d/l'd the old one, this one is just a refinement}

Because Post#17 had the example data and results and I wasn't doubly sure that the example data listed in post#8 was the same, I've based the following on Post#17

The attached zipfile has the database, the excel workbook from post#17, and a CSV file made from the input data as shown on the worksheet in post#17.

Three tables:
+ t_PCSeatLog - is empty and will be the table the data is imported into when the VBA runs

+ t_PCSeatLog_empty - also an empty table... just because it's nice to be able to reset things when testing. Just go with-it, this is the OCD in my development personality (%_%)

+ t_PCSeatLog_postfinalrun - this is the final results from my testing. When the filter I applied on the table so as to compare the resulting records against the desired output shown in Post#17 for position DSP, the records match one for one.

+ f_main - form will open with the database.
Very simple, has a little user feedback, a command button to launch the code, and a command button to exit Access (without save or warning!).
Some simple event coding here to make things pretty. :)
I like pretty - Pretty good.... yes my precious, yesssss....
(cough) :)
I'll not be posting this code.

+ Module m_connect_to_csv_adodb
Example code showing how to connect to the CSV as an ADODB.Recordset. VERY SIMPLE STUFF HERE!
The concepts from the DAO version can be pulled over here; however, ADODB has some different ways of doing things such as the default connection is "read forward" so you have to specify that you want to have random access to the data to do what I've done in the DAO version.

+ Module m_Connect_To_CSV_DAO
This is the "meat and potatoes" code.

This code is longish and commented so it really should stand by itself as an explanation.

The procedures within are:
Sub zGetCSVData_DAO() is the main calling routine that establishes the connection to the CSV file.
Using the file dialog, multi-select true, etc... one can conceivably create a loop (and I do just that, loop thru selected files, at work with importing my data files - today imported over 1000 such text files with at least 6 to 50 record entries in each... took about 5 minutes to parse)

Sub ParseDataFromCSVtoTBL(zRSIN As Object) This is the main parsing loop. The CSV-Recordset is passed in to this procedure and the logic applied based on the events.
Right now the code is only working for the Operator_Event as suggested in Post#17; however expansion is in place for other events

Sub logoutSeat(zTableRS_IN As Object, zCSVRS_IN As Object) As the name suggests, this looks for any open position, that is, a position without a logout time entry and takes the current CSV-Record and applies that information if the matching entry in the table is found

Sub logoutUser(zTableRS_IN As Object, zCSVRS_IN As Object) Same type of action as the Logoutseat however, this is for the user.

RecordUserLogon(zCSVRS_IN As Object, Optional zSOLO_in As Integer = 1) Here we create a new record for the current CSV-Record provided there isn't already a matching record in the table, based on user, position, logintime, and solo state.

Sub PrintCSVDataToDebug(zRSIN As Object) This was my proof of concept code to make sure I was able to pull the headers and records from the CSV File
I started out with the field name [Operation] based upon rajeevs' original information; however, that appears to have been changed to [Event]; thus, as this code stands it will not work; however, simple change in names and it's good to go... This procedure was called from Sub zGetCSVData_DAO(); however, I've commented that call out of the code.

I may post the code blocks in a subsequent entry if anyone is interested in having the text within the post thread... not sure where the cutoff is on the posts and this is getting long enough :0

=Z=

BTW: rajeevs - you should be able to expand the concepts used herein to your actual log file :) - it will take a bit more work for you to adapt this project to match your exact data file; however, I am out of time for the month.

That'll be Two Beers and a dinner. :)
=z=

NOTE: I will have very limited access - pun intended :0) - for the next few weeks. You are not abandoned just in suspended animation.
Attached Files
File Type: zip 966172_UsingCSVasTable.zip (164.9 KB, 34 views)
May 25 '16 #22
rajeevs
171 100+
Dear zmbd
I havent checked the DB. And writing this reply after going through your detailed explanation. I Owe you. You are so kind zmbd. I am so happy for such a detailed explanation and the time you spent for this.
Even during your busy schedule this is something special.
I will check and update you the results once you are back. Hope you check back on this thread.
REgards
May 25 '16 #23

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Chris Stegeman | last post by:
Hello there, On my site people have to log in and automatically start a session. An authorized login grants access to other pages and MySQL data. However when the user waits to long, the server...
0
by: ShailShin | last post by:
Hi All, Developing an VB App for Login and logout tracking. In App Form there are two buttons login and logout. When user click on login the loginName, LoginTime and LoginDate get stored in...
0
by: onlymukti4u | last post by:
Hi All, Can anyone help me out to get the code for knowing the login and logout time difference between some pages. What exactly I want is...."I have a small project having 5 pages,once the...
6
by: Thiago Macedo | last post by:
I could not find on the web a complete solution for this task. This is not the perfect solution, because it's doesn't have the ability to log the logout if browser crash or user leave it open while...
5
by: aajayaprakash | last post by:
<?php if($_GET) { $time_now=mktime(date('h')+5,date('i')+30); $a=date('h:i:s',$time_now); echo $b=date(m."/".d."/".Y); include("dbc.php");...
1
by: naveenkongati | last post by:
Hi, My application is designed under MVC2 architecture which includes JSP,Servlet,DAO and bean. In this application i am tracking a user login details into a table. When user logs out i am...
3
by: impin | last post by:
i wnat to store the login time and logout time in the database.... plz help... also how to retrieve stored logiin.logout times from database.... when i use the simple time disply $t=time();...
1
by: jagjit maan | last post by:
how to subtract the two times . one is login and second is logout .the time is get from the database but with two tables .one is login and second is logout .i got the both the time but how to...
1
by: fairy1992224 | last post by:
I had created a database to capture the user logged in time and user logged out time. But may I know how do I code what time the user had logged in and logged out in my login and logout page so that...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.