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

[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
:

[Ozzone]

(1) Filename (variable lenght)

Look up help in Access regarding the FileSearch object With VBA you
can lookup the file names in a directory.

(2) Modified File Date/Time

The LastModified property only allows you to specify a parameter for
filtering.

(3) Value in field 25: (that alphanumeric value is inside the file and
is of
variable lenght)

I'm confused a little here. Is each entry in the file one field?
Based on your sample data, I can't tell.

(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

Again I'm not sure what you mean by subfield. Your explanation might
make sense to you, but I'm confused without seeing the table structure
and the actual data file.

You can do string parsing to strip out the date as long as there is a
common setup for each field. It appears that the comma (,) denotes the
end of the alphanumeric value containing the date you want, so you
could start at the colon behind the number, check for an alpha
character afterwards and then strip out the date using a mid function.

It's very doable, but not easy unless you are good coder.

Sorry I couldn't be more help.