Import Text file into multiple tables based on key

[TaylorTot]

I have a text file that I need to import into access, however the file is "stacked". Below is an example of my data

|C1.|FW|Ver.|Seg|Seq|Segmentdata
|900|2222222222 | |BBB1| 13|111111 111 111 1
|900|2222222222 | |BBB2| 13|5555 6/2/2008 11 1 222 222

Outlook should look like:

Table 1 (based on Seg Key) BBB1
Invoice Test1 Test2 Final
111111 111 111 1

Table 2 (based on Seg Key) BBB2
OrderNum Date Price Quantity PricePer Value
5555 6/2/2008 11 1 222 222

I think I need to create a loop based on the Seg Key and have a lookup table for the different headers.

Anyhow, I really don't know how to approach this but would appreciate any help.

 

[Andrzejek]

Check your help for Split function

You can Split your line of Text with "|" as delimiter:

Dim str() As String

str = Split(strLineOfText, "|")

So your 5th element (0 based) will have "111111 111 111 1" in it.

If you Split it again with a space as a delimiter, you will get array of 4 element:

Dim strX() as String

strX = Split(str(5), " ")

And insert them in your table(s).
3rd element of your str will point you to the right table.


Have fun.

---- Andy