Access to review contents of text files

[techsupport3977]

I have a bunch of computers that have a data acquisition program and the program uses a file (.qma this is a text file format) to determine whether or not data records need to be submitted to a database. At the top of the file shows the KEY field. Below the key field, it will be blank (all records safely transmitted or the file may not exist which means all data has been sent) or it will keep a log of the key field entries of the records that have not been transmitted. I would like to have access or SQL look at this file(s) and determine if there are records to be sent. To me if there are records to be sent, then there is a problem with the computer.

My question is how can I create a query in Access or a stored procedure in SQL to look up a file that may or may not exist, review the contents and decide if there is data from line 2 of the text file? I would like the query to simply say 1 for problem, 0 for no problem.

 

[earthandfire]

Since you don't want the contents of the file:

Assuming you know the text that would appear on the first line, couldn't you just check (1) for the existence of the file and if it exists (2) is it larger than the known size if it just contained line one?


Hope this helps.

 

[techsupport3977]

That would be a good way to do it. How do I go about doing this?

 

[earthandfire]

You could try something like this:

Public Function TestFileNotOk(FName As String, MinSize As Long) As Boolean

  On Error Resume Next
  TestFileNotOk = FileLen(FName) > MinSize
  

End Function

Private Sub Command9_Click()

  MsgBox TestFileNotOk("C:\TempDumpingGround\text.txt", 10)

End Sub

False means that the file either does not exist or does exist but is the correct size for just containing line one.

True means that the file exists and is longer than the correct size for just containing lne one.

Make sure you check the length of the file with just line one.

If you move the function into a Module, change its declaration from Private to Public - then you can call it from anywhere.

Hope this helps.

 

[techsupport3977]

I have not tested this yet but it looks like you have it displaying a message box. I would like to just dump this info into a table. A table that has a field for file location, datetime, and true or false. It would be nice to have the code look for the file and then stamp the datetime, location and whether it was true or false into the table so I can track it.

 

[earthandfire]

MsgBox was just to demonstrate using the function.

You could easily produce an Update or Append query which called the function if you put it in a Module and change Private to Public. You would need the appropriate field to be Boolean (Yes/No)

Hope this helps.

 

[techsupport3977]

Can you give me a sample subroutine on how to do this?

 

[earthandfire]

Just use the Query Grid to produce either an Update or Append query in the normal way.

If for example you have a Yes/No field called TxtFileResult in a table called ResultLogs, then using Update as an example:

Select the TxtFileResult field and in the UpdateTo row type =TestFileNotOk(fielname,minimumsizeoffile)


Hope this helps.

 

[timw]

The spirit of this site is that you have a go yourself. If you get into difficulties, you come back, we help you over the sticking point and you try again. That way, you will learn more, rather than expecting to be 'spoon fed'.

Tim
---------------------------
"Your morbid fear of losing,
destroys the lives you're using." - Ozzy

 

[techsupport3977]

I have tested your msgbox function and it works fine.

I have placed this on form_open. I am not understanding how to create the query to interact with this function. How can I make the function update the table if I do not know how to apply the function in the query?

****
Public Function TestFileNotOk(FName As String, MinSize As Long) As Boolean

On Error Resume Next
TestFileNotOk = FileLen(FName) > MinSize


End Function

Private Sub Form_Open(Cancel As Integer)

MsgBox TestFileNotOk("C:\program files\qm\qmd\DB 1200test 1.qma", 10)

End Sub

****

 

[earthandfire]

I explained how to use the function in my example with an Update query. You will probably need an Append query. If you produce it using the Queries tab, you can use the function almost exactly as I showed you in my previous post, except that you will use the Field row as there is no UpdateTo row.

 

[easyit]

Would you mind using a little VBA?
I had something similar, I needed to test whether a file exists and if it was a dump straight from SAP or if it was extracted by hand (also from SAP, but in that case the layout of the file is different). The code sets a checkbox on the form to false or true, depending on the filetype.

Private Sub Form_Load()
Dim fs1 As FileSystemObject
Dim objFolder As Folder
Dim ts As TextStream
Dim strBuffer As String
Dim f As Object
On Error GoTo ErrHandler
Dim MyFile As String
MyFile = "c:\test.txt"
If Dir(MyFile) <> "" Then 'The file is present
    Set fs1 = CreateObject("Scripting.FileSystemObject") 'Now check if the '|'is in the file.
    strBuffer = MyFile
    Set f = fs1.OpenTextFile(strBuffer, ForReading)
    strBuffer = f.Read(5000) 'Read the first 5000 characters
    ' Check if there is a | present in the txt file If so, then the file is auto-generated. if not, then we have a hand generated file
    'Debug.Print InStr(strBuffer, "|")
    If InStr(strBuffer, "|") Then Me.selE25T_AS = False Else Me.selE25T_AS = True
    f.Close
    Set f = Nothing
    Set fs1 = Nothing
End If
Exit Sub

ErrHandler:
    Debug.Print "LOAD" & Err.Number & " " & Err.Description
    Resume Next
End Sub

EasyIT

 

[techsupport3977]

I think I would rather prefer this method. Can you help on showing if the file is present and larger than one line of data? I then need to report this in a table.