Parsing a text file

[Guest]

I know there isn't room here for a complete answer, but I'm hoping someone
can direct me to a website, or even resources like books or training CD's
that can help me with a problem. We have an old mainframe that still works in
the realm of printing Green Bar reports. An upgrade a few years ago has the
mainframe outputting the data to "virtual" greenbar reports that can be read
on a personal computer. The problem is, that data isn't mush good for broad
analysis. So I need to read the data into a database, one report at a time,
so we can analyze the data once it is in one spot. To do this, I need to be
able to read a text file, parsing it one line at a time and storing each
value found to be pertinent into an Access database.

I actually know how to do this in something like QuickBasic, but not in the
Visual basic that is used with Access. Where could I learn how to do this? I
have three books on Access, but none of them goes into enough detail to tell
me how to accomplish this. I understand there is a commercial program out
there called Monarch that does this, but they want $600 a copy and that's too
rich for my blood.

Any help would be appreciated.

---Mike

 

[Guest]

Mike,

If these files are all in the same format, you can create a destination
table that contains fields for all of the data, and maybe a couple additional
fields to identify the file you got the data from. This probably needs to be
a temporary table, as there will probably be some transformations that you
need to do to get the data from the report format into the actual format you
want.

Then, create an import specification (more on this in a second) that maps
the fields from your reports to the fields in your table. Once you have this
done, you can write some code that loops through your files, and uses the
DoCmd.TransferText method to import the data from the text file to the
temporary datafile. Then, transform it to get it into the final format you
want it in.

HTH
Dale

 

[Gary Walter]

I know there isn't room here for a complete answer, but I'm hoping someone
can direct me to a website, or even resources like books or training CD's
that can help me with a problem. We have an old mainframe that still works
in
the realm of printing Green Bar reports. An upgrade a few years ago has
the
mainframe outputting the data to "virtual" greenbar reports that can be
read
on a personal computer. The problem is, that data isn't mush good for
broad
analysis. So I need to read the data into a database, one report at a
time,
so we can analyze the data once it is in one spot. To do this, I need to
be
able to read a text file, parsing it one line at a time and storing each
value found to be pertinent into an Access database.

I actually know how to do this in something like QuickBasic, but not in
the
Visual basic that is used with Access. Where could I learn how to do this?
I
have three books on Access, but none of them goes into enough detail to
tell
me how to accomplish this. I understand there is a commercial program out
there called Monarch that does this, but they want $600 a copy and that's
too
rich for my blood.

Hi Mike,

How big are these files and do they end each line with CRLF?

In addition to Dale's sage help, here be something that might help
you get *started*.

http://www.freevbcode.com/ShowCode.Asp?ID=3280

It will pull all, or parts of, a file into a temp table.

From there you can better identify how to further parse each
line (saved as record in temp table) into your final table.

The point being that I always found it easier to work
with a small file initially, plus it is good to know *what*
you are working with (strange characters, no CR, etc.)

Maybe it might help...

good luck,

gary

 

[Dale Fye]

After re-reading my post, I realized that I didn't talk any more about
"import specifications". Are you familiar with these, or would you like
some help in that area?

Dale

 

[John Nurick]

Hi Mike,

1) Might be worth checking out TextPipe (www.datamystic.com): costs
much less than Monarch and there's a downloadable evaluation licence.

2) When it comes to rolling your own, VBA isn't all that different
from QuickBasic: you've got Left() and Right() and Mid() and Instr()
and not much more. It'd be something like this:


Dim R As DAO.Recordset
Dim strLine As String
Dim lngFN As Long
Dim strSomeField As String
Dim strOtherField As String

lngFN = FreeFile()
Open "D:\Folder\File.txt" For Input As #lngFN

'Open recordset on table
Set R = CurrentDB.OpenRecordset("MyTable")

Do Until EOF(lngFN)
'read line from file
Line Input #lngFN, strLine

'extract field value(s) from strLine
strSomeField = probably some expression using Mid()
strOtherField = another expression

'Append new record to table
With R
.AddNew 'new record
.Fields("SomeField").Value = strSomeField
.Fields("OtherField").Value = strOtherField
...
.Update 'save the record
End With

...

Loop

Close #lngFN
R.Close

3) Or there are lots of free text file tools, originating in the Unix
world and ported to Windows. I use the Unix textutils
(http://unxutils.sourceforge.net) and Perl.