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

Mike wrote:

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

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.
 

--
John Nurick - Access MVP