Import Multiple Text Files

[TechTutors]

Hello All,

Thank you for helping me with the following issue!

I have a large amount of *.txt files (60+)that I need to import and process
in Access. Each file has a different name. Here is what I'd like to do...

Run code that will import the file onto the table using a saved Spec... run
a few queries... then wipe the table... import the next file onto the
table... run the same few queries... then wipe the table... and so on and so
forth.

Any suggestions?

Thanks!

 

[Dale Fye]

Are all of the text files in the same directory? Are they the only files in
the directory?

If so, you could create a loop using the Dir function (there is a good
example in the Access Help files) to get the names of the files in the
directory. Then, you could use the TransferText method, with your import
specification, run the queries you want, then loop to the next text file.

I would probably create the temp table that you want to import into and just
delete all of the records from it as soon as you finish each part of the
loop. That is easier than having to delete the table object and do the
import into a new table. It, along with your import spec will help ensure
that Access formats the fields the way you want it to when it imports the
data.

You might also want to consider moving the file to a new "\processed\"
folder after you have completed the import of each table.

HTH
Dale

 

[TechTutors]

Thanks!

I looked up the Dir funtion in access, but I didn't get much out of it....
esp about loops. Yes, all the files are in one directory.

I have the queries running in a macro ((the final query wipes the import
table)). So essentially, I'd like to transfer text... run macro... repeat...
The problem is that the file names are all different... So I'm not sure what
that code would look like in a module.

I'm an amatuer VB coder :-/

Thanks for your help tho!

 

[Dale Fye]

The following code will loop through all of the files (those that are not
system or hidden or directories) on a given path and display them in the
debug window.

Public Sub LoopFiles()

Dim strFilename As String

strFilename = Dir("C:\Temp\*.*", vbNormal)
Do While Len(strFilename) > 0
Debug.Print strFilename
strFilename = Dir()
Loop

End Sub

What you need to do is take this a couple of steps further.
1. You will probably want to change the file extension in the first line
that references strFilename to .txt, since you will be importing text files.
2. You then need to insert a docmd.TransferText command, with the
appropriate parameters immediately after the Debug line
3. I would then run your series of queries using the currentdb.execute
method, passing it the query names as the argument
4. Finally, I would run the query to delete the records from the temp table
(also using the currentdb.execute method)
5. Lastly, I would probably use the NAME statement to rename the file, this
might take a couple of lines:

strOldPath = Left(strFilename, instrrev(strFilename, "\"))
strNewPath = strOldPath & "processed\"
strNewFilename = strNewPath & Mid(strFilename, instrrev("\") + 1)
NAME strFilename as strNewFilename

All of this should go before the strFilename = Dir( ) line.

HTH
Dale