[AccessD] Importing Multiple Text Files From Specified Folder

Here's sample code from one of my apps...

Usage:

ImportLogs "DirectoryName"

where DirectoryName is any constant that represents the ImportSpec/Folder combination.

ImportSpec needs to be defined and build before using. If you don't need to use different import specifications then you can do away with the strDir parameter and Select statement processing.

'-------------------------------------
Function ImportLogs(strDir As String) as String
    Dim searchFile As String
    Dim resultString As String
    Dim fileCounter As Integer
    Dim messageString As String
    Dim strTextFileName As String
    Dim myFileName As String
    Dim strTableName As String
    Dim strLocation As String
    Dim strImportSpec As String
    Dim strSearchFileSpec As String
    
    strTableName = "PutYourTableNameHere"
    
    Select Case strDir
        Case "DIR1"
            strLocation = "C:\DATA\FOLDER1\" & strSearchFileSpec
            strImportSpec = "ImportSpec1"
        Case "DIR2"
            strLocation = "C:\DATA\FOLDER2\" & strSearchFileSpec
            strImportSpec = "ImportSpec2"
        Case "DIR3"
            strLocation = "C:\DATA\FOLDER3\" & strSearchFileSpec
            strImportSpec = "ImportSpec3"
    End Select

    fileCounter = 0
    searchFile = Dir(strLocation)

    If searchFile <> "" Then
        fileCounter = 1
        resultString = searchFile & vbCrLf
    End If

    Do While searchFile <> ""
        searchFile = Dir
        If searchFile <> "" Then
            fileCounter = fileCounter + 1
            resultString = resultString & searchFile & vbCrLf
            
            strTextFileName = strLocation & searchFile
            DoCmd.TransferText acImportDelim, strImportSpec, strTableName, strTextFileName

        End If
    Loop

    If fileCounter = 0 Then
        messageString = "No files found"
    Else
        messageString = "Found " & fileCounter & " file/s named:" _
            & vbCrLf & resultString
    End If
   
    ImportLogs = messageString
End Function

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Paul Hartland
(ISHARP)
Sent: Thursday, December 02, 2004 8:08 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Importing Multiple Text Files From Specified Folder


To all,
 
I have a folder with around 100+ text files thaht I need to import into a
single table in an access database, is there anyway I can loop through all
files with a .txt extension and automatically import them one by one into my
table....
 
Thanks in advance for any help, suggesstions & sample code etc
 
PAUL HARTLAND
Database Designer/Programmer
paul.hartland at isharp.co.uk
ISHARP
DDI - 01922 472031
Mobile - 07730 523179

 


                                                                                                                                                                                                             ----------------------------------------------------------------
The information contained in this e-mail message and any file, document, previous e-mail message and/or attachment transmitted 
herewith is confidential and may be legally privileged. It is intended solely for the private use of the addressee and must not be 
disclosed to or used by anyone other than the addressee. If you receive this transmission by error, please immediately notify the 
sender by reply e-mail and destroy the original transmission and its attachments without reading or saving it in any manner.  If you 
are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any 
disclosure, copying, distribution or use of any of the information contained in or attached to this transmission is STRICTLY 
PROHIBITED. E-mail transmission cannot be guaranteed to be secure or error free as information could be intercepted, corrupted, 
lost, destroyed, arrive late or incomplete, or contain viruses.
The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of 
email transmission. Users and employees of the e-mail system are expressly required not to make defamatory statements and not 
to infringe or authorize any infringement of copyright or any other legal right by email communications. Any such communication is 
contrary to company policy. The company will not accept any liability in respect of such communication.