Searching for certain characters within recordsets

Hi, Im currently working on a program that allows the user to select an input file for my program to scan through and do various other things. In order for the program to read this file correctly, the file must have 4 fields, all seperated by a "#". I have made the program return a message box if the file contains no records, but is there any way of scanning each line, to make sure that there are 4 fields, seperated by a "#", rather than the program just dying?

Thnaks
elabivAsked:
Who is Participating?
 
Nitin SontakkeDeveloperCommented:
Make a small modification in my function, make it

Close #intHandle  '# charactor is added...

Also, it is a good programming practise NOT to use the syntax you are using, such as #1.

Please follow the code i have used to open the file. Such as:

01. Declare a separate variable to hold file handle.
02. Initialise the variable as: variableName = FreeFile
03. Then use that variable everywhere, where you have typed #1

I am sure i am clear enough.

Note: If you are running short of points, there is no need to increase it. Thanks anyway.

Regards,
0
 
deightonprogCommented:
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
0
 
Nitin SontakkeDeveloperCommented:
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

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
sdjefferiesCommented:
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
0
 
Nitin SontakkeDeveloperCommented:
I am sorry, but i didn't quite get where "deighton" is scanning through all the lines.
0
 
elabivAuthor Commented:
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.
0
 
elabivAuthor Commented:
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
0
 
elabivAuthor Commented:
When I say "This Code", I meant sdjefferies code.
0
 
deightonprogCommented:
'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
0
 
elabivAuthor Commented:
Sorry, it does contain multiple lines. Where do I put that checkfile code. Can it go anywhere?

Cheers
0
 
Nitin SontakkeDeveloperCommented:
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
0
 
elabivAuthor Commented:
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))
0
 
Nitin SontakkeDeveloperCommented:
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.
0
 
elabivAuthor Commented:
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.
0
 
Nitin SontakkeDeveloperCommented:
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.

0
 
elabivAuthor Commented:
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?
0
 
elabivAuthor Commented:
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
0
 
Nitin SontakkeDeveloperCommented:
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.

0
 
Nitin SontakkeDeveloperCommented:
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
0
 
elabivAuthor Commented:
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.
0
 
elabivAuthor Commented:
Thanks a lot for the help (and patience!). Muchly appreciated.

elabiv
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.