CODEWITHSUNDEEP
c#datatableclosedxml
JB1989
JB1989

Reputation: 55

DataTable column compare and write differences to a another column

I'm writing an application in c# that automates a comparison of values in two different databases.

The data is provided in two .xlsx files. These are loaded by the end user into the application. An in memory DataTable is then created from both files. One for each.

Now I need to compare column 1 from DataTable one with column 3 from DataTable two. The order of contents is not the same but they are all, always numeric values. Theres a couple of possible outcomes:

I have thought about creating a function that accepts two parameters. Both DataTables. A comparison would then be done on the first column of table one and the third column of table 3.

Another solution which I am working on now pasting both DataTables to a worksheet via ClosedXML. Extracting the relevant column (1 and 3) to another seperate worksheet and using a for each loop to compare all cells from both worksheets and get the differences. But I dont know how to go on about this as far as get the content which is not in column 1 but its in column 3 to worksheet A and the content which is in column 1 but not in column 3 to worksheet B.

Upvotes: 0

Views: 682

Answers (1)

Guilhem Prev
Guilhem Prev

Reputation: 979

You can use Linq to find what is in the column 1 and not in column 3 and vice-versa.

You just have to add System.Data.DataSetExtensions in reference of your project like here

And then the Linq query :

var dataTable1 = new DataTable();
dataTable1.Columns.Add(new DataColumn("Data Column 1"));

dataTable1.Rows.Add(1, "", "");
dataTable1.Rows.Add(3, "", "");
dataTable1.Rows.Add(5, "", "");
dataTable1.Rows.Add(7, "", "");
dataTable1.Rows.Add(9, "", "");

var dataTable2 = new DataTable();
dataTable2.Columns.Add(new DataColumn("Data Column 1"));
dataTable2.Columns.Add(new DataColumn("Data Column 2"));
dataTable2.Columns.Add(new DataColumn("Data Column 3"));

dataTable2.Rows.Add("", "", 1);
dataTable2.Rows.Add("", "", 2);
dataTable2.Rows.Add("", "", 4);
dataTable2.Rows.Add("", "", 6);
dataTable2.Rows.Add("", "", 7);
dataTable2.Rows.Add("", "", 8);
dataTable2.Rows.Add("", "", 9);

// Use the id of the column
var column1 = dataTable1.AsEnumerable().Select(r => r.ItemArray[0]).ToList();
var column3 = dataTable2.AsEnumerable().Select(r => r.ItemArray[2]).ToList();

// Use the name of the column
var column1WithName = dataTable1.AsEnumerable().Select(r => r.Field<string>("Data Column 1")).ToList();
var column3WithName = dataTable2.AsEnumerable().Select(r => r.Field<string>("Data Column 3")).ToList();

// All element of the Column 1 (DataTable1) that are not in Column 3 (DataTable2)
var column1Without3 = column1.Except(column3).ToList(); // [ "3", "5" ]
// All element of the Column 3 (DataTable2) that are not in Column 1 (DataTable1)
var column3Without1 = column3.Except(column1).ToList(); // [ "2", "4", "6", "8" ]

Upvotes: 1

Related Questions