asked on

compare txt file to database

I was looking for source code or tool that will accomplish the below.I have a text file in format
txtfile:
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.

ASKER CERTIFIED SOLUTION

deighton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aikimark

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

Mike McCracken

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

mlmcc

Chandramouli k

listening...

dannic

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 & ";" & _
"READONLY=False;"

'Create recordset
Set objRs = New ADODB.Recordset

'Set Recordset Properties
With objRs
.CursorType = adOpenStatic
.ActiveConnection = strDbConnection
.LockType = adLockPessimistic
.Source = "SELECT * FROM " & _
strDatabaseName
.Open
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
objRs.MoveNext
Loop
Else
MsgBox "Recordset is closed!"
End If

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

Good Luck!
dannic