Compare Two Datatables

[John Wright]

I have two datatables that I load. One I load from LDAP, the other gets
loaded from Excel. I need to check to see which names are in the LDAP that
are not in the Excel table and vica versa. There are about 300 records in
each. Short of a brute force attack on this, does anyone have an elegant
way to list the non-matches in each table?

Thanks.

John

[Cor Ligthert[MVP]]

Re: Compare Two Datatables

John,

Loop trough your both tables with a for each dr as datarow in datatable.rows
and use than in the other table the rowcollection find with the key from dr
if it exist.

http://msdn.microsoft.com/en-us/library/ydd48eyk(vs.85).aspx

Cor


"John Wright" <riley_wright@h otmail.comschre ef in bericht
news:eLyhk7F3IH A.4536@TK2MSFTN GP06.phx.gbl...

>I have two datatables that I load. One I load from LDAP, the other gets
>loaded from Excel. I need to check to see which names are in the LDAP that
>are not in the Excel table and vica versa. There are about 300 records in
>each. Short of a brute force attack on this, does anyone have an elegant
>way to list the non-matches in each table?
>
Thanks.
>
John
>

[Patrice]

Re: Compare Two Datatables

If you really need to optimize you could progress in parallel in two sorted
lists (you'll have the initial sort cost, then you'll basically browse each
list once in parallel to find out the difference).

I would try that only once I'm 100% sure the simpelst approach is not quick
enough...

--
Patrice

"John Wright" <riley_wright@h otmail.coma écrit dans le message de groupe
de discussion : eLyhk7F3IHA.453 6@TK2MSFTNGP06. phx.gbl...

I have two datatables that I load. One I load from LDAP, the other gets
loaded from Excel. I need to check to see which names are in the LDAP
that are not in the Excel table and vica versa. There are about 300
records in each. Short of a brute force attack on this, does anyone have
an elegant way to list the non-matches in each table?
>
Thanks.
>
John
>

[Miro]

Re: Compare Two Datatables

Doesnt SQL server have a compare on two databases as well?
-If the two have the same schema ?

I remember reading something up on this a while ago. I cannot remember if
it was "in code .net" or if it was part of the sql server options.

Might be a good quick way if this is a one time thing.
If it even exists of what I am talking about. - otherwise - sorry for the
post.

Miro


"Cor Ligthert[MVP]" <notmyfirstname @planet.nlwrote in message
news:DF24F5C5-951F-45C6-A066-9350438AF537@mi crosoft.com...

John,
>
Loop trough your both tables with a for each dr as datarow in
datatable.rows
and use than in the other table the rowcollection find with the key from
dr if it exist.
>

http://msdn.microsoft.com/en-us/library/ydd48eyk(vs.85).aspx

>
Cor
>
>
"John Wright" <riley_wright@h otmail.comschre ef in bericht
news:eLyhk7F3IH A.4536@TK2MSFTN GP06.phx.gbl...

>>I have two datatables that I load. One I load from LDAP, the other gets
>>loaded from Excel. I need to check to see which names are in the LDAP
>>that are not in the Excel table and vica versa. There are about 300
>>records in each. Short of a brute force attack on this, does anyone have
>>an elegant way to list the non-matches in each table?
>>
>Thanks.
>>
>John
>>

>

[John Wright]

Re: Compare Two Datatables

Thanks Cor for the suggestion. Now I guess this needs to get a little
Fuzzy. I need to do a like comparison search on the rows as well. Most of
the rows are the same, but there are some that are slightly different. For
example in on list the lastname will be Smith III (Smith the 3rd) while in
the other list it is just Smith. So I would like to make a 1 for 1
comparison at the row level as suggested then, use the unmatched names and
do a like search to provide the user of possible matches. Any suggestions
on doing a like search?

John
"Patrice" <http://www.chez.com/scribe/wrote in message
news:3F47C52C-2D0C-4183-8CD4-7A3FDFF10A3A@mi crosoft.com...

If you really need to optimize you could progress in parallel in two
sorted lists (you'll have the initial sort cost, then you'll basically
browse each list once in parallel to find out the difference).
>
I would try that only once I'm 100% sure the simpelst approach is not
quick enough...
>
--
Patrice
>
"John Wright" <riley_wright@h otmail.coma écrit dans le message de groupe
de discussion : eLyhk7F3IHA.453 6@TK2MSFTNGP06. phx.gbl...

>I have two datatables that I load. One I load from LDAP, the other gets
>loaded from Excel. I need to check to see which names are in the LDAP
>that are not in the Excel table and vica versa. There are about 300
>records in each. Short of a brute force attack on this, does anyone have
>an elegant way to list the non-matches in each table?
>>
>Thanks.
>>
>John
>>

>

[Patrice]

Re: Compare Two Datatables

Thanks Cor for the suggestion.

I'm not Cor ;-)

I need to do a like comparison search on the rows as well. Most of the
rows are the same, but there are some that are slightly different. For
example in on list the lastname will be Smith III (Smith the 3rd) while in
the other list it is just Smith. So I would like to make a 1 for 1
comparison at the row level as suggested then, use the unmatched names and
do a like search to provide the user of possible matches. Any suggestions
on doing a like search?

My understanding is that you would like to find "close" match. I saw once a
way to do that by computing the "editing distance" between two strings (it
gives the number of elementary editing operations that should be done to
pass from one to the other).

Try :

http://en.wikipedia.org/wiki/Levenshtein_distance

--
Patrice

[Cor Ligthert[MVP]]

Re: Compare Two Datatables

John,

Then if you have found the row(in the other case there is no need for it of
course). Then start an innerloop using the columns.

It go's always like this.

You take the row with the less equal names and then (I type it here so see
it a little bit as pseudo code)

dim equal = true
\\\
for each colum as DataColumn in drInTheLoop.Col umns
'For the columns you want to skip
if Not "ColumnName A ColumnNameB".Co ntains(column.C olumnName) then
if not drInTheLoop.Ite m(column.Column ) =
foundDataRow.It em(column.Colum n.ColumnName) then
if not equal = false then equal = false
end if
end if
end for
///

Be aware that looping seldom takes more time then a direct statement, as you
don't do it, then it is done behind the scene. DotNet is build around
collections.

Cor

"John Wright" <riley_wright@h otmail.comschre ef in bericht
news:eCvd$lG3IH A.5088@TK2MSFTN GP03.phx.gbl...

Thanks Cor for the suggestion. Now I guess this needs to get a little
Fuzzy. I need to do a like comparison search on the rows as well. Most
of the rows are the same, but there are some that are slightly different.
For example in on list the lastname will be Smith III (Smith the 3rd)
while in the other list it is just Smith. So I would like to make a 1 for
1 comparison at the row level as suggested then, use the unmatched names
and do a like search to provide the user of possible matches. Any
suggestions on doing a like search?
>
John
"Patrice" <http://www.chez.com/scribe/wrote in message
news:3F47C52C-2D0C-4183-8CD4-7A3FDFF10A3A@mi crosoft.com...

>If you really need to optimize you could progress in parallel in two
>sorted lists (you'll have the initial sort cost, then you'll basically
>browse each list once in parallel to find out the difference).
>>
>I would try that only once I'm 100% sure the simpelst approach is not
>quick enough...
>>
>--
>Patrice
>>
>"John Wright" <riley_wright@h otmail.coma écrit dans le message de
>groupe de discussion : eLyhk7F3IHA.453 6@TK2MSFTNGP06. phx.gbl...

>>I have two datatables that I load. One I load from LDAP, the other gets
>>loaded from Excel. I need to check to see which names are in the LDAP
>>that are not in the Excel table and vica versa. There are about 300
>>records in each. Short of a brute force attack on this, does anyone
>>have an elegant way to list the non-matches in each table?
>>>
>>Thanks.
>>>
>>John
>>>

>>

>
>

[John Wright]

Re: Compare Two Datatables

Using this as an example I get the error about a primary key. Since these
tables are pulled from LDAP and an Excel spreadsheet there is no primary
key. Any other ideas on how to search without a primary key?


John
"Cor Ligthert[MVP]" <notmyfirstname @planet.nlwrote in message
news:DF24F5C5-951F-45C6-A066-9350438AF537@mi crosoft.com...

John,
>
Loop trough your both tables with a for each dr as datarow in
datatable.rows
and use than in the other table the rowcollection find with the key from
dr if it exist.
>

http://msdn.microsoft.com/en-us/library/ydd48eyk(vs.85).aspx

>
Cor
>
>
"John Wright" <riley_wright@h otmail.comschre ef in bericht
news:eLyhk7F3IH A.4536@TK2MSFTN GP06.phx.gbl...

>>I have two datatables that I load. One I load from LDAP, the other gets
>>loaded from Excel. I need to check to see which names are in the LDAP
>>that are not in the Excel table and vica versa. There are about 300
>>records in each. Short of a brute force attack on this, does anyone have
>>an elegant way to list the non-matches in each table?
>>
>Thanks.
>>
>John
>>

>