Click here to Skip to main content
15,868,340 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have built 2 DataTables from CSV files. I have to compare these DataTables (table1 and table2 to built table 3)I have Successfully Built the 2 tables but I don't know how to pass these tables as arguments and build the new table I get error when I try to Build the table3

once table 3 is built I have to sort it from largest to smallest and return the sorted table

Compare Row of 1st Datatable with Column of 2nd Datatable and build 3rd datatable with matched columns[^]

table1

|Par Name.........| Par #|.......Units |.......LSL  |   USL | -----SKIP |
Diffusion.........908513100.......-..........  0  -----99.9--------0 
Program...........908514100.......-.......... 99.5--- 999----------0
name..............901201005.......-..........-0.812----0.1---------1
ADCI1_N[1]........1.0000000.......-..........-0.800----0.1---------1	


table2
starttime   | Product      | Device   | Diffusion       | Program | 
11/7/2013    SAF5100EL       163       -0.145712003      -0.146583006                                 
11/7/2013    SAF5100EL        84       -0.137499005      -0.137592003
11/7/2013    SAF5100EL        44       -0.142690003      -0.143250003  
11/7/2013    SAF5100EL       164       -0.139434993      -0.140459001
11/7/2013    SAF5100EL        34       -0.147183999      -0.148519993


table3
C#
 |Diffusion|       | Program |
-0.145712003      -0.146583006
-0.137499005      -0.137592003
-0.142690003      -0.143250003
-0.139434993      -0.140459001
-0.147183999      -0.148519993



C#
using System;
using System.Data;
using Microsoft.VisualBasic.FileIO;


namespace ReadDataFromCSVFile
{
    static class Program
    {
        static void Main()
        {
            string csv_file_path = @"C:\Matlab\Limits.csv";
            DataTable table1 = GetDataTabletFromCSVFile(csv_file_path);


            string csv_file_path1 = @"C:\Matlab\Sheet1_t168h.csv";
            DataTable table2 = GetDataTabletFromCSVFile1(csv_file_path1);
            

           DataTable table3 = CompareTwoDataTable( table1, table2);


        }
//BUILDING TABLE 1 FROM CSV FILE
        private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
        {
            DataTable table1 = new DataTable("Limits");

            using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
            {
                csvReader.SetDelimiters(new string[] { "," });
                csvReader.HasFieldsEnclosedInQuotes = true;
                string[] colFields = csvReader.ReadFields();
                foreach (string column in colFields)
                {
                    DataColumn datecolumn1 = new DataColumn(column);
                    datecolumn1.AllowDBNull = true;
                    table1.Columns.Add(datecolumn1);
                }
                while (!csvReader.EndOfData)
                {
                    string[] fieldData = csvReader.ReadFields();
                    //Making empty value as null
                    for (int i = 0; i < fieldData.Length; i++)
                    {
                        if (fieldData[i] == "")
                        {
                            fieldData[i] = null;
                        }
                    }
                    table1.Rows.Add(fieldData);

                }
        //Skip =1 are deleted and table has only those Par Nam which should be considered  
                DataRow[] rows1;
                rows1 = table1.Select("SKIP = '1'");
                foreach (DataRow r in rows1)
                    r.Delete();
            }

            return table1;
        }

//BUILDING TABLE 2 FROM CSV FILE
        private static DataTable GetDataTabletFromCSVFile1(string csv_file_path1)
        {
            DataTable table2 = new DataTable("Real");

            using (TextFieldParser csvReader1 = new TextFieldParser(csv_file_path1))
            {
                csvReader1.SetDelimiters(new string[] { "," });
                csvReader1.HasFieldsEnclosedInQuotes = true;
                string[] colFields = csvReader1.ReadFields();
                foreach (string column in colFields)
                {
                    DataColumn datecolumn2 = new DataColumn(column);
                    datecolumn2.AllowDBNull = true;
                    table2.Columns.Add(datecolumn2);
                }
                while (!csvReader1.EndOfData)
                {
                    string[] fieldData1 = csvReader1.ReadFields();
                    //Making empty value as null
                    for (int i = 0; i < fieldData1.Length; i++)
                    {
                        if (fieldData1[i] == "")
                        {
                            fieldData1[i] = null;
                        }
                    }
                    table2.Rows.Add(fieldData1);

                }

            }
            return table2;
        }

//PASSING TABLE 1 AND TABLE 2 TO BUILD TABLE 3
        private static DataTable CompareTwoDataTable(DataTable table1, DataTable table2)
        {
            DataTable table3 = new DataTable();
            DataRow dr = null;

            for (int i = 0; i < table1.Rows.Count; i++)
            {
                string col = table1.Rows[i]["Par Name"].ToString();
                if (table2.Columns.Contains(col))
                {
                    if (!table3.Columns.Contains(col))
                    {
                        table3.Columns.Add(col, typeof(string));
                    }

                    if (table3.Rows.Count == 0)
                    {
                        for (int j = 0; j < table2.Rows.Count; j++)
                        {
                            dr = table3.NewRow();
                            table3.Rows.Add(dr);
                        }
                    }

                    for (int j = 0; j < table2.Rows.Count; j++)
                    {
                        table3.Rows[j][col] = table2.Rows[j][col].ToString();
                    }
                }


            } 
            return table3;
           }
    
    
    
    
    
    
    
    
    }
}


[edit]SHOUTING removed, Code block added - OriginalGriff[/edit]
Posted
Updated 5-Mar-14 1:35am
v9
Comments
OriginalGriff 3-Mar-14 6:51am    
DON'T SHOUT. Using all capitals is considered shouting on the internet, and rude (using all lower case is considered childish). Use proper capitalisation if you want to be taken seriously.
FarhanShariff 3-Mar-14 7:01am    
Roger that
FarhanShariff 3-Mar-14 7:34am    
Errors
System.Data.DataTable is a type but is used like a variable
No overload for method CompareTwoDataTable takes argument 1

The problem with line
DataTable table3 = CompareTwoDataTable(DataTable table1,DataTable table2);
is that you have left the type definitions in the call - cut&paste error perhaps?
Change it to
DataTable table3 = CompareTwoDataTable(table1,table2);
 
Share this answer
 
Comments
Maciej Los 3-Mar-14 7:53am    
Hawk eye, +5!
CHill60 3-Mar-14 8:06am    
I wish I could see this well when I'm walking too close to trees!! *ouch*. Thank you!
FarhanShariff 3-Mar-14 8:12am    
~~Thanks a lot CHill60~~. I got my table.

I Want to Improve the Code.Suggest some Improvements.
CHill60 3-Mar-14 8:18am    
"Improve" in what way?
FarhanShariff 3-Mar-14 8:22am    
Execution time. I feel that some statements are used redundantly, like the TextFieldParser-- GetDataTabletFromCSVFile used twice for building table1 and table2 I want to pass CSV file Location's(two location in this case) to a single function and build my tables
Quote:
Neewbie, happy to hear u have the result.

final working code is updated below

C#
private static DataTable CompareTwoDataTable(DataTable table1, DataTable table2)
{
DataTable table3 = new DataTable();
DataRow dr = null;
string filterExp = string.Empty;
for (int i = 0; i < table1.Rows.Count; i++)
{
 
string col = table1.Rows[i]["Par Name"].ToString();
if (table2.Columns.Contains(col))
{
if (!table3.Columns.Contains(col))
{
table3.Columns.Add(col, typeof(double));
filterExp = filterExp + col + " asc ,";
}
 
for (int j = 0; j < table2.Rows.Count; j++)
{
if (table3.Rows.Count != table2.Rows.Count)
{
dr = table3.NewRow();
table3.Rows.Add(dr);
}
table3.Rows[j][col] = (table2.Rows[j][col]);
}
 

}
 

}
 
DataTable resultDt = table3.Clone();
for (int m = 0; m < table3.Columns.Count; m++)
{
DataView dv = new DataView(table3);
dv.Sort = filterExp.Split(',')[m];
table3 = dv.ToTable();
for (int i = 0; i < table3.Rows.Count; i++)
{
if (resultDt.Rows.Count != table3.Rows.Count)
{
resultDt.Rows.Add();
}
resultDt.Rows[i][m] = table3.Rows[i][m];
}
 
}
return resultDt;
}


}
}
 
Share this answer
 
v5
Comments
FarhanShariff 5-Mar-14 3:29am    
Error - filterExp does not exits in the current context
george4986 5-Mar-14 3:40am    
plz check updated solution
FarhanShariff 5-Mar-14 3:44am    
The table has negative values it is not considering negative sign when looking for smallest Value
george4986 5-Mar-14 4:02am    
change
table3.Columns.Add(col, typeof(string));
to
table3.Columns.Add(col, typeof(decimal));
FarhanShariff 5-Mar-14 4:15am    
table3.Rows[j][col] = Convert.ToDecimal(table2.Rows[j][col]);//Format exception was unhandled Input string was not in a correct format.
Quote:
george4986 the code is working only thing to be changed is table3.Columns.Add(col, typeof(double));You are a Genius man




Quote:
private static DataTable CompareTwoDataTable(DataTable table1, DataTable table2)
{
DataTable table3 = new DataTable();
DataRow dr = null;
string filterExp = string.Empty;
for (int i = 0; i < table1.Rows.Count; i++)
{

string col = table1.Rows[i]["Par Name"].ToString();
if (table2.Columns.Contains(col))
{
if (!table3.Columns.Contains(col))
{
table3.Columns.Add(col, typeof(double));
filterExp = filterExp + col + " asc ,";
}

for (int j = 0; j < table2.Rows.Count; j++)
{
if (table3.Rows.Count != table2.Rows.Count)
{
dr = table3.NewRow();
table3.Rows.Add(dr);
}
table3.Rows[j][col] = (table2.Rows[j][col]);
}


}


}

DataTable resultDt = table3.Clone();
for (int m = 0; m < table3.Columns.Count; m++)
{
DataView dv = new DataView(table3);
dv.Sort = filterExp.Split(',')[m];
table3 = dv.ToTable();
for (int i = 0; i < table3.Rows.Count; i++)
{
if (resultDt.Rows.Count != table3.Rows.Count)
{
resultDt.Rows.Add();
}
resultDt.Rows[i][m] = table3.Rows[i][m];
}

}
return resultDt;
}


}
}
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900