E
Eskimo
Private Sub bt_Select_Click()
lblCurrentFolder.Caption = BrowseFolder()
End Sub
Private Sub ParseFile(FileSpec As String)
' Given a full path to a text file, parse it into the temporary table.
Dim LineNo As Long
Dim i As Long
Dim ObsDate As String
Dim ObsTime As String
Dim CollarNo As String
Dim LC As String
Dim LatLon As String
Dim Lat As Double
Dim Lon As Double
Dim SQL As String
Dim Word As String
Dim Collar As Boolean
Dim Act As Long
Dim ErrorString As String
Dim Errors As Long
Dim ParsedRows As String
Dim DataMask As Long
' Necessary fields are given bits to allow us to tell if everything is
present before committing a record
' Bits are assigned as follows:
' 1 PTT
' 2 Date
' 4 LC
' 8 Lat
' 16 Lon
' 32 Act
lblStatus.Caption = "Opening File: " & FullFilePath
Me.Repaint
' Open the file
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile(FileSpec, 1)
LineNo = 1
DataMask = 0
' loop through the whole text file
Do Until objTextFile.AtEndOfStream
LineNo = LineNo + 1
lblStatus.Caption = "Processing File: " & FileSpec & ", Line: " &
LineNo
Me.Repaint
' get a line from the files
strnextline = objTextFile.Readline
' get rid of multiple spaces - SPLIT function needs a single space
to delimit elements
Do While InStr(1, strnextline, " ") <> 0
strnextline = Replace(strnextline, " ", " ")
Loop
' get rid of any leading and trailing blanks
strnextline = Trim(strnextline)
' break the line using space delimiters
arrline = Split(strnextline, " ")
' pull out NUM4 (ACT). There can be many structures to the text
files, so try all three ways
If CollarNo <> "" And UBound(arrline) = 5 Then
If IsNumeric(arrline(2)) And IsNumeric(arrline(3)) And
IsNumeric(arrline(4)) And IsNumeric(arrline(5)) Then
Act = Val(arrline(5))
DataMask = DataMask + 32
End If
End If
If CollarNo <> "" And UBound(arrline) = 4 Then
If IsNumeric(arrline(1)) And IsNumeric(arrline(2)) And
IsNumeric(arrline(3)) And IsNumeric(arrline(4)) Then
Act = Val(arrline(4))
DataMask = DataMask + 32
End If
End If
If CollarNo <> "" And UBound(arrline) = 3 Then
If IsNumeric(arrline(0)) And IsNumeric(arrline(1)) And
IsNumeric(arrline(2)) And IsNumeric(arrline(3)) Then
Act = Val(arrline(3))
DataMask = DataMask + 32
End If
End If
For i = 0 To UBound(arrline)
Word = arrline(i)
If IsCollar(Word) And InStr(1, strnextline, "Date") <> 0 Then
' we've hit a new collar. We should spit out the
previous one, if present
If CollarNo <> "" Then
If DataMask <> 63 Then
Errors = Errors + 1
Else
SQL = "INSERT into tblParsedData values (" &
CollarNo & ", """ & ObsDate & """, #" & ObsTime & _
"#, """ & ObsDate & " "
& ObsTime & """, """ & LC & """, " & Lat & ", " & Lon & ", " & Act & ", -1,
0)"
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
End If
End If
CollarNo = arrline(i)
DataMask = 1
End If
Select Case arrline(i)
Case "Date"
i = i + 1
' skip over any colons in the string
If arrline(i) = ":" Then
i = i + 1
End If
ObsDate = arrline(i)
ObsDate = MonthName(Val(Mid(ObsDate, 4, 2))) & " " &
Left(ObsDate, 2) & ", " & Right(ObsDate, 2)
i = i + 1
' skip over any colons in the string
If arrline(i) = ":" Then
i = i + 1
End If
ObsTime = arrline(i)
DataMask = DataMask + 2
Case "LC"
i = i + 1
' skip over any colons in the string
If arrline(i) = ":" Then
i = i + 1
End If
LC = arrline(i)
DataMask = DataMask + 4
Case "Lat1"
i = i + 1
' skip over any colons in the string
If arrline(i) = ":" Then
i = i + 1
End If
If InStr(1, arrline(i), "?") = 0 Then
If UCase(Right(arrline(i), 1)) = "N" Or
UCase(Right(arrline(i), 1)) = "S" Then
Lat = Val(Left(arrline(i), Len(arrline(i)) - 1))
Else
Lat = Val(arrline(i))
End If
Else
Lat = 0
End If
DataMask = DataMask + 8
Case "Lon1"
i = i + 1
' skip over any colons in the string
If arrline(i) = ":" Then
i = i + 1
End If
If InStr(1, arrline(i), "?") = 0 Then
If UCase(Right(arrline(i), 1)) = "E" Then
Lon = Val(Left(arrline(i), Len(arrline(i)) - 1))
ElseIf UCase(Right(arrline(i), 1)) = "W" Then
Lon = Val(Left(arrline(i), Len(arrline(i)) - 1))
* -1
Else
Lon = Val(arrline(i))
End If
Else
Lon = 0
End If
DataMask = DataMask + 16
End Select
Next i
Loop
If DataMask <> 63 Then
Errors = Errors + 1
Else
SQL = "INSERT into tblParsedData values (" & CollarNo & ", """ &
ObsDate & """, #" & ObsTime & _
"#, """ & ObsDate & " " & ObsTime & """, """
& LC & """, " & Lat & ", " & Lon & ", " & Act & ", -1, 0)"
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
End If
ParsedRows = DLookup("count(*)", "tblParsedData")
If ParsedRows = 0 Then
lstErrors.AddItem ("WARNING: " & FileSpec & " - 0 records parsed")
Else
lstErrors.AddItem (FileSpec & " - " & ParsedRows & " records parsed.
Errors: " & Errors)
End If
End Sub
The Above code parses and does other things to a file type looking like
this...
07711 Date : 01.12.04 00:08:20 LC : 1 IQ : 56
Lat1 : 75.690N Lon1 : 99.232W Lat2 : 69.689N Lon2 : 133.519W
Nb mes : 008 Nb mes>-120dB : 000 Best level : -129 dB
Pass duration : 504s NOPC : 2
Calcul freq : 401 649012.9 Hz Altitude : 53 m
01 00 00 00
07711 Date : 01.12.04 00:55:00 LC : 0 IQ : 55
Lat1 : 75.668N Lon1 : 99.313W Lat2 : 81.588N Lon2 : 74.534W
Nb mes : 005 Nb mes>-120dB : 000 Best level : -129 dB
Pass duration : 504s NOPC : 3
Calcul freq : 401 649096.8 Hz Altitude : 55 m
00 00 465 00
07711 Date : 01.12.04 00:56:24 LC : Z IQ : 00
Lat1 : ??????? Lon1 : ???????? Lat2 : ??????? Lon2 : ????????
Nb mes : 001 Nb mes>-120dB : 000 Best level : -134 dB
Pass duration : ? s NOPC : ?
Calcul freq : 401 650000.0 Hz Altitude : 0 m
01 00 00 00
07711 Date : 01.12.04 01:52:24 LC : 2 IQ : 55
Lat1 : 75.676N Lon1 : 99.223W Lat2 : 64.121N Lon2 : 168.831W
Nb mes : 006 Nb mes>-120dB : 000 Best level : -134 dB
Pass duration : 448s NOPC : 3
Calcul freq : 401 649167.3 Hz Altitude : 57 m
00 00 435 00
07711 Date : 01.12.04 02:36:44 LC : Z IQ : 00
Lat1 : ??????? Lon1 : ???????? Lat2 : ??????? Lon2 : ????????
Nb mes : 010 Nb mes>-120dB : 000 Best level : -134 dB
Pass duration : 616s NOPC : 0
Calcul freq : 401 650000.0 Hz Altitude : 1548 m
01 00 00 00
07711 Date : 01.12.04 02:40:56 LC : A IQ : 04
Lat1 : 75.693N Lon1 : 99.198W Lat2 : 67.303N Lon2 : 144.014W
Nb mes : 003 Nb mes>-120dB : 000 Best level : -132 dB
Pass duration : 112s NOPC : 2
Calcul freq : 401 649054.4 Hz Altitude : 57 m
01 00 00 00
I have a slightly new format in the text that I would want changes made to
the code to accomodate. The new kind of data looks like this....
21042 Date : 04.12.04 14:22:10 LC : Z IQ : 00
Lat1 : ??????? Lon1 : ???????? Lat2 : ??????? Lon2 : ????????
Nb mes : 001 Nb mes>-120dB : 000 Best level : -133 dB
Pass duration : ? s NOPC : ?
Calcul freq : 401 650000.0 Hz Altitude : 0 m
21875 21875
21042 Date : 04.12.04 14:42:03 LC : 2 IQ : 66
Lat1 : 75.139N Lon1 : 99.819W Lat2 : 85.660N Lon2 : 119.788W
Nb mes : 006 Nb mes>-120dB : 000 Best level : -130 dB
Pass duration : 580s NOPC : 3
Calcul freq : 401 648053.5 Hz Altitude : 19 m
21875 21875
21042 Date : 04.12.04 14:56:02 LC : 3 IQ : 66
Lat1 : 75.143N Lon1 : 99.849W Lat2 : 79.346N Lon2 : 119.014W
Nb mes : 004 Nb mes>-120dB : 000 Best level : -123 dB
Pass duration : 451s NOPC : 3
Calcul freq : 401 648052.8 Hz Altitude : 17 m
21875 21875
21042 Date : 04.12.04 15:06:14 LC : B IQ : 00
Lat1 : 75.152N Lon1 : 99.743W Lat2 : 60.309N Lon2 : 6.807W
Nb mes : 002 Nb mes>-120dB : 000 Best level : -133 dB
Pass duration : 129s NOPC : 1
Calcul freq : 401 648052.8 Hz Altitude : 748 m
21875 21875
21042 Date : 04.12.04 15:55:42 LC : 0 IQ : 56
Lat1 : 75.098N Lon1 : 99.720W Lat2 : 77.769N Lon2 : 112.993W
Nb mes : 007 Nb mes>-120dB : 000 Best level : -133 dB
Pass duration : 387s NOPC : 2
Calcul freq : 401 648098.7 Hz Altitude : 24 m
21875 21875
21042 Date : 04.12.04 16:19:22 LC : 2 IQ : 65
Lat1 : 75.148N Lon1 : 99.848W Lat2 : 83.897N Lon2 : 69.202W
Nb mes : 004 Nb mes>-120dB : 000 Best level : -133 dB
Pass duration : 258s NOPC : 2
Calcul freq : 401 648160.6 Hz Altitude : 17 m
21875 21875
I guess there is no more "ACT" portion. I just need the two numbers on the
bottom in their own respective columns.
Thanks for any suggestions ahead of time
Eskimo
lblCurrentFolder.Caption = BrowseFolder()
End Sub
Private Sub ParseFile(FileSpec As String)
' Given a full path to a text file, parse it into the temporary table.
Dim LineNo As Long
Dim i As Long
Dim ObsDate As String
Dim ObsTime As String
Dim CollarNo As String
Dim LC As String
Dim LatLon As String
Dim Lat As Double
Dim Lon As Double
Dim SQL As String
Dim Word As String
Dim Collar As Boolean
Dim Act As Long
Dim ErrorString As String
Dim Errors As Long
Dim ParsedRows As String
Dim DataMask As Long
' Necessary fields are given bits to allow us to tell if everything is
present before committing a record
' Bits are assigned as follows:
' 1 PTT
' 2 Date
' 4 LC
' 8 Lat
' 16 Lon
' 32 Act
lblStatus.Caption = "Opening File: " & FullFilePath
Me.Repaint
' Open the file
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile(FileSpec, 1)
LineNo = 1
DataMask = 0
' loop through the whole text file
Do Until objTextFile.AtEndOfStream
LineNo = LineNo + 1
lblStatus.Caption = "Processing File: " & FileSpec & ", Line: " &
LineNo
Me.Repaint
' get a line from the files
strnextline = objTextFile.Readline
' get rid of multiple spaces - SPLIT function needs a single space
to delimit elements
Do While InStr(1, strnextline, " ") <> 0
strnextline = Replace(strnextline, " ", " ")
Loop
' get rid of any leading and trailing blanks
strnextline = Trim(strnextline)
' break the line using space delimiters
arrline = Split(strnextline, " ")
' pull out NUM4 (ACT). There can be many structures to the text
files, so try all three ways
If CollarNo <> "" And UBound(arrline) = 5 Then
If IsNumeric(arrline(2)) And IsNumeric(arrline(3)) And
IsNumeric(arrline(4)) And IsNumeric(arrline(5)) Then
Act = Val(arrline(5))
DataMask = DataMask + 32
End If
End If
If CollarNo <> "" And UBound(arrline) = 4 Then
If IsNumeric(arrline(1)) And IsNumeric(arrline(2)) And
IsNumeric(arrline(3)) And IsNumeric(arrline(4)) Then
Act = Val(arrline(4))
DataMask = DataMask + 32
End If
End If
If CollarNo <> "" And UBound(arrline) = 3 Then
If IsNumeric(arrline(0)) And IsNumeric(arrline(1)) And
IsNumeric(arrline(2)) And IsNumeric(arrline(3)) Then
Act = Val(arrline(3))
DataMask = DataMask + 32
End If
End If
For i = 0 To UBound(arrline)
Word = arrline(i)
If IsCollar(Word) And InStr(1, strnextline, "Date") <> 0 Then
' we've hit a new collar. We should spit out the
previous one, if present
If CollarNo <> "" Then
If DataMask <> 63 Then
Errors = Errors + 1
Else
SQL = "INSERT into tblParsedData values (" &
CollarNo & ", """ & ObsDate & """, #" & ObsTime & _
"#, """ & ObsDate & " "
& ObsTime & """, """ & LC & """, " & Lat & ", " & Lon & ", " & Act & ", -1,
0)"
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
End If
End If
CollarNo = arrline(i)
DataMask = 1
End If
Select Case arrline(i)
Case "Date"
i = i + 1
' skip over any colons in the string
If arrline(i) = ":" Then
i = i + 1
End If
ObsDate = arrline(i)
ObsDate = MonthName(Val(Mid(ObsDate, 4, 2))) & " " &
Left(ObsDate, 2) & ", " & Right(ObsDate, 2)
i = i + 1
' skip over any colons in the string
If arrline(i) = ":" Then
i = i + 1
End If
ObsTime = arrline(i)
DataMask = DataMask + 2
Case "LC"
i = i + 1
' skip over any colons in the string
If arrline(i) = ":" Then
i = i + 1
End If
LC = arrline(i)
DataMask = DataMask + 4
Case "Lat1"
i = i + 1
' skip over any colons in the string
If arrline(i) = ":" Then
i = i + 1
End If
If InStr(1, arrline(i), "?") = 0 Then
If UCase(Right(arrline(i), 1)) = "N" Or
UCase(Right(arrline(i), 1)) = "S" Then
Lat = Val(Left(arrline(i), Len(arrline(i)) - 1))
Else
Lat = Val(arrline(i))
End If
Else
Lat = 0
End If
DataMask = DataMask + 8
Case "Lon1"
i = i + 1
' skip over any colons in the string
If arrline(i) = ":" Then
i = i + 1
End If
If InStr(1, arrline(i), "?") = 0 Then
If UCase(Right(arrline(i), 1)) = "E" Then
Lon = Val(Left(arrline(i), Len(arrline(i)) - 1))
ElseIf UCase(Right(arrline(i), 1)) = "W" Then
Lon = Val(Left(arrline(i), Len(arrline(i)) - 1))
* -1
Else
Lon = Val(arrline(i))
End If
Else
Lon = 0
End If
DataMask = DataMask + 16
End Select
Next i
Loop
If DataMask <> 63 Then
Errors = Errors + 1
Else
SQL = "INSERT into tblParsedData values (" & CollarNo & ", """ &
ObsDate & """, #" & ObsTime & _
"#, """ & ObsDate & " " & ObsTime & """, """
& LC & """, " & Lat & ", " & Lon & ", " & Act & ", -1, 0)"
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
End If
ParsedRows = DLookup("count(*)", "tblParsedData")
If ParsedRows = 0 Then
lstErrors.AddItem ("WARNING: " & FileSpec & " - 0 records parsed")
Else
lstErrors.AddItem (FileSpec & " - " & ParsedRows & " records parsed.
Errors: " & Errors)
End If
End Sub
The Above code parses and does other things to a file type looking like
this...
07711 Date : 01.12.04 00:08:20 LC : 1 IQ : 56
Lat1 : 75.690N Lon1 : 99.232W Lat2 : 69.689N Lon2 : 133.519W
Nb mes : 008 Nb mes>-120dB : 000 Best level : -129 dB
Pass duration : 504s NOPC : 2
Calcul freq : 401 649012.9 Hz Altitude : 53 m
01 00 00 00
07711 Date : 01.12.04 00:55:00 LC : 0 IQ : 55
Lat1 : 75.668N Lon1 : 99.313W Lat2 : 81.588N Lon2 : 74.534W
Nb mes : 005 Nb mes>-120dB : 000 Best level : -129 dB
Pass duration : 504s NOPC : 3
Calcul freq : 401 649096.8 Hz Altitude : 55 m
00 00 465 00
07711 Date : 01.12.04 00:56:24 LC : Z IQ : 00
Lat1 : ??????? Lon1 : ???????? Lat2 : ??????? Lon2 : ????????
Nb mes : 001 Nb mes>-120dB : 000 Best level : -134 dB
Pass duration : ? s NOPC : ?
Calcul freq : 401 650000.0 Hz Altitude : 0 m
01 00 00 00
07711 Date : 01.12.04 01:52:24 LC : 2 IQ : 55
Lat1 : 75.676N Lon1 : 99.223W Lat2 : 64.121N Lon2 : 168.831W
Nb mes : 006 Nb mes>-120dB : 000 Best level : -134 dB
Pass duration : 448s NOPC : 3
Calcul freq : 401 649167.3 Hz Altitude : 57 m
00 00 435 00
07711 Date : 01.12.04 02:36:44 LC : Z IQ : 00
Lat1 : ??????? Lon1 : ???????? Lat2 : ??????? Lon2 : ????????
Nb mes : 010 Nb mes>-120dB : 000 Best level : -134 dB
Pass duration : 616s NOPC : 0
Calcul freq : 401 650000.0 Hz Altitude : 1548 m
01 00 00 00
07711 Date : 01.12.04 02:40:56 LC : A IQ : 04
Lat1 : 75.693N Lon1 : 99.198W Lat2 : 67.303N Lon2 : 144.014W
Nb mes : 003 Nb mes>-120dB : 000 Best level : -132 dB
Pass duration : 112s NOPC : 2
Calcul freq : 401 649054.4 Hz Altitude : 57 m
01 00 00 00
I have a slightly new format in the text that I would want changes made to
the code to accomodate. The new kind of data looks like this....
21042 Date : 04.12.04 14:22:10 LC : Z IQ : 00
Lat1 : ??????? Lon1 : ???????? Lat2 : ??????? Lon2 : ????????
Nb mes : 001 Nb mes>-120dB : 000 Best level : -133 dB
Pass duration : ? s NOPC : ?
Calcul freq : 401 650000.0 Hz Altitude : 0 m
21875 21875
21042 Date : 04.12.04 14:42:03 LC : 2 IQ : 66
Lat1 : 75.139N Lon1 : 99.819W Lat2 : 85.660N Lon2 : 119.788W
Nb mes : 006 Nb mes>-120dB : 000 Best level : -130 dB
Pass duration : 580s NOPC : 3
Calcul freq : 401 648053.5 Hz Altitude : 19 m
21875 21875
21042 Date : 04.12.04 14:56:02 LC : 3 IQ : 66
Lat1 : 75.143N Lon1 : 99.849W Lat2 : 79.346N Lon2 : 119.014W
Nb mes : 004 Nb mes>-120dB : 000 Best level : -123 dB
Pass duration : 451s NOPC : 3
Calcul freq : 401 648052.8 Hz Altitude : 17 m
21875 21875
21042 Date : 04.12.04 15:06:14 LC : B IQ : 00
Lat1 : 75.152N Lon1 : 99.743W Lat2 : 60.309N Lon2 : 6.807W
Nb mes : 002 Nb mes>-120dB : 000 Best level : -133 dB
Pass duration : 129s NOPC : 1
Calcul freq : 401 648052.8 Hz Altitude : 748 m
21875 21875
21042 Date : 04.12.04 15:55:42 LC : 0 IQ : 56
Lat1 : 75.098N Lon1 : 99.720W Lat2 : 77.769N Lon2 : 112.993W
Nb mes : 007 Nb mes>-120dB : 000 Best level : -133 dB
Pass duration : 387s NOPC : 2
Calcul freq : 401 648098.7 Hz Altitude : 24 m
21875 21875
21042 Date : 04.12.04 16:19:22 LC : 2 IQ : 65
Lat1 : 75.148N Lon1 : 99.848W Lat2 : 83.897N Lon2 : 69.202W
Nb mes : 004 Nb mes>-120dB : 000 Best level : -133 dB
Pass duration : 258s NOPC : 2
Calcul freq : 401 648160.6 Hz Altitude : 17 m
21875 21875
I guess there is no more "ACT" portion. I just need the two numbers on the
bottom in their own respective columns.
Thanks for any suggestions ahead of time
Eskimo