Comparing two DataTables by specific column in C#

Question

I am fairly new to C#, but I was able to pull in data from an excel document to a datatable and also data from a sql stored procedure into a datatable. Now I need to loop through the excel datatable to find anything that does not match from the sql datatable. I would also like the matching to begin at a certain column name which is not necessarily the first column. Any ideas or pointers? Thank you!!

Here is my code

foreach (DataRow row in excelTb.Rows) // Loop over the rows.
{
    Console.WriteLine("--- Row ---"); // Print separator.
    foreach (var item in row.ItemArray) // Loop over the items.
    {
        Console.Write("Item: "); // Print label.
        Console.WriteLine(item); // Invokes ToString abstract method.
    }
}
Console.Read(); // Pause.*/

Have you tried anything? Do you have code for us to help with? As it stands, your question is way too open ended. — Faraday, Oct 19 '13 at 00:01
Yeah you could provide us with some code, so we could understand your problem better. Anyway I have provided you with a code to comper records from two DataTables on certain column. — Tafari, Oct 19 '13 at 00:20

Tafari · Accepted Answer · 2013-10-21T16:24:19.223

1

I am not sure if I get your intentions right, but just for compering rows from one datatable to another on certain column you could try this:

private void compareDataTables()
{
  // I assume that datatables are of same length
  for(int i = 0; i < excelDataTable.Rows.Count; i++)
  {
    // Assuming that given columns in both datatables are of same type
    if(excelDataTable.Rows[i]["col_name"] == sqlDataTable.Rows[i]["col_name"])
    {
      //your code
    }
}

Printing column names:

string names = "Excel table: \n";
foreach (DataColumn dc in excelDataTable.Columns)
{
    names += dc.ColumnName+"\n";
}

names += "Sql table: \n";
foreach (DataColumn dc in sqlDataTable.Columns)
                {
                    names += dc.ColumnName+"\n";
                }
MessageBox.Show(names);

edited Oct 21 '13 at 16:24

answered Oct 19 '13 at 00:18

Tafari

2,639
4
20
28

Sorry for taking so long to get back to you on this... It turns out that when pulling both my datatables from sql and excel, so the column names are not static. I am trying to get that to work first, but then I believe your solution will work for me. Here is the code I have to populate the datatable for excel, and it is mirrored for sql. – Terry Minton Oct 21 '13 at 15:11
I used your solution, but am getting an Argument Exception Error: Column 'Template Description' does not belong to table – Terry Minton Oct 21 '13 at 15:50
Do you put the correct "col_names"? As it seems you provide it with the wrong column name. Are you sure there is a column with the name 'Template Description'? I have put a code in my answer for you, to list all of column names of particular table. – Tafari Oct 21 '13 at 16:22
I think the problem might be that the excel document has a few extra rows at the top for misc garbage... The sql query runs and puts the correct column names in. Is there a way to loop through the excel datatable and start at a specific row that contains "Template description"? – Terry Minton Oct 21 '13 at 16:25
Have you checked the code I gave you with printing column names? If yes do you have 'Template Description' column in there? – Tafari Oct 21 '13 at 16:29
ahh, the excel is using the wrong row as the header... that is the problem. Anyway I can get around this @Tafari? I really do appreciate all this help! the sql query displays the proper names. – Terry Minton Oct 21 '13 at 16:30
@TerryMinton Glad to help you! If you could please upvote/select my answer : ) If still you have any problems feel free to add more comments here ; ) If so just correct the wrong column name or change your condition to use the proper excel column name and it should work. – Tafari Oct 21 '13 at 16:32
I need to have the header row start at row 9, is that even possible? – Terry Minton Oct 21 '13 at 16:35
I don't really understand, start at row 9 in what terms? And what do you mean by saying 'header row'. DataTable is build of rows and columns, the header is a property of a column. – Tafari Oct 21 '13 at 16:42
Sorry, The column names in the excel document do not start at the top row, they are actually 9 rows down in the doc. So when I run your column names loop it shows me the wrong column names for the excel doc. when I tried running your first loop you suggested by switching the column names on the excel sheet to match the columns in the sql datatable, I get an IndexOutOfRange exception. There is no row at position 89. – Terry Minton Oct 21 '13 at 16:48
So the tables aren't of equal length right? You could check it by using this `Console.WriteLine("Excel: "+excelDataTable.Rows.Count+" SQL: "+sqlDataTable.Count);` – Tafari Oct 21 '13 at 17:04
yes very interesting that is says excel has 248 but sql has 89. And the fact that excel has the incorrect column names as the header... after filling the datatable, can I delete the first 9 rows and rename the header? – Terry Minton Oct 21 '13 at 17:36
Yeah it is possible to delete the rows (`DataTable.Rows.Remove`) and change the column name (`DataTable.Columns[i].ColumnName = newColumnName`). But anyway you still will have to rebuild your loop as both tables are of different lengths. – Tafari Oct 21 '13 at 17:43
I removed the rows in the excel sheet that were causing an issue, and I renamed all the column names in excel, but they are all failing to match? – Terry Minton Oct 21 '13 at 18:17
So maybe they should? If you want to be sure you can loop through the values of specified column of each row for example `foreach(DataRow dr in excelDataTable.Rows)Console.WriteLine(dr["col_name"].ToString());` – Tafari Oct 21 '13 at 18:48
I have a feeling that it is all because the lengths of the rows are not equal. I thought it would loop through and match the rows that actually matched between them and then just not match on the extra excel rows. – Terry Minton Oct 21 '13 at 19:16
So I finally got the column names all the same, and trimmed down the rows the match exactly. In the loop, is there anyway to search through the excel datatable, and not match rows exactly, but look through each row for a match? They are not all in order, so basically search one row in excel to the whole sql datatable? Does that make sense? – Terry Minton Oct 21 '13 at 19:52
Try this: `for(int i=0; i < excelDataTable.Rows.Count; i++){ for(int j=0; j < sqlDataTable.Rows.Count; j++) { if(excelDataTable.Rows[i]["col_name"] == sqlDataTable.Rows[j]["col_name"]){//your code}}}` – Tafari Oct 21 '13 at 22:58
That made everything not match as well. I did choose your answer and I cannot thank you enough for helping me! This site really rocks, and so do all the people on here. I know there has to be a way to compare one row to a whole table, I just need to keep doing trial and error. @Tafari, You are the best! – Terry Minton Oct 21 '13 at 23:21
You are welcome, the code I provided in my previous comment, compares every single row from excelDataTable to every single row in sqlDataTable : ) – Tafari Oct 22 '13 at 05:39

Comparing two DataTables by specific column in C#

1 Answers1