parse a text file

[Karen]

We have text files that are the saved information from a meter dumping into
hyperterminal on a winXP machine. The data is generated by the meter itself.
Most of the text files conform to a standard layout. Can I use Access to
parse the text files and put that data into a table? How?

Also, once we've designed the app, we are going to want to capture this data
directly from the meter, without hyperterminal and the text file. How do I
do that?

Thanks for your help,
Karen


Sample of one of the text files:
Meter Name SN 2833 Software Revision VER 2.3 xxxxxx Company anytown, ST
CH1 520nm 05-01-05 13:07:17 27160
CH2 430nm 05-01-05 13:07:21 25984
CH3 620nm 05-01-05 13:07:24 33527
CH4 570nm 05-01-05 13:07:27 25775
CH5 520nm 05-01-05 13:07:38 30942
CH6 430nm 05-01-05 13:07:42 16717
CH7 620nm 05-01-05 13:07:47 44984
CH8 570nm 05-01-05 13:07:51 34286
CH9 520nm 05-01-05 13:08:04 33221
CH9 520nm 05-01-05 13:08:05 05811
CH10 430nm 05-01-05 13:08:17 16617
CH10 430nm 05-01-05 13:08:19 00023
CH11 620nm 05-01-05 13:08:30 41099
CH11 620nm 05-01-05 13:08:32 56669
CH12 570nm 05-01-05 13:08:42 33030
CH12 570nm 05-01-05 13:08:43 38522
test name concentration units abs %T WL blank sample sec min hrs day mo year
101 %T/ABS 430 1.5483e+00 1.548 2.8 430 25868 732 15 09 13 01 05 05
102 %T/ABS 520 1.6773e+00 1.677 2.1 520 27634 581 28 09 13 01 05 05
103 %T/ABS 570 7.4955e-01 0.750 17.8 570 25723 4579 40 09 13 01 05 05
104 %T/ABS 620 1.3377e+00 1.338 4.6 620 33365 1533 54 09 13 01 05 05

 

[Karen]

We have come up with a solution for the parsing of the existing files. We
are going to use the copy command at the command prompt and append the text
files together. Then, in Word, we are going to manipulate the file so the
output from each meter is on one long comma delimited line. Then we can
import that big file into Access.

But, if anyone knows how this can be done in Access alone, please let me
know.
Thanks,
Karen

 

[Dirk Goldgar]

We have come up with a solution for the parsing of the existing
files. We are going to use the copy command at the command prompt
and append the text files together. Then, in Word, we are going to
manipulate the file so the output from each meter is on one long
comma delimited line. Then we can import that big file into Access.

But, if anyone knows how this can be done in Access alone, please let
me know.

You could easily write code -- well, easily if you're comfortable with
code -- to open each text file and read it line by line, parsing the
lines either positionally (using Mid() the function) or by using the
Split() function to use the space character as a delimiter, which ever
is appropriate for the type of line. Then you could add records to the
appropriate table(s) using a DAO Recordset.

To read the text file you could use the built-in VBA Open, Line Input,
and Close statements. The Dir() function can be used if need be to loop
through all the text files in a particular folder.