Help on a ETL problem

[phient]

Hi, I'm new to VBA in Access and need help/suggestions on how to do the following:

I have a large text file that I'm importing into Access that has 5 groups of data in 2 fields (Header and Data) without a primary key or any way to associate the following data to the group/report header.

Column1 Column2
Row1:[Data Group1]
Row2:Header1 Data1
Row3:Header2 Data2
Row4:etc...
Row5:Empty Row
Row6:[Data Group2]
Row7:Header1 Data1
Row8:Header2 Data2
Row9:etc...
Row10:Empty Row
Row11:etc....70,000 rows

I am trying to split the groups into 5 different tables and transpose the Header and Data

 

[gemma-the-husky]

how are you importing/reading the file?

 

[phient]

I'm using the Get External data in Access which pulls the text file and splits the field headers into field 1 and the associated header's data into field 2. I'm trying to grab each data group and append to an appropriate table that has the headers already as field names:

[Group 1]
Header 1 Data1
Header 2 Data2
etc...

Transposed to table "Group 1" with the following fields
ID (Primary Key) Header1 Header2
1 Data1 Data2
2 Data1 Data2

Thanks for any suggestions.