Log in

View Full Version : Data Extract from CSV File with date filter



huanghe53
10-10-2007, 02:40 AM
Hi, I have a csv file contains of data with below structure:
10/10/2007 9:05:19 AM [UI ACTION] Information None 0 N/A SC-46713 10/10/2007 9:00:00 AM ARAMS Module Information None 0 N/A SC-46713 10/10/2007 8:25:55 AM Pick and Place Robot Register Information None 0 N/A pp20
This file is huge and lengthy; more than an excel worksheet can load.
I would like to extract the file by date range specify by the user.
I have a macro written by someone to extract the data, analyze the contents and report out. But this macro extract the whole file info; and I only want to see the summary for certain date range. I have tried to look for the code over the internet for sometime but can't find anything.

I am not good in programming and would like to seek for help from the expert.

Here I attaech the code that written by someone for your reference. I am looking to add a calender interface to select the start date and end date after the file type selection; and after which when I click the button to extract data, data will be loaded according to date range specify.
---------------------------------------------------------------
Option Explicit

Public Function GetOpenFileName() As String
Dim cfdOpen As New cFileOpenDlg
Dim blnExist As Boolean
Dim strTitle As String
Dim strFilter As String
Dim strFileName As String

strTitle = "Select Log File"

Select Case geltEventLog
Case Is = eltCsv
strFilter = "EventLogFile (*.csv)|*.csv|All Files (*.*)|*.*"
Case Is = eltTab
strFilter = "EventLogFile (*.txt)|*.txt|All Files (*.*)|*.*"
Case Is = eltPerl
strFilter = "EventLogFile (*.log;*.txt)|*.log;*.txt|All Files (*.*)|*.*"
End Select

With cfdOpen
.Flags = &H281000 ' MultiSelect disabled
' .Flags = &H281200 ' MultiSelect enabled
.DlgTitle = strTitle
.Filter = strFilter
blnExist = .VBGetOpenFileName(strFileName, "")
End With

GetOpenFileName = strFileName

End Function

Sub OpenTextStream(strFileName As String)
Dim fso As Scripting.FileSystemObject
Dim f As Scripting.File

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFile(strFileName)
Set gtsEventLog = f.OpenAsTextStream(ForReading, TristateUseDefault)

End Sub

Public Function GetEventRecord(strLine As String) As EvtRecord
Dim erline As EvtRecord

Select Case geltEventLog
Case Is = eltTab
erline = GetEvtRecordTab(strLine)
Case Is = eltCsv
erline = GetEvtRecordCsv(strLine)
Case Is = eltPerl
erline = GetEvtRecordPerl(strLine)
End Select

GetEventRecord = erline

End Function

Private Function GetEvtRecordTab(strLine As String) As EvtRecord
On Error GoTo errHand
Dim erTemp As EvtRecord
Dim strRight As String
Dim strSearch1 As String
Dim strSearch2 As String
Dim strDate As String
Dim strYear As String
Dim strMonth As String
Dim strDay As String
Dim intPos As Integer
Dim dtDate As Date
Dim dtTime As Date

strRight = strLine
strSearch1 = "The following information is part of the event: "
strSearch2 = "It contains the following insertion string(s): "

' evt tab : Date[T]Time[T]Source[T]Type[T]Category[T]EventID[T]User[T]Computer[T]Description
' get Date
intPos = InStr(1, strRight, vbTab)
strDate = Left(strRight, intPos - 1)
If InStr(1, strDate, "/") Then
strMonth = Left(strDate, InStr(1, strDate, "/") - 1)
strDate = Right(strDate, Len(strDate) - InStr(1, strDate, "/"))
strDay = Left(strDate, InStr(1, strDate, "/") - 1)
strYear = Right(strDate, Len(strDate) - InStr(1, strDate, "/"))
If Len(strYear) = 2 Then strYear = "20" & strYear
strDate = strYear & "-" & strMonth & "-" & strDay
dtDate = DateSerial(CInt(strYear), CInt(strMonth), CInt(strDay))
Else
dtDate = CDate(strDate)
End If

strRight = Right(strRight, Len(strRight) - intPos)
' get Time
intPos = InStr(1, strRight, vbTab)
dtTime = CDate(Left(strRight, intPos - 1))
strRight = Right(strRight, Len(strRight) - intPos)
erTemp.DateTime = dtDate + dtTime
' get Source
intPos = InStr(1, strRight, vbTab)
erTemp.Source = (Left(strRight, intPos - 1))
strRight = Right(strRight, Len(strRight) - intPos)
' get Type
intPos = InStr(1, strRight, vbTab)
erTemp.Type = (Left(strRight, intPos - 1))
strRight = Right(strRight, Len(strRight) - intPos)
' get Category
intPos = InStr(1, strRight, vbTab)
erTemp.Category = (Left(strRight, intPos - 1))
strRight = Right(strRight, Len(strRight) - intPos)
If InStr(1, erTemp.Category, "(") > 0 Then erTemp.Category = Mid(erTemp.Category, 2, Len(erTemp.Category) - 2)
' get EventID
intPos = InStr(1, strRight, vbTab)
erTemp.EventID = (Left(strRight, intPos - 1))
strRight = Right(strRight, Len(strRight) - intPos)
' get User
intPos = InStr(1, strRight, vbTab)
erTemp.User = (Left(strRight, intPos - 1))
strRight = Right(strRight, Len(strRight) - intPos)
' get Computer
intPos = InStr(1, strRight, vbTab)
erTemp.Computer = (Left(strRight, intPos - 1))
strRight = Right(strRight, Len(strRight) - intPos)
' get Description
intPos = InStr(1, strRight, strSearch1)
If intPos > 0 Then strRight = Right(strRight, Len(strRight) - intPos - Len(strSearch1) + 1)
intPos = InStr(1, strRight, strSearch2)
If intPos > 0 Then strRight = Right(strRight, Len(strRight) - intPos - Len(strSearch2) + 1)
intPos = InStr(1, strRight, "AX: ")
If intPos > 0 Then strRight = Right(strRight, Len(strRight) - intPos - 3)
intPos = InStr(1, strRight, "??: ")
If intPos > 0 Then strRight = Right(strRight, Len(strRight) - intPos - 3)
intPos = InStr(1, strRight, " UTC ")
If intPos > 0 Then strRight = Left(strRight, intPos - 22)
erTemp.Description = strRight

GetEvtRecordTab = erTemp
Exit Function

errHand:
erTemp.Category = ""
erTemp.Computer = ""
erTemp.DateTime = 0
erTemp.Description = ""
erTemp.EventID = 0
erTemp.Source = ""
erTemp.Type = ""
erTemp.User = ""

GetEvtRecordTab = erTemp
End Function

Private Function GetEvtRecordCsv(strLine As String) As EvtRecord
On Error GoTo errHand
Dim erTemp As EvtRecord
Dim strRight As String
Dim strSearch1 As String
Dim strSearch2 As String
Dim strDate As String
Dim strYear As String
Dim strMonth As String
Dim strDay As String
Dim intPos As Integer
Dim dtDate As Date
Dim dtTime As Date

strRight = strLine
strSearch1 = "The following information is part of the event: "
strSearch2 = "It contains the following insertion string(s): "

' evt comma: Date,Time,Source,Type,Category,EventID,User,Computer,Description
' get Date
intPos = InStr(1, strRight, ",")
strDate = Left(strRight, intPos - 1)
If InStr(1, strDate, "/") Then
strMonth = Left(strDate, InStr(1, strDate, "/") - 1)
strDate = Right(strDate, Len(strDate) - InStr(1, strDate, "/"))
strDay = Left(strDate, InStr(1, strDate, "/") - 1)
strYear = Right(strDate, Len(strDate) - InStr(1, strDate, "/"))
If Len(strYear) = 2 Then strYear = "20" & strYear
strDate = strYear & "-" & strMonth & "-" & strDay
dtDate = DateSerial(CInt(strYear), CInt(strMonth), CInt(strDay))
Else
dtDate = CDate(strDate)
End If
strRight = Right(strRight, Len(strRight) - intPos)
' get Time
intPos = InStr(1, strRight, ",")
dtTime = CDate(Left(strRight, intPos - 1))
strRight = Right(strRight, Len(strRight) - intPos)
erTemp.DateTime = dtDate + dtTime
' get Source
intPos = InStr(1, strRight, ",")
erTemp.Source = (Left(strRight, intPos - 1))
strRight = Right(strRight, Len(strRight) - intPos)
' get Type
intPos = InStr(1, strRight, ",")
erTemp.Type = (Left(strRight, intPos - 1))
strRight = Right(strRight, Len(strRight) - intPos)
' get Category
intPos = InStr(1, strRight, ",")
erTemp.Category = (Left(strRight, intPos - 1))
strRight = Right(strRight, Len(strRight) - intPos)
If InStr(1, erTemp.Category, "(") > 0 Then erTemp.Category = Mid(erTemp.Category, 2, Len(erTemp.Category) - 2)
' get EventID
intPos = InStr(1, strRight, ",")
erTemp.EventID = (Left(strRight, intPos - 1))
strRight = Right(strRight, Len(strRight) - intPos)
' get User
intPos = InStr(1, strRight, ",")
erTemp.User = (Left(strRight, intPos - 1))
strRight = Right(strRight, Len(strRight) - intPos)
' get Computer
intPos = InStr(1, strRight, ",")
erTemp.Computer = (Left(strRight, intPos - 1))
strRight = Right(strRight, Len(strRight) - intPos)
' get Description
intPos = InStr(1, strRight, strSearch1)
If intPos > 0 Then strRight = Right(strRight, Len(strRight) - intPos - Len(strSearch1) + 1)
intPos = InStr(1, strRight, strSearch2)
If intPos > 0 Then strRight = Right(strRight, Len(strRight) - intPos - Len(strSearch2) + 1)
intPos = InStr(1, strRight, "AX: ")
If intPos > 0 Then strRight = Right(strRight, Len(strRight) - intPos - 3)
intPos = InStr(1, strRight, "??: ")
If intPos > 0 Then strRight = Right(strRight, Len(strRight) - intPos - 3)
intPos = InStr(1, strRight, " UTC ")
If intPos > 0 Then strRight = Left(strRight, intPos - 22)
erTemp.Description = strRight

GetEvtRecordCsv = erTemp
Exit Function

errHand:
erTemp.Category = ""
erTemp.Computer = ""
erTemp.DateTime = 0
erTemp.Description = ""
erTemp.EventID = 0
erTemp.Source = ""
erTemp.Type = ""
erTemp.User = ""

GetEvtRecordCsv = erTemp
End Function

Private Function GetEvtRecordPerl(strLine As String) As EvtRecord
On Error GoTo errHand
Dim erTemp As EvtRecord
Dim strRight As String
Dim strSearch1 As String
Dim strSearch2 As String
Dim intPos As Integer
Dim dtDate As Date
Dim dtTime As Date
Dim strDate As String

strRight = strLine
strSearch1 = "The following information is part of the event: "
strSearch2 = "It contains the following insertion string(s): "

' perl: "Date"("mm-dd-yyyy"),"Time","Type","Source",Category,EventID,"Computer","Description"-Leading Char"
' get Date
intPos = InStr(1, strRight, ",")
strDate = Mid(strRight, 2, intPos - 3)
strDate = Mid(strDate, 4, 2) & "/" & Left(strDate, 2) & "/" & Right(strDate, 4)
dtDate = CDate(strDate)
strRight = Right(strRight, Len(strRight) - intPos)
' get Time
intPos = InStr(1, strRight, ",")
dtTime = CDate(Mid(strRight, 2, intPos - 3))
strRight = Right(strRight, Len(strRight) - intPos)
erTemp.DateTime = dtDate + dtTime
' get Type
intPos = InStr(1, strRight, ",")
erTemp.Type = (Mid(strRight, 2, intPos - 3))
strRight = Right(strRight, Len(strRight) - intPos)
' get Source
intPos = InStr(1, strRight, ",")
erTemp.Source = (Mid(strRight, 2, intPos - 3))
strRight = Right(strRight, Len(strRight) - intPos)
' get Category
intPos = InStr(1, strRight, ",")
erTemp.Category = (Left(strRight, intPos - 1))
strRight = Right(strRight, Len(strRight) - intPos)
' get EventID
intPos = InStr(1, strRight, ",")
erTemp.EventID = (Left(strRight, intPos - 1))
strRight = Right(strRight, Len(strRight) - intPos)
' get Computer
intPos = InStr(1, strRight, ",")
erTemp.Computer = (Mid(strRight, 2, intPos - 3))
strRight = Right(strRight, Len(strRight) - intPos)
' get Description
intPos = InStr(1, strRight, strSearch1)
If intPos > 0 Then strRight = Right(strRight, Len(strRight) - intPos - Len(strSearch1) + 1)
intPos = InStr(1, strRight, strSearch2)
If intPos > 0 Then strRight = Right(strRight, Len(strRight) - intPos - Len(strSearch2) + 1)
intPos = InStr(1, strRight, "AX: ")
If intPos > 0 Then strRight = Right(strRight, Len(strRight) - intPos - 3)
intPos = InStr(1, strRight, "??: ")
If intPos > 0 Then strRight = Right(strRight, Len(strRight) - intPos - 3)
intPos = InStr(1, strRight, " UTC ")
If intPos > 0 Then strRight = Left(strRight, intPos - 22)
erTemp.Description = strRight

GetEvtRecordPerl = erTemp
Exit Function

errHand:
erTemp.Category = ""
erTemp.Computer = ""
erTemp.DateTime = 0
erTemp.Description = ""
erTemp.EventID = 0
erTemp.Source = ""
erTemp.Type = ""
erTemp.User = ""

GetEvtRecordPerl = erTemp
End Function
-------------------------------------------------------------------------
Thanks! Hope to get the respond ASAP.

Oorang
10-10-2007, 11:54 AM
:welcome:
Hi Haunghe,
Welcome to the board. The code you posted has an undefined type "EvtRecord". For anyone to help you, you will need to tell us how to get the type definition. As you have said you are a bit new to all of this, here are some instructions I hope will help. In the visual basic editor, click tools, then references. Make a list of everything that is check marked and post it back here. Also, look for something in the project that says "Public/Private Type EvtRecord" if you find it please post the entire type block. Thanks:)

Those who believe in telekinetics, raise my hand.

huanghe53
10-10-2007, 05:50 PM
Hi! Thanks for your guide and sorry for any inconveniences. Here is the list from prefrences:
-Visual Basic Applications
-Microsoft Excel 11.0 Object Library
-OLE Automation
-Microsoft Office 11.0 Object Library
-Microsoft Form 2.0 Object Library
-Microsoft Scripting Runtime.

I shall paste the code in next post.

huanghe53
10-10-2007, 05:53 PM
Here you go with the type definition.


Public Type EvtRecord
DateTime As Date
Type As String ' Error/Warning/Information
Source As String ' Process
Category As String ' Robot Nr
EventID As Long
User As String
Computer As String
Description As String
End Type