External Data Parsing

[PaulO]

ps It's still true that for all Header records the first 19 characters will be exactly the same ...

 

[Beetle]

So if the following conditions will always be true;

1) A training header row will always have a "T" at position 56
2) A race header row will always have a space at position 58
3) The race data rows will never meet either of the above conditions

then we can rewrite the code as follows, which is beneficial because it eliminates the need to open the file just to determine what constitutes a header, then close and re-open it for the loop.

ps It's still true that for all Header records the first 19 characters will be exactly the same

But if the above conditions are true then we don't need this to determine the header row.

Here is modified code;

Dim lngCount As Long
Dim rs As DAO.Recordset
Dim strLineData As String
Dim strFileName As String
Dim strPrefix As String
Dim strDelete As String
 
strFileName = "C:\Documents and Settings\sbailey.SUMMIT\My Documents\Access\Misc\racedata.txt"
 
'Delete any existing records from the Temp table
strDelete = "Delete * From tblTemp;"
CurrentDb.Execute strDelete, dbFailOnError
 
'Set the record counter to 0
lngCount = 0
 
'Open the recordset
Set rs = CurrentDb.OpenRecordset("tblTemp")
 
'Open the file and read each line, inserting new records in the table as we go
Open strFileName For Input As #1
Do While Not EOF(1)
Line Input #1, strLineData
lngCount = lngCount + 1
If Mid(strLineData, 56, 1) = "T" Then
    strPrefix = "T"
ElseIf Mid(strLineData, 58, 1) = " " Then
    strPrefix = Mid(strLineData, 56, 2)
End If
 
With rs
    .AddNew
    !LineNumber = lngCount
    !LinePrefix = strPrefix
    !LineText = strLineData
    .Update
End With
Loop
 
'Close the file and the recordset
Close #1
rs.Close

A test run results in the data set shown in the image.

 

[PaulO]

Sean ... The output looks good ... can't wait to give it a roadtest!

 

[PaulO]

Hi Sean ... output works fine thanks very much ...

But you know I'm such a dummy I didn't spot the really obvious identifier for Header records:-

If a Training flight the LAST character in the text string will be "T"
If a Race flight the LAST character in the text string will be "R"

Whilst we'll end up with the same output, I guess it'd be better to re-write the code in that manner? Is that possible?

Many thanks for all your support!

 

[Beetle]

If a Training flight the LAST character in the text string will be "T"
If a Race flight the LAST character in the text string will be "R"

Whilst we'll end up with the same output, I guess it'd be better to re-write the code in that manner? Is that possible?

Yes, if you think it will be more reliable to do it that way it just requires a simple modification of two lines of the code to use the Right function instead of Mid. Here is the modified code with the changes in red.

Dim lngCount As Long
Dim rs As DAO.Recordset
Dim strLineData As String
Dim strFileName As String
Dim strPrefix As String
Dim strDelete As String
 
strFileName = "C:\YourFolder\racedata.txt"
 
'Delete any existing records from the Temp table
strDelete = "Delete * From tblTemp;"
CurrentDb.Execute strDelete, dbFailOnError
 
'Set the record counter to 0
lngCount = 0
 
'Open the recordset
Set rs = CurrentDb.OpenRecordset("tblTemp")
 
'Open the file and read each line, inserting new records in the table as we go
Open strFileName For Input As #1
Do While Not EOF(1)
Line Input #1, strLineData
lngCount = lngCount + 1
[COLOR=red]If Right(strLineData, 1) = "T" Then[/COLOR]
    strPrefix = "T"
[COLOR=red]ElseIf Right(strLineData, 1) = "R" Then[/COLOR]
    strPrefix = Mid(strLineData, 56, 2)
End If
With rs
    .AddNew
    !LineNumber = lngCount
    !LinePrefix = strPrefix
    !LineText = strLineData
    .Update
End With
Loop
 
'Close the file and the recordset
Close #1
rs.Close

 

[PaulO]

Thanks once again, Sean