?
Solved

Searching for certain characters within recordsets

Posted on 2001-08-02
21
Medium Priority
?
160 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 9

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

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 9

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 9

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 9

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 9

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 9

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 9

Accepted Solution

by:
Nitin Sontakke earned 300 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

800 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