View Single Post
  #3 (permalink)  
Old December 28th, 2004, 04:12 PM
mmcdonal mmcdonal is offline
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

To do this you need to do the following:

1. Create an Access database called Comparo.mdb (or another name) with two tables named tblTextFile1 and tblTextFile2.
   The tables should have two fields per record:
      a. LineID (autonumber PK)
      b. Line (memo)

2. Also in the database create a find unmatched query to find the difference between these two tables and call this qryTextFile3

3. A system DSN called "Comparo" linked to your Access database.

4. This assumes your two text files are on your C: root.

Then use this code to pull the data from your text files and push them into your database tables:

'========Start Code==========================
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists("C:\text1.txt") Then
    Set objStream = fso.OpenTextFile("C:\text1.txt", 1, False, 0)
End If

Do While Not objStream.AtEndOfStream
    strLine = objStream.ReadLine 'captures the whole line

    Set objConn = CreateObject("ADODB.Connection")
    Set objRS = CreateObject("ADODB.Recordset")
    objConn.Open "DSN=Comparo;"
    'objRS.CursorLocation = 3
    objRS.Open "SELECT * FROM tblTextFile1", objConn, 3, 3
        objRS.AddNew
        objRS("Line") = strLine
           objRS.AddNew
           objRS.Close
        objConn.Close
Loop

   Set fso = CreateObject("Scripting.FileSystemObject")
   Set MyFile = fso.CreateTextFile("c:\text1.txt", True)


Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists("C:\text2.txt") Then
    Set objStream = fso.OpenTextFile("C:\text2.txt", 1, False, 0)
End If

Do While Not objStream.AtEndOfStream
    strLine = objStream.ReadLine 'captures the whole line

    Set objConn = CreateObject("ADODB.Connection")
    Set objRS = CreateObject("ADODB.Recordset")
    objConn.Open "DSN=Comparo;"
    'objRS.CursorLocation = 3
    objRS.Open "SELECT * FROM tblTextFile2", objConn, 3, 3
        objRS.AddNew
        objRS("Line") = strLine
           objRS.AddNew
           objRS.Close
        objConn.Close
Loop

   Set fso = CreateObject("Scripting.FileSystemObject")
   Set MyFile = fso.CreateTextFile("c:\text2.txt", True)

'===========End Code=======================

This will pull the data into your access database and through the query where you will find your results. It also overwrites the text files so they are empty afterwards.

I am not sure why or how you want this data to show up in a third text file. Can you explain the results you want to see and the format?

P.S. - It is a little cludgey in that I don't think you may have to open and close the database connection so many times. But with this small amount of data, I think it should work in less than a minute.

I hope this helps.

mmcdonal
Reply With Quote