JOING TWO TABLES

[juggers]

Hi all,

I have 2 text files they have the Field [name] in common, I a using 2 DSNs 2 open because they have different structures.
--------------
set con = new ADODB.Connection
set cn = new ADODB.Connection
Set rec = New ADODB.Recordset
Set rs = New ADODB.Recordset
rec.Open " select * from names.txt , con, adOpenDynamic
rs.Open "select * from address.txt", cn, adOpenDynamic
----------------
The idea is to select all the names that are matching in both files add to them the addresses and put them into new text file. I was thinking of joining them threw and SQL coomand they call it inner join but I do not know what is the best solution. Any Ideas?

Regards

time is the father of truth

 

[ArtieChoke]

You can't join across recordsets. Can you drop the files into an Access or SQL database? Otherwise, you probably need to have two loops, one on the names and an inner loop on the addresses and write out an entry to the new file when they match up.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[juggers]

Hi thanks for your reply,

I have created two tables in access I only have used recordsets what can to do now?

Thanks

time is the father of truth

 

[strongm]

>I a using 2 DSNs 2 open because they have different structures

Are you sure that you need two DSNs?

 

[juggers]

Well yes i need 2 DSNs how can you open 2 text files with different layout with one DSN?


time is the father of truth

 

[PatrickIRL]

Is it possible to loop through the Names recordset (rec) and then for each identifier, set the filter accordingly on the Addresses recordset (rs)??

Just a thought ...

Patrick

 

[strongm]

How? By letting the ODBC text driver figure out the layout for you ...

Create the following text file in C:\temp as text1.txt

1, 5
2, 2
3, 7
4, 4
5, 8

and this one as text2.txt in the same folder

27 Jan 2004, 0.5, 1
14 Dec 2005, 0.7, 1
11 May 2000, 0.6, 5

(notice the different layouts and data types)

Add a reference to ADO, then try the following code:

[blue]Option Explicit

Public Sub Commmand1_Click()
    Dim cn As Connection
    Dim recordset1 As Recordset
    
    Set cn = New Connection
    [green]' We'll tell the driver there's no header, and allow driver to figure out fields
    ' which means they'll be called F1 thru F<n>
    [/green]cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\;Extended Properties=""text;HDR=No;FMT=Delimited"""
    
    Set recordset1 = New Recordset
    recordset1.Open "SELECT text2.f1 as ADate, text1.f2*text2.f2 as crosstablecalc from text1.txt as text1 inner join text2.txt as text2 on text1.F1=text2.F3", cn
    Do Until recordset1.EOF
        Debug.Print Format(recordset1("ADate"), "long date"), recordset1("crosstablecalc")
        recordset1.MoveNext
    Loop
End Sub
[/blue]

 

[PatrickIRL]

No, that's not what I meant. Maybe it's not possible but ...

Using your text files as an example I was thinking of the following.

(assuming the first field in text1.txt can link to the third field in text2.txt)

Do while not recs.eof
    rs.filter (<FIELDNAME>) = (recs.fields(1))
    recs.movenext
loop

Will this work??

Patrick

 

[PatrickIRL]

strongm,

Just as a quick test I used you're code for the following.

    Dim cn      As Connection
    Dim rs1     As Recordset
    Dim rs2     As Recordset
    Dim sFilter As String
    ''''''''''''''''''''''''''''''''''
    Set cn = New Connection
    ' We'll tell the driver there's no header, and allow driver to figure out fields
    ' which means they'll be called F1 thru F<n>
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\;Extended Properties=""text;HDR=No;FMT=Delimited"""
    ''''''''''''''''''''''''''''''''''
    Set rs1 = New Recordset
    Set rs2 = New Recordset
    ''''''''''''''''''''''''''''''''''
    rs1.Open "SELECT * from text1.txt", cn, adOpenStatic
    rs2.Open "SELECT * from text2.txt", cn, adOpenStatic
    ''''''''''''''''''''''''''''''''''
    Do Until rs1.EOF
        sFilter = rs2.Fields(2).Name & " = " & rs1.Fields(1).Value
        rs2.Filter = sFilter
        
        Do While Not rs2.EOF
            Debug.Print rs2.Fields(0).Value & " :: " & rs2.Fields(1).Value & " :: " & rs2.Fields(2).Value
            rs2.MoveNext
        Loop
        
        rs1.MoveNext
    Loop
    ''''''''''''''''''''''''''''''''''

Am I missing something?? (I'm having quite a few "senior moments" lately so it's possible)

Patrick

 

[strongm]

So, you don't want an inner join as per your initial post?

 

[PatrickIRL]

Don't confuse me (Patrick) with the original poster (juggers).

I was just offering an opinion, that's all.

 

[juggers]

Guys you are complicating things too much

I have file1.txt with the following fileds:
name, age,sex
and file2.txt with the following fields:
name, address,telephone

All what I want with any method, is to get a third text file file3.txt with the following fields:
name,age,sex,address,telephone.

Any method will I tried loops it is working but I was told that this can be done with sql commands inside the coding



time is the father of truth

 

[bjd4jc]

Although I would personally prefer using a method similar to strongm, another option would be to link your text files in an access database and join them that if (if what strongm is saying is too complicated for you). Then you can use a query to create a text file if you'd like...

Not saying I would do it this way, it's just another option.

 

[strongm]

> I was told that this can be done with sql commands inside the coding

Correct. And that I what I illustrated (well, apart from saving it as a third file), albeit using different fields from yours

 

[strongm]

Here, I've had a slow afternoon, so I've put an example together that more closely matches your requirements.

Create the following text file in c:\temp as names1.txt

Mike, 25, M
Dave, 30, M
Jerry, 42, M
Amanda, 22, F
Simon, 37, M
Sarah, 34, F
Jenny, 36, M

Then create addresses.txt in c:\temp as follows:

Mike, "10 London Road, Kingston, Surrey, KK1 44J", 12345678
Jenny, "Flat 1, 6 Mill Street, Effingham, Surrey", 45678990

And then this code:

[blue]Option Explicit

Public Sub Commmand1_Click()
    Dim cn As Connection
    Dim recordset1 As Recordset
    
    Set cn = New Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\;Extended Properties=""text;HDR=No;FMT=Delimited;"""
    
    Set recordset1 = New Recordset
    
    On Error Resume Next [green]' Ignore the error caused where mydemo.txt does not already exist
        [/green]recordset1.Open "drop table mydemo.txt", cn '
    On Error GoTo 0
    [green]' Creates new table as text file and creates the relevant schema.ini which is in turn
    ' used by INSERT INTO[/green]
    recordset1.Open "CREATE TABLE mydemo.txt (Name  char(50), Age  Integer, Sex  char(1), Address char(100), Tel char(12))", cn
    
    recordset1.Open "INSERT INTO mydemo.txt (Name,Age,Sex,Address,Tel) SELECT AName.f1 , aname.f2 , aname.f3 , address.f2 , address.f3  from names1.txt as AName inner join addresses.txt as address on AName.F1=address.f1", cn

End Sub[/blue]

 

[juggers]

hi STRONGM

I tried your code it is given an error on the last line
"No Value Given for one or more required parameters"




time is the father of truth

 

[juggers]

as well as My text files already exists and they are fixed length

time is the father of truth

 

[strongm]

1) The error message is misleading, and appears to be a quirk of some MDAC versions: Replace all the f<n> with F<n> as below (or use .Fields(<n>) instead of the .F<n>):

recordset1.Open "INSERT INTO mydemo.txt (Name,Age,Sex,Address,Tel) SELECT AName.F1 , AName.F2 , AName.F3 , address.F2 , address.F3  from names1.txt as AName inner join addresses.txt as address on AName.F1=address.F1", cn

2) Fixed length? Fine, just make the appropriate entries in schema.ini (whose existence I hinted at in the code sample, and details of which should be available at msdn.microsoft.com). You may also then have to make modifications to the INSERT INTO command, as your columns will now have names other than the ODBC-generated F1 thru F<n>
3) I'm only giving you an outline of what you need to do, not a full code solution to your specific problem as that should be down to you ...

 

[BobRodes]

thread222-1109585 contains some related detail that you might find helpful.

Bob