how to get the difference between the data of the two files

[jagpreet]

i have load two data files into a mysql table.now i want the compare the data from these two files (i.e want to compare a particular column of the table and want to get the difference between the values from these two files).
i dont know how to do this.can anyone help me out with this?

[hopalongcassidy]

Your description is kind of vague. So, I'm going to make a couple of assumptions. Firstly, the two data files are similar in structure. I assume that the first data file has a key field that you can search for in the second data file. Secondly, I assume that having searched and found a record in the second data file where the key matches the key in the first file, that there is another column that you wish to compare in the two selected records.

I would load the records from the two data files into two tables, say tbl1 and tbl2.

Then do the following:

Dim db as Database
Dim rs1 as Recordset, rs2 as Recordset
.
.
.
Set rs1 = db.OpenRecordset("SELECT key, value FROM tbl1 WHERE key=" & keyvalue)
If Not rs1.BOF Then
Set rs2 = db.OperRecordset("SELECT key, value FROM tbl2 WHERE key=" & keyvalue)
If Not rs2.BOF then
if rs1.Fields("value") = rs2.Fields("value") then
MsgBox("Found matching record for " & rs2.Fields("value")
Else
MsgBox(rs1.Fields("value") & " in rs1 does not match " & rs2.Fields("value") & " in rs2!"
Else
MsgBox("No matching record in tbl2 for " & keyvalue)
Exit Sub
End If
Else
MsgBox("No matching record in tbl1 for " & keyvalue)
end If
End Sub

This is not a good technique if you have multiple keys you want to match, but I hope it gives you a sense of what you need to do.

Hoppy

[jagpreet]

i have to load the two files into the same table not in two different tables.
then i want to compare the following thing
say c1 is column1,c2 is column 2
r1 is row1 and r2 is row2
now the values in the table r like this
c1r1 c2r1(data from file 1)
c2r2 c2r2( data from file 2)
now i want to get the difference between c2r1 and c2r2

[yello]

rs.open "SELECT C2 FROM Table_Name"

C2R1 = rs("C2")

rs.MoveNext

C2R2 = rs("C2")

compare C2R1 and C2R2

Hope this helps.

[hopalongcassidy]

Firstly, I don't think that yello's solution will work unless both files have exactly one row.

If you initially load both files into a single table, say by loading one file and then appending the second, you will not be able to tell whether two rows that match came from the two rows in the first file (with the same key), two rows from second file or one row from each.

If you want both files to end up in the same table, you can always combine the two tables after your search.

Hoppy

[jagpreet]

i have two data files which contain the values to be stored into the columns of the table .
these files are of same nature ,i.e their data needs to be put into the same table.
these files have data for 6 columns.
now i want to compare the data of only two columns and want to get the difference between these two columns only.
if we can compare these two files and get the difference between two columns before loading them into the table , then that would also be fine.

can u guys help me out with its code..

[hopalongcassidy]

I'm still not quite sure I understand exactly what you are asking for, but I'm going to take a stab at it. Firstly, you need to realize that mysql or any other SQL does not operate on data files on your hard disc; only on tables or things that look like tables (queries) within a database. So, if you want to use mysql, you must first get the data into two tables, say tbl1 and tbl2.

Let's say that you have done that and both tbl1 and tbl2 each have two fields called "key" and "value", then try this query:

mysql_query("SELECT tbl1.key, (tbl1.value - tbl2.value) AS diff FROM tbl1 INNER JOIN tbl2 ON tbl1.key = tbl2.key")

Hoppy

[yello]

I can suggest two methods that may work for you.

1. do some vbscripting to extract the required columns from both text files and save it as two files say file1.txt and file2.txt
Now run the 'diff' command to get the difference.

2. Create one more column in the database for storing some ID. While loading data from file 1 give the ID as F1. While loading data from file 2 give the ID as F2. Then write an sql query to get the values for both IDs seperately, sort it and compare it.

[jagpreet]

can u help me out with any of these methods?