Solved

Searching for certain characters within recordsets

Posted on 2001-08-02
21
155 Views
Last Modified: 2010-05-02
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
0
Comment
Question by:elabiv
  • 10
  • 8
  • 2
  • +1
21 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 6344072
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
 
LVL 7

Expert Comment

by:Nitin Sontakke
ID: 6344082
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
 

Expert Comment

by:sdjefferies
ID: 6344085
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 7

Expert Comment

by:Nitin Sontakke
ID: 6344095
I am sorry, but i didn't quite get where "deighton" is scanning through all the lines.
0
 

Author Comment

by:elabiv
ID: 6344099
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
 

Author Comment

by:elabiv
ID: 6344120
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
 

Author Comment

by:elabiv
ID: 6344123
When I say "This Code", I meant sdjefferies code.
0
 
LVL 18

Expert Comment

by:deighton
ID: 6344129
'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
 

Author Comment

by:elabiv
ID: 6344137
Sorry, it does contain multiple lines. Where do I put that checkfile code. Can it go anywhere?

Cheers
0
 
LVL 7

Expert Comment

by:Nitin Sontakke
ID: 6344153
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
 

Author Comment

by:elabiv
ID: 6344186
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
 
LVL 7

Expert Comment

by:Nitin Sontakke
ID: 6344215
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
 

Author Comment

by:elabiv
ID: 6344223
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
 
LVL 7

Expert Comment

by:Nitin Sontakke
ID: 6344224
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
 

Author Comment

by:elabiv
ID: 6344226
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
 

Author Comment

by:elabiv
ID: 6344247
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
 
LVL 7

Expert Comment

by:Nitin Sontakke
ID: 6344256
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
 
LVL 7

Expert Comment

by:Nitin Sontakke
ID: 6344270
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
 

Author Comment

by:elabiv
ID: 6344287
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
 
LVL 7

Accepted Solution

by:
Nitin Sontakke earned 75 total points
ID: 6344323
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
 

Author Comment

by:elabiv
ID: 6344353
Thanks a lot for the help (and patience!). Muchly appreciated.

elabiv
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question