Search and extract data from TXT file

[Guest]

Hi,

I am trying to write some code to extract certain data from a number of text
files of the same layout and enter it into a table. An example of the text
file is shown below:

Monday, July 18, 20054:49 PM
CYCLE
START
M- 1041- 4843 PART 299T 1651 18 7

---------- --------- ---------- --------- ---------- ---------
PART 300 18 7 2005 16.51
---------- --------- ---------- --------- ---------- ---------
FEATURE 3
0FFSET 2
MAIN SPINDLE EXTERNAL FINISH TURN
X - .018 Y .000 Z - 2.999 SIZE 16.532
OLD WEAR OFFSET X - .056 Y .000 Z - .050 R .000
NEW WEAR OFFSET X - .088 Y .000 Z - .050 R .000

FEATURE CORRECT
---------- --------- ---------- --------- ---------- ---------
FEATURE 4
0FFSET 27
MAIN SPINDLE HORIZONTAL SPOT DRILL
X 32.995 Y - .033 Z .000 SIZE 2.980
OLD WEAR OFFSET X .000 Y .000 Z .301 R .000
NEW WEAR OFFSET X .000 Y .000 Z .291 R .000

FEATURE CORRECT

If I want to import the M number, M- 1041- 4843, the part number, 300, the
date, as well as the x, y, z and size, how could i go about it?

I'm working in Access 97

Thanks

 

[John Nurick]

Hi Pete,

It appears from your sample data that there can be two or more sets of X, Y,
Z and SIZE values per PART, and maybe more than one PART per M-number. Do
you want them all, or just the first PART number and first X, Y, Z and SIZE
for each M-number? What is the structure of the table(s) into which you want
to put the data?

The general idea is to open the file and read it line by line, taking action
on what you find. Use old-style Basic file I/O: the FreeFile() function and
the Open #, Line Input # and Close # statements.

It looks as if
-any line that begins with "M-" contains an M-number
-after you've found an "M-" line, a line that begins " PART"
contains a part number and date
-and then any lines that begin with "X " contain the other data until
you find another PART or M- line.

To extract the values from the lines, you can write code using functions
such as InStr() and Mid(), or you can use regular expressions. I'd prefer
the latter (at least for the " PART" and "X " lines); see e.g. the
rgxExtract() function at http://www.j.nurick.dial.pipex.com/Code/index.htm

Once you've extracted all the data values from one record in the text file
into VBA variables, use a recordset or SQL INSERT statement to append a
record to your Access table.

Then continue reading the text file until you find another "M-" line.