Excel Tool to compare two Excel Sheets

Question

This is my current code. Open to receive any comments to improve the memory optimization.

When I am taking a sample of 1000000 * 8 with 1000000*8 data its resulting into out of memory exception. Would love to have advice on optimizing memory usage.

Compare The two tables in a data set named "Before" and "After" and fill all result tables.

private bool CompareAndFillResultTable(DataSet ds)
    {
        Stopwatch stopWatch = new Stopwatch(); stopWatch.Start();
        System.Data.DataTable dt_copy;
        dt_copy = new System.Data.DataTable();
        dt_copy = ds.Tables["Before"].Copy();
        dt_copy.TableName = "BeforeBackup";
        ds.Tables.Add(dt_copy);
        dt_copy = new System.Data.DataTable();
        dt_copy = ds.Tables["After"].Copy();
        dt_copy.TableName = "AfterBackup";
        ds.Tables.Add(dt_copy);
        dt_copy = new System.Data.DataTable();
        dt_copy = ds.Tables["Before"].Clone();
        dt_copy.TableName = "BeforeSingular";
        ds.Tables.Add(dt_copy);
        dt_copy = new System.Data.DataTable();
        dt_copy = ds.Tables["Before"].Clone();
        dt_copy.TableName = "AfterSingular";
        ds.Tables.Add(dt_copy);
        dt_copy = new System.Data.DataTable();
        dt_copy = ds.Tables["Before"].Clone();
        dt_copy.TableName = "Duplicates";
        ds.Tables.Add(dt_copy);
        dt_copy = new System.Data.DataTable();
        dt_copy = ds.Tables["Before"].Clone();
        dt_copy.TableName = "Mismatch";
        ds.Tables.Add(dt_copy);

        foreach (System.Data.DataTable table in ds.Tables)
        {
            table.Columns.Add("Source_Label");
        }

        //Remove identical from before, then after
        for (int i = 0; i < ds.Tables["Before"].Rows.Count; i++)
        {
            string BeforeCompareKeyVal = ds.Tables["Before"].Rows[i][Inputs.SortColumn].ToString();
            if (ds.Tables["After"].Rows.Count > 0)
            {
                for (int j = 0; j < ds.Tables["After"].Rows.Count; j++)
                {
                    string AfterCompareKeyVal = ds.Tables["After"].Rows[j][Inputs.SortColumn].ToString();

                    if (ds.Tables["Before"].Rows[i].ItemArray.SequenceEqual(ds.Tables["After"].Rows[j].ItemArray))
                    {
                        //copy Aftter row to duplicate Table and Remove row from After
                        DataRow rw = ds.Tables["After"].Rows[j];
                        rw[ds.Tables["After"].Columns.Count - 1] = "NA";
                        ds.Tables["Duplicates"].ImportRow(rw);
                        ds.Tables["After"].Rows.RemoveAt(j);
                        j--;
                        break;
                    }
                    if (Int64.Parse(BeforeCompareKeyVal) > Int64.Parse(AfterCompareKeyVal))// Review - 7
                    {
                        if (true)//all dup after + a before - set logic
                        {
                            //Copy After row to AfterSingular Table and Remove row from After
                            DataRow rw = ds.Tables["After"].Rows[j];
                            rw[ds.Tables["After"].Columns.Count - 1] = "After";
                            ds.Tables["AfterSingular"].ImportRow(rw);
                            ds.Tables["After"].Rows.RemoveAt(j);
                            j--;
                            if (ds.Tables["After"].Rows.Count == 0)
                            {
                                rw = ds.Tables["Before"].Rows[i];
                                rw[ds.Tables["Before"].Columns.Count - 1] = "Before";
                                ds.Tables["BeforeSingular"].ImportRow(rw);
                            }
                            continue;
                        }

                    }
                    if (Int64.Parse(BeforeCompareKeyVal) < Int64.Parse(AfterCompareKeyVal))// Review - 7
                    {
                        if (true)//all dup after and a before set logic
                        {
                            //Copy Before row to BeforeSingular Table
                            DataRow rw = ds.Tables["Before"].Rows[i];
                            rw[ds.Tables["Before"].Columns.Count - 1] = "Before";
                            ds.Tables["BeforeSingular"].ImportRow(rw);
                            break;
                        }
                    }
                    if (Int64.Parse(BeforeCompareKeyVal) == Int64.Parse(AfterCompareKeyVal))// Review - 7
                    {
                        //Copy Before, After row to Mismatch Table and Remove row from After
                        if (true)//all dup after and a before set logic
                        {
                            DataRow rwB = ds.Tables["Before"].Rows[i];
                            rwB[ds.Tables["Before"].Columns.Count - 1] = "Before";
                            DataRow rwA = ds.Tables["After"].Rows[j];
                            rwA[ds.Tables["After"].Columns.Count - 1] = "After";
                            ds.Tables["Mismatch"].ImportRow(rwB);
                            ds.Tables["Mismatch"].ImportRow(rwA);
                            ds.Tables["After"].Rows.RemoveAt(j);
                            j--;
                            break;
                        }
                    }
                }
            }
            else
            {
                DataRow rw = ds.Tables["Before"].Rows[i];
                rw[ds.Tables["Before"].Columns.Count - 1] = "Before";
                ds.Tables["BeforeSingular"].ImportRow(rw);
                continue;
            }
        }
        //Add remaining after table rows to AfterSingular table
        ds.Tables["AfterSingular"].Merge(ds.Tables["After"]);

        //ds.Tables["AfterSingular"].Columns.Add("Source_Label", System.Type.GetType("System.String"), "After_Singular");
        //ds.Tables["BeforeSingular"].Columns.Add("Source_Label", System.Type.GetType("System.String"), "Before_Singular");

        //foreach (System.Data.DataTable table in ds.Tables)
        //{
        //    DataRow colNames = table.NewRow();
        //    //foreach (var col in table.Columns)
        //    //{

        //    //}                
        //    for (int i = 0; i < table.Columns.Count; i++)
        //        colNames[i] = table.Columns[i].ColumnName;
        //    table.Rows.InsertAt(colNames, 0);
        //}

        foreach (System.Data.DataTable table in ds.Tables)
        {
            table.Columns.Remove(Inputs.SortColumn);
            table.AcceptChanges();
        }

        stopWatch.Stop(); lbAlert.Text = lbAlert.Text + "\n\n" + "Total Comparison time for B: " + Inputs.RowNoBeforeTable + " x " + Inputs.ColumnNoBeforeTable + " A: " + Inputs.RowNoAfterTable + " x " + Inputs.ColumnNoAfterTable + " is " + stopWatch.ElapsedMilliseconds + " ms, " + stopWatch.ElapsedMilliseconds / 1000 + " s";
        return true;
    }

Answer 1

Can you not just use a VBA script to do this kind of thing?

Option Explicit

Sub test()

    Dim varSheetA As Variant
    Dim varSheetB As Variant
    Dim strRangeToCheck As String
    Dim iRow As Long
    Dim iCol As Long

    strRangeToCheck = "A1:IV65536"
    ' If you know the data will only be in a smaller range, reduce the size of the ranges above.
    Debug.Print Now
    varSheetA = Worksheets("Sheet1").Range(strRangeToCheck)
    varSheetB = Worksheets("Sheet2").Range(strRangeToCheck) ' or whatever your other sheet is.
    Debug.Print Now

    For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
        For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
            If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
                ' Cells are identical.
                ' Do nothing.
            Else
                ' Cells are different.
                ' Code goes here for whatever it is you want to do.
                    Cells(iRow, iCol).Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 49407
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With

            End If
        Next iCol
    Next iRow

End Sub