Importing all .txt files in folder into table

[Guest]

I have several hundred .txt files that are ~ deliminted in a folder. What is
the code to import all of them to one table.

I'm new to Access programming and would apprecite your help.

Thanks!

 

[Guest]

The following function will accomplish what you want but it does not include
any error handling:

Function fImportAllFiles()

Dim strfile As String
Dim strPath As String

'File path '
strPath = "YOUR FILE PATH HERE"

'Change the default directory to the file path
'
ChDir strPath

'Find the firsttext file
'
strfile = Dir("*.txt")

'Loop through the string & import the files
'
Do While Len(strfile) > 0


DoCmd.TransferText acImportDelim, , "TableName", strPath & "\" &
strfile

'delete the file (consider moving it to an Archive folder instead.)
'Kill strPath & "/" & strfile

'Call Dir to get the next file
'
strfile = Dir
Loop

End Function

Also, look up the TransferText method in the MS help file as there are
several options you may need.

 

[Guest]

Great!

How is it going to knw that the delimitor is a ~ ?

 

[Guest]

Sorry, forgot about that. You need to define an import specification where
you declare that the ~ is the delimiter.

To define a specification:

-- Start the import manually
-- Set all the parameters
-- On the last window, click Advanced button at bottom left
-- Save the settings as a specification with a name that you choose
-- Cancel the import

Use that spec name in the TransferText method's arguments.