Data import and Parse

I have to process delimited files on a regular basis and
would like to automate the process.
The incomming files have the following format:

|_12345678_____| Qty1 |
|___23456789___| Qty2 |
|___34567890___| Qty3 |
|_____45678901_| Qty4 |
|_____56789012_| Qty5 |
|___67890123___| Qty6 |
|___78901234___| Qty7 |

(this is a multi-level indented Bill Of Material report that is delimited with pipe symbols)

I need to turn this .txt file into a table with 3 fields:
Bill of material part number - BMPN
Component part number - CPN
Quanity - QTY

So the table would look like this:
|___BMPN__|___CPN___|Qty_|
|_12345678_|_23456789_|Qty2|
|_12345678_|_34567890_|Qty3|
|_12345678_|_67890123_|Qty6|
|_12345678_|_78901234_|Qty7|
|_34567890_|_45678901_|Qty4|
|_34567890_|_56789012_|Qty5|

I would love some advise from anyone with a clearer view of how to handle this than I.
Thanks,
Doc.

This is a bit dirty beause I was in a hurry, but you should get the general idea and be able to tidy it up from here.


Option Explicit

Private Sub Command1_Click()

Dim hInputFile As Integer
Dim strMyData As String
Dim varComponent() As String, i As Integer
Dim intStr As Integer, intEnd As Integer

hInputFile = FreeFile
Open "Input.txt" For Input As hInputFile

While Not EOF(hInputFile)
Line Input #hInputFile, strMyData
If Len(strMyData) >= 22 Then
intStr = 0: intEnd = 0
For i = 2 To Len(strMyData)
If IsNumeric(Mid(strMyData, i, 1)) Then
If intStr = 0 Then
intStr = i
ReDim Preserve varComponent(i \ 2)
End If
Else
If intStr > 0 Then
intEnd = i - intStr
Exit For
End If
End If
Next i
varComponent(UBound(varComponent)) = Mid(strMyData, intStr, intEnd)

If UBound(varComponent) > 1 Then
Debug.Print "|_" & varComponent(UBound(varComponent) - 1) & "_|_" & varComponent(UBound(varComponent)) & "_|" & Mid(strMyData, 17)
End If
End If
Wend
Close hInputFile

End Sub

Gives the results:
|_12345678_|_23456789_| Qty2 |
|_12345678_|_34567890_| Qty3 |
|_34567890_|_45678901_| Qty4 |
|_34567890_|_56789012_| Qty5 |
|_12345678_|_67890123_| Qty6 |
|_12345678_|_78901234_| Qty7 |


Just noticed: not actually in the same order but same results in general. Perhaps you may wish to sort ?

Hope it helps ...