Read Value from Text files and write to table 1

[ba4crm]

Hello,
Currently, I manually open a number of text files (different names, same naming convention, same directory) and read a certain data element and enter into a table. The data element is always in the last row of text. The last row begins with "T" and is always a 6-byte field in the same position (position 10 to 16). There can be different number of rows in each file.
The data element is entered for the specific client - determined by the first 5-bytes of the file name.
The same client's file can be in the folder more than once. I need to pull both the file's data.
Files are saved to the folder 4-times a day by a mainframe job and each file gets a different datetime stamp in the name.

Is there a way to automate this with VBA where I can execute the command from a form and Access compiles this data for me?

 

[Remou]

You can use the file system object to get the file. This is by no means properly tested. You could also do this in VBScript and add it to the Task Scheduler.

Sub GetDataFromFile()
''Late binding
Dim fs As Object
Dim fldr As Object
Dim f As Object
Dim a As Object
Dim sCompany As String
Dim sData As String

Set fs=CreateObject("Scripting.FileSystemObject")
Set fldr = fs.GetFolder(sBase)

For Each f In fldr.Files

    Set a = f.OpenAsTextStream
    sCompany = Mid(f.Name, 1, 5)
        
    If f.Size > 0 Then
       asContent = Split(a.ReadAll, vbCrLf)
       ''To allow for extra returns after the last data line
       ''You may not need this For loop,
       ''sData=asContent(UBound(asContent)) may suit
       For i = UBound(asContent) To 0 Step -1

           If Left(asContent(i), 1) = "T" Then
              ''This is just characters, I will leave you to
              ''sort out bytes :)
              sData = Mid(asContent(i), 10, 16)
              Exit For
            End If
        Next
            
        If sData <> vbNullString Then
            RecordData sCompany, sData
        End If
 
    Else
       'Empty file
    End If
    
Next

''Don't forget to relaese everything & clean up
End Sub

''How ever you wish to record the data
''Recordset, parameter query, etc
Sub RecordData(Company, Data)

End Sub

http://lessthandot.com

 

[GComyn]

I like it!! I would have read the file one line at a time, checking to see if the first letter is a "T", then work from there.... but I like this... it could help in other applications that I have where I do go through the file one line at a time.

BTW... your mid function needs correcting... it should be:

sData = Mid(asContent(i), 10, 7)

because he specified characters 10-16... which is 7 characters.

Have a star...

GCOmyn

 

[Remou]

I guess you notice that it goes through the file backwards, because you said the "T" line is the last line? This means that it may only test one line.

There is an alternative if the data is structured, and that is using Jet & ADO with text drivers:

http://www.connectionstrings.com/textfile

http://lessthandot.com

 

[ba4crm]

Can you provide some assistance on how to write the two values (Company and Data) to an existing table in the database?

Thanks!