Update Access Table with filename, modified file date/time and values contained in these flat files

P

Pierre Maricq

Hi,

I am using Win2000 and Access2000.

I need to build build a macro or write a VBA in Access that would screen all
files contained in a directory on my C drive (files are structrured DAT
files [flat files]) and update an Access table with the following
information:
(1) Filename (variable lenght)
(2) Modified File Date/Time
(3) Value in field 25: (that alphanumeric value is inside the file and is of
variable lenght)
(4) Date in field 62, second subfield (that value is inside the file). The
date is in format YYMMDD. So for instance, for ":62M:C050608EUR16695,64
", I need to capture 05/06/08

A single file might contain several times a combination of field 25 and
field 62. The table should be updated with each combination. I pasted a
sample of the aforementioned flat file below.

Can anyone advice on where to start? Thanks
Pierre
:20:9400105159192706
:25:00001959026
:28C:112/1
:60F:C050608EUR0,
:61:0506080608CR18,18NTRFNONREF//9991912629
/BAI/195/INCOMING WIRE
:86:/PT/DE/EI/JOHN SMITH GMBH, 1010 WIEN IHR KT 0000012345/20000
12345 NR.1234 /22.04.05/NETTOBTR. xx,xx
:61:0506080608CR34,92NTRFNONREF//9991912631
/BAI/195/INCOMING WIRE
:86:/PT/DE/EI/JOHN SMITH GMBH, 1010 WIEN IHR KT 0000012345/20000
12345 NR.1234 /22.04.05/NETTOBTR. xx,xx
:61:0506080608CR240,95NTRFNONREF//9991912633
/BAI/195/INCOMING WIRE
:86:/PT/DE/EI/JOHN SMITH GMBH, 1010 WIEN IHR KT 0000012345/20000
12345 NR.1234 /22.04.05/NETTOBTR. xx,xx
:61:0506080608CR261,90NTRFNONREF//9991912635
/BAI/195/INCOMING WIRE
:86:/PT/DE/EI/JOHN SMITH GMBH, 1010 WIEN IHR KT 0000012345/20000
12345 NR.1234 /22.04.05/NETTOBTR. xx,xx
:61:0506080608CR357,60NTRFNONREF//9991912637
/BAI/195/INCOMING WIRE
:86:/PT/DE/EI/JOHN DOE 1234567
:61:0506080608CR408,NTRFNONREF//9991912639
/BAI/195/INCOMING WIRE
:86:/PT/DE/EI/JOHN SMITH GMBH, 1010 WIEN IHR KT 0000012345/20000
12345 NR.1234 /22.04.05/NETTOBTR. xx,xx
:61:0506080608CR1127,92NTRFNONREF//9991912641
/BAI/195/INCOMING WIRE
:86:/PT/DE/EI/JOHN SMITH GMBH, 1010 WIEN IHR KT 0000012345/20000
12345 NR.1234 /22.04.05/NETTOBTR. xx,xx
:61:0506080608CR14246,17NTRFNONREF//9991912643
/BAI/195/INCOMING WIRE
:86:/PT/DE/EI/JOHN SMITH GMBH, 1010 WIEN IHR KT 0000012345/20000
12345 NR.1234 /22.04.05/NETTOBTR. xx,xx
:62M:C050608EUR16695,64
:86:/PG/1
:20:9400205159192706
:25:00001959026
:28C:112/2
:60M:C050608EUR16695,64
:61:0506080608DR16695,64NMSC490 0001234567//3377752425
/BAI/577/ZBA DEBIT
:86:/PT/DE/EI/123 1234567891
:62F:C050608EUR0,
:64:C050608EUR0,
:86:/PG/2L
 
D

Douglas J Steele

Use the Line Input # statement to read the text in line by line. Use a
combination of the Left, Mid and Right functions (and maybe InStr) to split
the row into its component parts.

Hopefully that's enough to point you in the correct direction.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pierre Maricq said:
Hi,

I am using Win2000 and Access2000.

I need to build build a macro or write a VBA in Access that would screen all
files contained in a directory on my C drive (files are structrured DAT
files [flat files]) and update an Access table with the following
information:
(1) Filename (variable lenght)
(2) Modified File Date/Time
(3) Value in field 25: (that alphanumeric value is inside the file and is of
variable lenght)
(4) Date in field 62, second subfield (that value is inside the file). The
date is in format YYMMDD. So for instance, for ":62M:C050608EUR16695,64
", I need to capture 05/06/08

A single file might contain several times a combination of field 25 and
field 62. The table should be updated with each combination. I pasted a
sample of the aforementioned flat file below.

Can anyone advice on where to start? Thanks
Pierre
:20:9400105159192706
:25:00001959026
:28C:112/1
:60F:C050608EUR0,
:61:0506080608CR18,18NTRFNONREF//9991912629
/BAI/195/INCOMING WIRE
:86:/PT/DE/EI/JOHN SMITH GMBH, 1010 WIEN IHR KT 0000012345/20000
12345 NR.1234 /22.04.05/NETTOBTR. xx,xx
:61:0506080608CR34,92NTRFNONREF//9991912631
/BAI/195/INCOMING WIRE
:86:/PT/DE/EI/JOHN SMITH GMBH, 1010 WIEN IHR KT 0000012345/20000
12345 NR.1234 /22.04.05/NETTOBTR. xx,xx
:61:0506080608CR240,95NTRFNONREF//9991912633
/BAI/195/INCOMING WIRE
:86:/PT/DE/EI/JOHN SMITH GMBH, 1010 WIEN IHR KT 0000012345/20000
12345 NR.1234 /22.04.05/NETTOBTR. xx,xx
:61:0506080608CR261,90NTRFNONREF//9991912635
/BAI/195/INCOMING WIRE
:86:/PT/DE/EI/JOHN SMITH GMBH, 1010 WIEN IHR KT 0000012345/20000
12345 NR.1234 /22.04.05/NETTOBTR. xx,xx
:61:0506080608CR357,60NTRFNONREF//9991912637
/BAI/195/INCOMING WIRE
:86:/PT/DE/EI/JOHN DOE 1234567
:61:0506080608CR408,NTRFNONREF//9991912639
/BAI/195/INCOMING WIRE
:86:/PT/DE/EI/JOHN SMITH GMBH, 1010 WIEN IHR KT 0000012345/20000
12345 NR.1234 /22.04.05/NETTOBTR. xx,xx
:61:0506080608CR1127,92NTRFNONREF//9991912641
/BAI/195/INCOMING WIRE
:86:/PT/DE/EI/JOHN SMITH GMBH, 1010 WIEN IHR KT 0000012345/20000
12345 NR.1234 /22.04.05/NETTOBTR. xx,xx
:61:0506080608CR14246,17NTRFNONREF//9991912643
/BAI/195/INCOMING WIRE
:86:/PT/DE/EI/JOHN SMITH GMBH, 1010 WIEN IHR KT 0000012345/20000
12345 NR.1234 /22.04.05/NETTOBTR. xx,xx
:62M:C050608EUR16695,64
:86:/PG/1
:20:9400205159192706
:25:00001959026
:28C:112/2
:60M:C050608EUR16695,64
:61:0506080608DR16695,64NMSC490 0001234567//3377752425
/BAI/577/ZBA DEBIT
:86:/PT/DE/EI/123 1234567891
:62F:C050608EUR0,
:64:C050608EUR0,
:86:/PG/2L
Click to expand...
 
Top