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

 

[spike250]

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