CODEWITHSUNDEEP
vbams-accessimport
Sam Nowak
Sam Nowak

Reputation: 1

Loop files and replace the contents of a staging table on each iteration

I'm working with MS Access to import a large number of text files from a folder.

Each text file has the same structure, and a big header contains useful data. I import each text file as a space delimited file into a staging table, and use a series of queries to collect the relevant info and append everything to a master table.

I created a macro to run all of the queries. I'm trying to loop through each file in the folder, but I cannot get the next file import to replace the contents of my staging table for each iteration.

Public Sub LoopThroughFiles()
Dim strFileName As String
Dim intNumberOfFiles As Integer

intNumberOfFiles = 0

strFileName = Dir("My_File_Path\*", vbNormal)

Do Until strFileName = ""
    intNumberOfFiles = intNumberOfFiles + 1
    strFileName = Dir()
    DoCmd.TransferText acImportDelim, My_Import_Spec, "My_Staging_Table", Dir(), False
    DoCmd.RunMacro ("RunMacros")
Loop

End Sub

I implemented the following code based on the suggestions.

I get the same error on DoCmd.TransferText:

"Run-time error 3027
Cannot update. Database or object is read only."

This is the code based on your suggestions:

Private Sub Loopthroughfiles()

    Const SRC_PATH As String = "FilePath" 'for example
    
    Dim strFileName As String
    Dim NumberOfFiles As Long 'prefer Long to Integer
    
    strFileName = Dir(SRC_PATH & "*.txt", vbNormal)
    NumberOfFiles = 0

    Do Until strFileName = "FilePath\LastFileName.txt"
        NumberOfFiles = NumberOfFiles + 1
        
        DoCmd.TransferText acImportDelim, MyImportSpec, "MyStagingTable", _
                           SRC_PATH & strFileName, False
        
        DoCmd.RunMacro "RunMacros"
      
        strFileName = Dir() 'next file
    
        CurrentDb.Execute "DELETE * FROM [MyStagingTable]", dbFailOnError
    Loop

    MsgBox NumberOfFiles & " files imported"
    
End Sub

Upvotes: 0

Views: 122

Answers (2)

ASH
ASH

Reputation: 20302

Is this what you want?

Private Sub Command0_Click()

        Dim strPathFile As String, strFile As String, strPath As String
        Dim strTable As String
        Dim blnHasFieldNames As Boolean

        blnHasFieldNames = True

        strPath = "C:\Users\ryans\OneDrive\Desktop\test\"

        strTable = "Table1"

        strFile = Dir(strPath & "*.txt")
        Do While Len(strFile) > 0
              strPathFile = strPath & strFile
              DoCmd.TransferText acImportDelim, _
                TableName:="Test1", _
                FileName:=strPath & strFile, _
                HasFieldNames:=True
              strFile = Dir()
        Loop


End Sub

If not, post back with more details, please.

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166241

I would think maybe something like this should work:

    Const SRC_PATH As String = "C:\Testing\" 'for example
    
    Dim strFileName As String
    Dim NumberOfFiles As Long 'prefer Long to Integer
    
    NumberOfFiles = 0
    
    strFileName = Dir(SRC_PATH & "*.txt", vbNormal)
    
    Do Until strFileName = ""
        NumberOfFiles = NumberOfFiles + 1
        
        DoCmd.TransferText acImportDelim, My_Import_Spec, "My_Staging_Table", _
                           SRC_PATH & strFileName, False
        
        DoCmd.RunMacro "RunMacros"
      
        strFileName = Dir() 'next file
    Loop

    MsgBox NumberOfFiles & " files imported"

Upvotes: 1

Related Questions