Import custom text file

[spike250]

Hi,

I am pretty new to access and I am trying to import a custom text file in VBA, I have tried searching but most articles discuss delimited text files.

I'm using Access 2003

Text File contents

Line 1: Authorisation 1
Line 2: -----------------
Line 3: A,00.0000001.Y
Line 4: B,00.0000002.Z
Line 5: Authorisation 2
Line 6: ------------------
Line 7: C,00.0000003.F
Line 8: Authorisation 3
Line 9: ------------------
Line 10: C,12.0000005.B

There can be multiple numbers within each section i.e Line 3,4,7,10

I would like to import the data into a table like the following;
A,00.0000001.Y | Authorisation 1
B,00.0000002.Z | Authorisation 1
C,00.0000003.F | Authorisation 2
C,12.0000005.B | Authorisation 3
And so on - ignoring the --------- separator.

I hope someone could kindly help as I cannot figure out where to start

Many thanks

Spike
:banghead:

 

[StarGrabber]

Hi Spike,

if I understand you right, there are both, 'authorisations' with two numbers and some with one number. Therefore I suggest you use two tables, linked by an 1:n relation.

If you agree, you can try the code in attached database file. Otherwise you can easily change the code (and the tables of course).

To run the import you should set the cursor somewhere in the sub 'ImportTextFile()' and press F5. But before you do this, please delete all records in the tables (first in 'tblAuthNumbers').

If you need further help let us know.

StarGrabber

 

[Pat Hartman]

You will need to do this with code since the record format is non-standard and different for each record. Look up OPEN and INPUT for code examples that read text files. Your loop will need to examine the first position of each record to determine what record type you have and then add rows to your table accordingly.

 

[spike250]

Many thanks to StarGrabber and Pat Hartman, I managed to figure it out with your help.