Parsing one delimited text row into multiple records

[ChrisPy]

I'm a frequent reader of these forums and have gotten great info over the
years but this is my first post...

I have a pipe (|) delimited text file that I need to regularly import into
Access. Each row starts with unique identifiers followed by 1 to 40
activities. I need each activity to end up as a new record along with the
unique data.

For Example...

Text file:
ID|NAME|ACTIVITY1|ACTIVITY2|ACTIVITY3|...
1|Chris|Sweep|Mop|Scrub|Eat
2|John|Eat|Mow|Drive

Access Table Result:
ID NAME ACTIVITY
1 Chris Sweep
1 Chris Mop
1 Chris Scrub
1 Chris Eat
2 John Eat
2 John Mow
2 John Drive

Any suggestions you have would be greatly appreciated

 

[John Nurick]

Hi Chris,

I'd do this by pre-processing the text file into something that Access
can import. Here's a Perl script that converts your sample into a
tab-delimited text file with three fields:

while (my $line = <>) {
next if $. == 1; #skip first line
chomp $line; #strip CRLF from end of line
my ($ID, $name, $activities) = split '\|', $line, 3;
print "$ID\t$name\t$_\n" foreach split '\|', $activities;
}

Doing it in VBA takes a lot more typing but the logic is the same.
Incomplete air code:

Dim strLine As String
Dim Fields as Variant
Dim Activities As Variant
Dim Activity As Variant

...

Line Input #fIn, strLine
Fields = Split("|", strLine, 3)
Activities = Split("|", Fields(2))

For Each Activity In Activities
Print #fOut, Fields(0), Fields(1), Activity
Next

Close #fIn
Close #fOut