compare txt file to database

Posted on 2002-04-16
Last Modified: 2010-05-02
I was looking for source code or tool that will accomplish the below.I have a text file in format
firstname   lastname   date
john          mosely    3/2/2002
lisa          dunn      4/2/2002
mac           something  5/2/2002

Database in sql server
firstname   lastname   date
jeff         porter     3/3/2002
staci        watson     4/3/2002
mike         stern      2/1/2002

I want to compare the txtfile against the database,and where there is a match to input the results into a text file. I can use visualb 6.0 or asp.
Question by:jbird090897
LVL 18

Accepted Solution

deighton earned 200 total points
ID: 6945197
   Dim sLine As String
    Dim sForename As String, sSurname As String, sDob As String

    Open "yourfile" For Input As #1
    Do Until EOF(1)
        Line Input #1, sLine
        sForename = Left(sLine, 14)
        sSurname = Mid(sLine, 15, 10)
        sDob = Mid(sLine, 25)
        'now you can check database
LVL 45

Expert Comment

ID: 6945275
your checking SQL should be:
"select from tablename where firstname = '" & Trim(sForename) & "' and lastname = '" & Trim(sSurname) & "' and [date] = #" & sDOB & "#"
LVL 100

Expert Comment

ID: 6945771
Make ceertain your dates are in the correct format for SQL otherwise you may get one as dd/mm/yyyy and the other as mm/dd/yyyy


Expert Comment

ID: 6945780

Expert Comment

ID: 6946250
You can also use ADODB yo access the text file.

If you use ADODB then you can use ASP as well as VB.

Private Sub cmdADOText_Click()
    'Variables and constants
    Dim objRs  As ADODB.Recordset
    Dim strPath As String
    Dim strDbConnection As String
    Const strDatabaseName As String = "ADOTEST.txt"
    '/Variables and constants
    'Database Path
    strPath = App.Path
    'Connection string
    strDbConnection = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
                        "Dbq=" & strPath & ";" & _
                        "Extensions=asc,csv,tab,txt;" & _
                        "Persist Security Info=False;" & _
                        "DEFAULTDIR=" & strPath & ";" & _
    'Create recordset
    Set objRs = New ADODB.Recordset
    'Set Recordset Properties
    With objRs
        .CursorType = adOpenStatic
        .ActiveConnection = strDbConnection
        .LockType = adLockPessimistic
        .Source = "SELECT * FROM " & _
    End With
    'Check if the Recordset is open
    If objRs.State = adStateOpen Then
        'Loop until EOF
        Do While Not objRs.EOF
            ' zzz Debug
            Debug.Print "Firstname = " & objRs.Fields(0).Value & "   Lastname = " & _
                        objRs.Fields(1).Value & "   Date1 = " & objRs.Fields(2).Value
            ' /zzz Debug
            'Add code here to compare the data
            'Add code here to compare the data
        MsgBox "Recordset is closed!"
    End If

    'Clean up
    If objRs.State = adStateOpen Then
    End If
    Set objRs = Nothing
    '/Clean up
End Sub

Good Luck!

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DO Loop not working 4 72
Validating VB6 Function 19 56
VBA: Select SQL query based on a config Sheet v2 11 38
I need help embedding an image as HTML in my application 3 49
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

920 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

13 Experts available now in Live!

Get 1:1 Help Now