Importing an unconventional text data file

[d-mac]

I am trying to format/import a 'capture' file into a manageable table, but the way the data is structured, it is giving me several issues.

The file (after conversion to notepad) looks like this:

Page 1 10:26:17 28 OCT 2002


History : 64684654646864
Closed 03SEP02
CUSTOMER NAME SMITH,JOHN D
ADDRESS 6846 28TH ST NW APT 5
CITY FEDERAL WAY
STATE WA
ZIP 98565
YR 01
MAKE GMC
OP-CODE 9
MILEAGE 23546

History : 68468468564
Closed 03SEP02
CUSTOMER NAME Jones,JOHN D
ADDRESS 6834 28TH ST NW APT 5
CITY FEDERAL WAY
STATE WA
ZIP 98565

Page 2 10:26:34 28 OCT 2002

YR 01
MAKE VOLVO
OP-CODE 9
MILEAGE 23626

1. The records as you can see are listed vertically and I can't figure out a way to get them into a table with these field names into the proper place.
2. There are hundreds of records and the page breaks happen at different places within every other record or so.
3. I've tried converting it to comma delimited and space delimited, but haven't been able to do both.
4. The CUSTOMER NAME ends up on 3 separate fields as I would like, but it separates the address into several fields - number, street name, ST, APT, #.
5. When a city contains a space, multiple fields are made.

I thank you in advance for any tips on getting this project on the road! Can Access handle this?

 

[pdx_man]

You are going to have to create another file if you want to import. How's your VBA skills? Another way would be to Open the file for Input as #1, then look for the keywords to populate your recordset.

This should get you started, you have to test to get rid of the Page line, but here is a push:

Public Sub tester()
Dim MyStr As String
Dim dbs As Database
Dim rst As Recordset

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("MyTable")
    Open "c:\Test.txt" For Input As #1
    Do While Not EOF(1)
        Line Input #1, MyStr
        If Left(MyStr, 7) = "History" Then
            rst.AddNew
            rst!Hist = Mid(MyStr, 10)
:
:
:
:
    rst.Update
    Loop
Close #1
End Sub