Parsing text files

G

Guest

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
 
J

John Nurick

It looks as if the numbers will always be 21875, so you can just
hard-code them into the SQL INSERT statement.

If that's not the case, which part of the task is causing you problems?
 
G

Guest

Hi John,

When I run the code, it does not generate any errors, but in the table where
I usually get the parsed text, I get no records.

I think the changes required have to consider the difference in the
structure of the two text files. In the old structure where the code worked,
there are 4 numbers in the last line of each block. ie 00 00 128 180

In the new structure, the last line of each block has just two numbers, it
looks like both have 5 digits. The numbers are not always 21875. I just took
a sample out of a bigger file where there are different numbers.

The part I think that needs modification is in the part below where it
says....

' 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

there might be other parts of the task that need to be changed such as the
elimination of empty spaces.

I have a bit of an idea now how VB Code works, but this is a bit more
complex than I can handle.

Thanks for the help.

Eskimo
 
J

John Nurick

I'm sorry, I don't have time to master nearly 200 lines of quite obscure
code. It doesn't help that you're not certain of the actual change(s) in
the format of the text file.
I guess there is no more "ACT" portion. I just need the
two numbers on the bottom in their own respective columns.

Probably what's needed is

1) Certainty about the changes in the format and how the code needs to
respond. The present code appears to handle records that have between 4
and 6 values on the last line; will such records continue to be
encountered, or will they all now have just two values? What happens to
the Act field? Does it have to be give a default value (Null? 0?) or
will it be deleted from the table? What about the last fields in the
present table, which are now given values of 0 and -1: are these columns
now to be used for the two numbers in the new last row, or are new
columns needed? If so, do they go after the last existing column? What
are their names? And so on.

2) Option Explicit at the start of the module: the present code has
undeclared variables in it.

3) a new chunk of code to handle the case

If CollarNo <> "" And UBound(arrline) = 1 Then

by assigning arrline(0) and arrline(1) to variables for the two new
fields.

4) modifications to the statement(s) that build the SQL statement to
take into account the new fields and the fate of Act.

5) If Act is dead, remove other references to it, including the
existing
If CollarNo <> "" And UBound(arrline) = X Then
...
End If
blocks.
 
G

Guest

Hi John,

Your right, the nature of this problem seems to be out of the scope of what
help that I should be soliciting on this site. The code is longer than I
posted because it is in an event procedure in a botton on a form to perform
several tasks, not just the parsing of the text.

I will ask for help from the person who wrote the routine. He is away for
meetings and I needed something in the next two days. He will be back
shortly and can wait till he returns.

Thank you for your time anyway and we'll talk to you soon. I will make sure
to post what changes he had to close this question.

Eskimo
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top