Retain Sequence in Table from Original Imported Text File

[shaggy]

I use a macro to import a text file into a table. Each line of the text file contains 1 record. The type of record is identified by the 1st 3 characters (MR#). Multiple records contain related data but the only way to define the relationship is by the sequence of records in the original text file.
After import I want to import the data into tables for each record type and assign fields with unique values which link the related records together.

MR4 = Header record (1st record and only 1 in file)
MR1 = Route Header
MR2 = Account Info
MR3 = Meter Info

There are multiple accounts per route and multiple meters per account.

I wrote the 2 functions below to identify route and account relationships.

Public Static Function RtIdent(Ident As String) As Long
Dim X As Long
If Ident = "MR4" Then
X = 0
ElseIf Ident = "MR1" Then
X = X + 1
End If
RtIdent = X
End Function

Public Static Function AcctIdent(Ident As String) As Long
Dim X As Long
If Ident = "MR4" Then
X = 0
ElseIf Ident = "MR2" Then
X = X + 1
End If
AcctIdent = X
End Function


The data should look something like this

Record Route Account
MR4 0 0

MR1 1 0
MR2 1 1
MR3 1 1
MR2 1 2
MR3 1 2
MR3 1 2
MR2 1 3
MR3 1 3
MR1 2 3
MR2 2 4
MR3 2 4
MR2 2 5
MR3 2 5
MR3 2 5

The functions seem to work correctly, but the problem is the imported data doesn't seem to keep the sequence of the text file from which it was imported thus destroying the record relationships.

I'm thinking the only way to do this may be using VBA to import the file.

Any suggestions on how to import the data and retain the record sequence?

 

[gemma-the-husky]

have a sequence number in the text file

the sequence shouldnt matter with properly normalised data - so you need to address this issue

 

[shaggy]

How do I add sequence numbers to the text file? VBA? The text file is the output from an application. The format of the file is fixed without a programming change to the application.

 

[gemma-the-husky]

can you derive the order from other info in the file

alternatively load it into excel, add a id column, and import from that

 

[shaggy]

There's no way to determine the original order after it has been scrambled.
I hadn't thought of importing it 1st into excel. I'll try that next week.

Thanks for the help