csv import

[Les]

Below is the first few lines of a csv file that I receive
every day. In the line that starts with an 'R' I would
like the 3rd value (1601) to be in each record as it's
own field. In an Access table one record would look like
this:
1601 T07 307 6 4
1601 T14 200 8 6

and so on. Can anyone help?
Les.

H,22/10/2003,08:00:13,,,,
R,5499 ,1601 ,N,H,22/10/2003,22/10/2003 10:00,
L, ,T07 ,307,6 ,4 ,
L, ,T14 ,200,8 ,6 ,
L, ,T17 ,302,4 ,4 ,
L, ,T18 ,201,25 ,24 ,
L, ,T25 ,107,6 ,6 ,
L, ,T28 ,203,18 ,13 ,
L, ,T30 ,204,33 ,25 ,
L, ,T32 ,304,21 ,13 ,
L, ,T37 ,206,53 ,40 ,
L, ,T37 ,208,142 ,76 ,
L, ,T38 ,105,101 ,75 ,
L, ,T41 ,104,73 ,46 ,
L, ,T41 ,106,37 ,22 ,
L, ,T43 ,101,45 ,28 ,
L, ,T49 ,209,7 ,7 ,
L, ,T50 ,204,7 ,7 ,
L, ,T51 ,102,25 ,23 ,
L, ,T54 ,303,50 ,42 ,
L, ,T54 ,305,13 ,12 ,
L, ,T56 ,202,7 ,6 ,
L, ,T57 ,205,59 ,56 ,
L, ,T57 ,207,17 ,14 ,
L, ,T59 ,309,5 ,5 ,
L, ,T74 ,209,21 ,19 ,
L, ,T76 ,310,3 ,2 ,
L, ,T77 ,209,1 ,1 ,
L, ,T81 ,200,6 ,6 ,
L, ,T86 ,300,13 ,12 ,
L, ,T87 ,308,10 ,10 ,
L, ,T88 ,300,2 ,2 ,
L, ,T92 ,200,1 ,1 ,
L, ,T93 ,306,10 ,10 ,
R,5499 ,1601 ,N,B,22/10/2003,22/10/2003 10:00,
L,BX10A,T10 ,05 ,79 ,37 ,
L,BX21A,T21 ,20 ,352 ,42 ,
L,BX22A,T22 ,54 ,80 ,31 ,
L,BX28A,T28 ,36 ,14 ,8 ,
L,BX60A,T60 ,15 ,141 ,62 ,
L,BX60A,T60 ,18 ,94 ,42 ,
L,BX60A,T60 ,21 ,18 ,11 ,
L,BX64A,T64 ,24 ,13 ,9 ,
L, ,T01 ,70 ,58 ,20 ,
L, ,T01 ,71 ,38 ,15 ,
L, ,T01 ,80 ,69 ,22 ,
L, ,T01 ,84 ,17 ,5 ,
L, ,T02 ,38 ,25 ,23 ,
L, ,T03 ,74 ,6 ,4 ,
L, ,T03 ,75 ,10 ,6 ,
L, ,T03 ,76 ,6 ,6 ,
L, ,T05 ,48 ,1 ,1 ,
L, ,T06 ,BM ,10 ,4 ,
L, ,T06 ,JR ,59 ,26 ,
L, ,T06 ,81 ,6 ,2 ,
L, ,T07 ,33 ,14 ,12 ,
L, ,T09 ,AG ,18 ,7 ,
L, ,T09 ,78 ,9 ,2 ,
L, ,T09 ,79 ,22 ,4 ,
L, ,T09 ,80 ,1 ,1 ,
L, ,T09 ,83 ,18 ,4 ,
L, ,T11 ,26 ,10 ,9 ,
L, ,T11 ,29 ,3 ,3 ,
L, ,T11 ,32 ,13 ,11 ,
L, ,T12 ,01 ,9 ,6 ,
L, ,T14 ,39 ,50 ,34 ,
L, ,T14 ,45 ,63 ,33 ,
L, ,T17 ,08 ,9 ,8 ,
L, ,T20 ,37 ,16 ,11 ,
L, ,T20 ,40 ,77 ,28 ,
L, ,T20 ,43 ,16 ,8 ,
L, ,T21 ,MB ,9 ,2 ,
L, ,T21 ,11 ,192 ,34 ,
L, ,T21 ,14 ,48 ,12 ,
L, ,T21 ,17 ,191 ,28 ,
L, ,T21 ,95 ,9 ,1 ,
L, ,T21 ,97 ,49 ,8 ,

 

[John Nurick]

Hi Les,

Access's built-in import routines can't handle this. An all-Access
solution means writing a VBA procedure that opens the textfile and reads
it line by line, assembling the records the way you want them, and
appending them to the table.

The alternative is to munge the textfile itself into a form that Access
can import. This can be done using practically any programming language;
I prefer Perl because it has powerful text-handling functions that do
more work in fewer lines of code. If Perl is installed on your computer
or if you download it from www.activestate.com, this script should do
the trick:

while (<>) { #loop through file
next if m/^H/; #skip "H" lines
@fields = split ","; #split into fields
foreach (@fields) { $_ =~ s/\s+$// }; #trim trailing spaces
if (@fields[0] eq "R") { #it's an "R" line
$third = $fields[2]; #store 3rd field
} else { #assemble record
$record = join ",", $third, @fields[2..5];
print "$record\n"; #write to output
}
}

 

[Les]

Thank you
Very useful information.
Les

-----Original Message-----
Hi Les,

Access's built-in import routines can't handle this. An all-Access
solution means writing a VBA procedure that opens the textfile and reads
it line by line, assembling the records the way you want them, and
appending them to the table.

The alternative is to munge the textfile itself into a form that Access
can import. This can be done using practically any programming language;
I prefer Perl because it has powerful text-handling functions that do
more work in fewer lines of code. If Perl is installed on your computer
or if you download it from www.activestate.com, this script should do
the trick:

while (<>) { #loop through file
next if m/^H/; #skip "H" lines
@fields = split ","; #split into fields
foreach (@fields) { $_ =~ s/\s+$// }; #trim trailing spaces
if (@fields[0] eq "R") { #it's an "R" line
$third = $fields[2]; #store 3rd field
} else { #assemble record
$record = join ",", $third, @fields[2..5];
print "$record\n"; #write to output
}
}

Below is the first few lines of a csv file that I receive
every day. In the line that starts with an 'R' I would
like the 3rd value (1601) to be in each record as it's
own field. In an Access table one record would look like
this:
1601 T07 307 6 4
1601 T14 200 8 6

and so on. Can anyone help?
Les.

H,22/10/2003,08:00:13,,,,
R,5499 ,1601 ,N,H,22/10/2003,22/10/2003 10:00,
L, ,T07 ,307,6 ,4 ,
L, ,T14 ,200,8 ,6 ,
L, ,T17 ,302,4 ,4 ,
L, ,T18 ,201,25 ,24 ,
L, ,T25 ,107,6 ,6 ,
L, ,T28 ,203,18 ,13 ,
L, ,T30 ,204,33 ,25 ,
L, ,T32 ,304,21 ,13 ,
L, ,T37 ,206,53 ,40 ,
L, ,T37 ,208,142 ,76 ,
L, ,T38 ,105,101 ,75 ,
L, ,T41 ,104,73 ,46 ,
L, ,T41 ,106,37 ,22 ,
L, ,T43 ,101,45 ,28 ,
L, ,T49 ,209,7 ,7 ,
L, ,T50 ,204,7 ,7 ,
L, ,T51 ,102,25 ,23 ,
L, ,T54 ,303,50 ,42 ,
L, ,T54 ,305,13 ,12 ,
L, ,T56 ,202,7 ,6 ,
L, ,T57 ,205,59 ,56 ,
L, ,T57 ,207,17 ,14 ,
L, ,T59 ,309,5 ,5 ,
L, ,T74 ,209,21 ,19 ,
L, ,T76 ,310,3 ,2 ,
L, ,T77 ,209,1 ,1 ,
L, ,T81 ,200,6 ,6 ,
L, ,T86 ,300,13 ,12 ,
L, ,T87 ,308,10 ,10 ,
L, ,T88 ,300,2 ,2 ,
L, ,T92 ,200,1 ,1 ,
L, ,T93 ,306,10 ,10 ,
R,5499 ,1601 ,N,B,22/10/2003,22/10/2003 10:00,
L,BX10A,T10 ,05 ,79 ,37 ,
L,BX21A,T21 ,20 ,352 ,42 ,
L,BX22A,T22 ,54 ,80 ,31 ,
L,BX28A,T28 ,36 ,14 ,8 ,
L,BX60A,T60 ,15 ,141 ,62 ,
L,BX60A,T60 ,18 ,94 ,42 ,
L,BX60A,T60 ,21 ,18 ,11 ,
L,BX64A,T64 ,24 ,13 ,9 ,
L, ,T01 ,70 ,58 ,20 ,
L, ,T01 ,71 ,38 ,15 ,
L, ,T01 ,80 ,69 ,22 ,
L, ,T01 ,84 ,17 ,5 ,
L, ,T02 ,38 ,25 ,23 ,
L, ,T03 ,74 ,6 ,4 ,
L, ,T03 ,75 ,10 ,6 ,
L, ,T03 ,76 ,6 ,6 ,
L, ,T05 ,48 ,1 ,1 ,
L, ,T06 ,BM ,10 ,4 ,
L, ,T06 ,JR ,59 ,26 ,
L, ,T06 ,81 ,6 ,2 ,
L, ,T07 ,33 ,14 ,12 ,
L, ,T09 ,AG ,18 ,7 ,
L, ,T09 ,78 ,9 ,2 ,
L, ,T09 ,79 ,22 ,4 ,
L, ,T09 ,80 ,1 ,1 ,
L, ,T09 ,83 ,18 ,4 ,
L, ,T11 ,26 ,10 ,9 ,
L, ,T11 ,29 ,3 ,3 ,
L, ,T11 ,32 ,13 ,11 ,
L, ,T12 ,01 ,9 ,6 ,
L, ,T14 ,39 ,50 ,34 ,
L, ,T14 ,45 ,63 ,33 ,
L, ,T17 ,08 ,9 ,8 ,
L, ,T20 ,37 ,16 ,11 ,
L, ,T20 ,40 ,77 ,28 ,
L, ,T20 ,43 ,16 ,8 ,
L, ,T21 ,MB ,9 ,2 ,
L, ,T21 ,11 ,192 ,34 ,
L, ,T21 ,14 ,48 ,12 ,
L, ,T21 ,17 ,191 ,28 ,
L, ,T21 ,95 ,9 ,1 ,
L, ,T21 ,97 ,49 ,8 ,

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.