Searching for certain characters within recordsets

Option Explicit




Private Function CheckFile(sPath As String) As Boolean

    Dim sbuf As String
    Dim i As Long
    Dim c As Long
   
    Open sPath For Binary As #1
    sbuf = String(LOF(1), 0)
    Get #1, , sbuf
    Close 1
   
    For c = 1 To Len(sbuf)
        If Mid(sbuf, c, 1) = "#" Then i = i + 1
    Next
   
    CheckFile = i = 3 'assume need 3 * #


End Function

Private Sub Form_Load()
If CheckFile("c:\test\grades.mdb") Then MsgBox "ok" Else MsgBox "oops"
End Sub

Yes, you can certainly do that. I should be giving you the solution soon.

Meanwhile, you can start implementing, if not already existing, an interface to inform user that you are validating the file. This will be particularly useful if the file is too big. For example,

Please wait...Validating

You can use the "instr" command. e.g.

    Dim strData As String
    Dim lngPos As Long
   
    strData = "Test1#Test2#Test3#Test4"
   
    ''' locate position of first hash
    lngPos = InStr(strData, "#")
   
    ''' locate position of second hash
    lngPos = InStr(lngPos + 1, strData, "#")
   
    ''' locate position of third hash
    lngPos = InStr(lngPos + 1, strData, "#")

You could put this into loop and increment a counter counting the number of #'s found. e.g.

    Dim strData As String
    Dim lngPos As Long
    Dim lngNumHash As Long
   
    strData = "Test1#Test2#Test3#Test4"
   
    lngPos = 0
    lngNumHash = 0
   
    Do
       ''' locate position of next hash
        lngPos = InStr(lngPos + 1, strData, "#")
       
        If (lngPos > 0) Then
           
            ''' found hash, increment counter
            lngNumHash = lngNumHash + 1
           
        End If
       
    Loop Until (lngPos = 0)

Regards
Simon

I am sorry, but i didn't quite get where "deighton" is scanning through all the lines.

Where do I put the "Private Function CheckFile(sPath As String) As Boolean" code. Can It go within the command1_click code? Can the "If CheckFile("c:\test\grades.mdb") Then MsgBox "ok" Else MsgBox "oops"
" also go within the command1_click code?

thanks.

I dont understand where to put this code. I have a button on my form, and when this is clicked, it makes a new recordset. it then begins to scan through the file, placing the text entries into the recordsets. If at this point, the text file does not contain any #'s, then the program dies. The code is as follows:

Dim rs As New ADODB.Recordset
With rs
.Fields.Append "first", adChar, 150, adFldIsNullable
.Fields.Append "second", adChar, 150, adFldIsNullable
.Fields.Append "third", adChar, 150, adFldIsNullable
.Fields.Append "fourth", adChar, 150, adFldIsNullable
End With

pathtofile = Text1.Text
Dim holder As Variant
rs.Open
Open pathtofile For Input As #1
Do Until EOF(1)
Line Input #1, sline1
holder = Split(sline1, "#")
rs.AddNew
rs!first = CStr(holder(0))
rs!second = CStr(holder(1))
rs!third = CStr(holder(2))
rs!fourth = CStr(holder(3))
rs.Update
Loop
Close #1

elabiv

When I say "This Code", I meant sdjefferies code.

'something like this

'did you give us the right info?  You said 'the
file must have 4 fields,'

 you're going to say that it has multiple lines of 4 fields each I bet!


Dim rs As New ADODB.Recordset

if checkfile(pathtofile) then

With rs
.Fields.Append "first", adChar, 150, adFldIsNullable
.Fields.Append "second", adChar, 150, adFldIsNullable
.Fields.Append "third", adChar, 150, adFldIsNullable
.Fields.Append "fourth", adChar, 150, adFldIsNullable
End With

pathtofile = Text1.Text
Dim holder As Variant
rs.Open
Open pathtofile For Input As #1
Do Until EOF(1)
Line Input #1, sline1
holder = Split(sline1, "#")
rs.AddNew
rs!first = CStr(holder(0))
rs!second = CStr(holder(1))
rs!third = CStr(holder(2))
rs!fourth = CStr(holder(3))
rs.Update
Loop
Close #1

else
 msgbox "sorry error in file format - can't do it"
end if

Sorry, it does contain multiple lines. Where do I put that checkfile code. Can it go anywhere?

Cheers

I believe that i am giving you the solution you are looking for. I just hope that i am not too optimistic.

Let me tell you that i have not thoroughly tested it, as i don't have the live data.

Please let me know, if you need any more info.

Regards,


Private Sub Command1_Click()
    Dim strFilename As String
    strFilename = "C:\NitinSontakke\VBProjects\TempFile.txt"
    If isValidFile(strFilename) Then
        loadFile strFilename
    Else
        'Your error message goes here...
    End If
End Sub

Private Function isValidFile(ByVal strFilename As String) As Boolean
    isValidFile = False
    Dim intHandle       As Integer
    Dim tempArray       As Variant
    Dim strLine         As String
    intHandle = FreeFile
    Open strFilename For Input As intHandle
    Do While Not EOF(intHandle)
        Line Input #intHandle, strLine
        tempArray = Split(strLine, "#", , vbTextCompare)
        If UBound(tempArray) > 3 Then
            Exit Function
        End If
    Loop
    Close intHandle
    isValidFile = Len(Trim(strLine)) <> 0
End Function

Private Sub loadFile(ByVal strFilename)
    '
    ' Your logic to load file in recordset goes here....
    '
End Sub

When you say "Your logic to load file in recordset goes here..." I have entered:

Dim rs As New ADODB.Recordset
   With rs
.Fields.Append "domain", adChar, 150, adFldIsNullable
.Fields.Append "primarydns", adChar, 150, adFldIsNullable
.Fields.Append "secondarydns", adChar, 150, adFldIsNullable
.Fields.Append "mxrecord", adChar, 150, adFldIsNullable
End With

pathtofile = Text1.Text
Dim holder As Variant
rs.Open
Open pathtofile For Input As #1
Do Until EOF(1)
Line Input #1, sline1
holder = Split(sline1, "#")
rs.AddNew
rs!domain = CStr(holder(0))
rs!primarydns = CStr(holder(1))
rs!secondarydns = CStr(holder(2))
rs!mxrecord = CStr(holder(3))
rs.Update
Loop
Close #1

Is this correct. Also the only modification I have made to your code is to replace

strFilename = "C:\NitinSontakke\VBProjects\TempFile.txt"
with

strFilename = Text1.Text

Is this correct.

When I run the script now on an invalid file, it now says:

Subscript Out of range and highlights the line

rs!second = CStr(holder(1))

Is it possibly for you to give me the test data? It has to be worst kind of data that you can possibly have.

As i cannot foresee ALL sorts of possibilities, they are not covered in my function.

If you give me the data, i will be able to test the function on GIVEN data and modify it.

You can send file as an attachment to NitinSontakke@Yahoo.com

I personally believe that this is the best way to attack the problem.

Thanks.

The problem occurs if the data within the file is incorrect. If the file is correct (4 fields and 3x #) then it works fine, but if the file is empty or only has 3 fields or it has 4 fields seperated by a "," then that is when I get the error.

Also, please let me know:

01. If number of columns MUST BE 4 or,

02. less than 4 is possible and you are happy with that.

This is important, as it will change the solution.

Is there anyway of saying:

if the line of the file that i am reading doesnt contain 3 #'s and 4 fields then error message?

all 4 fields must contain data.

Acceptable :

john#smith#21#male
alex#hart#38#female

UnAcceptable:

john#smith##male
alex##38#female

john,snith,21,male
alex,,,female

etc...etc

cheers

That's what the function does, actually. However, it fails under certain conditions. So if you can give the test data it will faster for me to detect those conditions. Simple.

If it is NOT possible for you to give the data, please let me know that explicitely.

So that i can start thinking of some otherways to improve. In fact, as a first attempt, you can modify the following line

FROM : If UBound(tempArray) > 3 Then
TO :   If UBound(tempArray) <> 3 Then

and try again.

Here is a further improved function for the sample "unaccepted" data given by you.

Just replace the existing function with following code.

Private Function isValidFile(ByVal strFilename As String) As Boolean
    isValidFile = False
    Dim intHandle       As Integer
    Dim intIndex        As Integer
    Dim tempArray       As Variant
    Dim strLine         As String
    intHandle = FreeFile
    Open strFilename For Input As intHandle
    Do While Not EOF(intHandle)
        Line Input #intHandle, strLine
        tempArray = Split(strLine, "#", , vbTextCompare)
        If UBound(tempArray) <> 3 Then
            Exit Function
        End If
        For intIndex = LBound(tempArray) To UBound(tempArray)
            If Len(Trim(tempArray(intIndex))) = 0 Then
                Exit Function
            End If
        Next
    Loop
    Close intHandle
    isValidFile = Len(Trim(strLine)) <> 0
End Function

Thats Brilliant. It now detects all invalid files. The only problem is, is that if the file is valid, I get an error in my part of the code where it tries to open the file for reading it into the recordsets.

Open pathtofile For Input As #1

Is there anyway of closing this file in your part of the code. If so, what would the code be, and where would it go?

Cheers.

P.S I'll increase and award the points after this one.

Thanks a lot for the help (and patience!). Muchly appreciated.

elabiv