Solved

Searching for certain characters within recordsets

Posted on 2001-08-02
21
148 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
Comment Utility
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 6

Expert Comment

by:Nitin Sontakke
Comment Utility
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
Comment Utility
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
 
LVL 6

Expert Comment

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

Author Comment

by:elabiv
Comment Utility
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
Comment Utility
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
Comment Utility
When I say "This Code", I meant sdjefferies code.
0
 
LVL 18

Expert Comment

by:deighton
Comment Utility
'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
Comment Utility
Sorry, it does contain multiple lines. Where do I put that checkfile code. Can it go anywhere?

Cheers
0
 
LVL 6

Expert Comment

by:Nitin Sontakke
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:elabiv
Comment Utility
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 6

Expert Comment

by:Nitin Sontakke
Comment Utility
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
Comment Utility
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 6

Expert Comment

by:Nitin Sontakke
Comment Utility
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
Comment Utility
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
Comment Utility
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 6

Expert Comment

by:Nitin Sontakke
Comment Utility
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 6

Expert Comment

by:Nitin Sontakke
Comment Utility
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
Comment Utility
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 6

Accepted Solution

by:
Nitin Sontakke earned 75 total points
Comment Utility
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
Comment Utility
Thanks a lot for the help (and patience!). Muchly appreciated.

elabiv
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

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 …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now