Importing delimited text files

[Steve S.]

Hi,

I'm hoping someone can point me in the right direction as I'm pulling
my hair out in frustration!

I'm using Access 97 and am trying to import several thousand comma
delimited text files into Access that will come to us in batches. I
can accomplish this using application.filesearch to search in a
specified directory to find all the files and then the following to
import the files:

For i = 1 To .foundfiles.Count
DoCmd.TransferText acImportDelim, "import_final", "main table",
..foundfiles(i)
next i

This works fine but I want to record some additional info in the "main
table" that's not in the text file that's being imported - i.e.
filename, date and time of import, etc. I can't see how I can do this
using TransferText. The help files talk about using DAO instead of
TransferText but I've not been able to find much in the way of how to
use this/get it to work - I'm a bit lost on how to set up the
connection, use the schema, etc.

Alternatively, I could write this additonal data to an "import logs"
table but then I'd need the primary key (this is an autonumber field)
of the record in the "main table" as it's imported by TransferText. I
thought I could just use the following (rstfindkey is recordset
pointing to Main Table) to get the last used primary key and then just
increment it in the above for next loop and write it to the table:

With rstFindKey
.Bookmark = rstFindKey.LastModified
TableID = rstFindKey!primary_key
End With

But this doesn't work - if the last used primary key was 1000 and I
import 200 using the code at the top of page then run another import
later it'll still return 1000, not 1200. I assume that i somehow need
to use .Update after the Transfertext has finished to move the
pointer?

Any ideas/thoughts/better ways of doing this would be greatly
appreciated.

Thanks
Steve.

 

[Nikos Yannacopoulos]

Steve,

You could do it through DAO, but it's not the only way, and I'm not even
sure it's the most efficient one, as you would have to read line by line and
append record by record; I believe action queries are more efficient. So,
alternatively, you could use an intermediate table with all the fields of
the target table, and for each table:

* Clear the intermediate table, e.g.
CurrentDB.Execute "DELETE * FROM tblIntermediate"

* Do the TransferText into the Intermediate table

* Run an Update query to fill in the additional fields. e.g.
strSQL = "UPDATE tblIntermediate SET fldFileName = '" _
.foundfiles(i).Name & "', DTStamp = " & Now
CurrentDB.Execute strSQL

* Run an Append query to copy all records from the Intermediate table to the
Target one, e.g.
strSQL = "INSERT INTO tblTarget (Field1, Field2, ..., fldFileName,
DTStamp)" _
" SELECT (Field1, Field2, ..., fldFileName, DTStamp) FROM
tblIntermediate"

HTH,
Nikos