Help needed to read all text files in a folder (1 Viewer)

[HiArt]

Hello Folks,

I am attempting to read all the text files in to a folder using Access VBA. I need to open and read these as some pre-processing is required before writing the data to the appropriate tables.

The following code does not work, but I do not understand why not. It opens the first file but the "While Not ts.AtEndOfStream" immediuatly returns true, even though the file is far from empty, hence no data in the file is read.

Secondly, I get a "file not found" error at the next attempt to execute "Set fileObj = fso.GetFile(f.Name)", even though it is the FileScriptingObject that has returned the filename!

I have been all over the place with this and gave attempted "LINE INPUT XXX AS F" type structure too.

here is the errant code:

Public Sub ImportData()

    Dim db As Database
    Dim rsRace As Recordset
    Dim rsHorse As Recordset
    Dim fileNumber%
    Dim sBuf As String
    Dim fs As String

    Dim fso
    Dim fld
    Dim fl
    Dim f
    Dim ts
    Dim fileObj
    Const ForReading = 1, ForWriting = 2, ForAppending = 8

    Set db = CurrentDb()
    Set fso = CreateObject("Scripting.FileSystemObject")
   
    sSql = "SELECT *" & _
         " FROM _Race;"
    
    Set rsRace = db.OpenRecordset(sSql)
        
    sSql = "SELECT *" & _
         " FROM _Horse;"
   
    Set rsHorse = db.OpenRecordset(sSql)
        
    Set fld = fso.GetFolder("C:\Temp\rpdata")
    Set fl = fld.Files
        
    For Each f In fl
        Set fileObj = fso.GetFile(f.Name)
        Set ts = fileObj.OpenAsTextStream(ForReading, TristateUseDefault)
        Debug.Print ("File: " & f.Name)
    
        While Not ts.AtEndOfStream
            Debug.Print ts.ReadLine
        Wend
    
        ts.Close
    Next
   
    
    'Close stuff
    rsRace.Close
    rsHorse.Close
        
    'Clean up
    Set rsRace = Nothing
    Set rsHorse = Nothing
    Set db = Nothing
    
End Sub

Any advice?

Thanks

Arthur

 

[gemma-the-husky]

i am not too familiar with the file scripting

you could use dir to iterate the files
dir returns any file matching the spec you request
successive calls to dir return further files until no more are found

so something like this - but note that dir is not recursive - so you cant drop into subdirectories with it.

dim filename as string
filename = dir(fullpath,vbnormal)
while len(filename)>0 then
  process(filename) 'your procedure to examine the file
  filename = dir()  'retrieve the next file
wend

 

[HiArt]

Thanks for the reply. Regrettably same problem with DIR.

Art

 

[HiArt]

Sorted.

Amended
Set fileObj = fso.GetFile(f.Name)
to
Set fileObj = fso.GetFile(path & f.Name)

Secondly, the first file was corrupt. Once removed this worked perfectly.

Art

 

[tranchemontaigne]

thanks for sharing your code *SMILE*
________
Weed vaporizers