You must Sign In to post a response.
  • Category: .NET

    How to compare the excel data with data in table(Database)?

    I have one scenario.

    1) Uploading of excel. Excel having 10 row with 10 column

    I have attached one excel for your reference.

    If i upload it will get inserted into table named ABC in backend with one ID(Say 1) generated for that 10 rows.

    2) If i upload the same excel again, it is getting inserted in backend by generating the ID no. 2.

    When i upload the excel, I want to compare the data in excel with the data in database based on ID. If the database having the same data as that of second upload file it should show message as "Already exist". How can we achieve this. It should give message if the all
    the row and all the data in the cell matches.

  • #765029
    Both EXCEL data and database table are in different format, you need to make them uniform to compare ? first you need to read EXCEL file (may be with OLEDB) and stored data in DATATABLE and then read database table to read DATA and stored in DATATABLE then you can compare both data table
    But, DataTables have no built-in methods for complicated comparison operations so logically we need to introduce the CompareRows method. It receives two DataTable instances. Then it loops over all the rows in both DataTables with nested loops.
    see below links

    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #765034

    If you want to insert Excel data into database you need to open OLEDB Provider connection, refer below sample code here i will explain how to insert excel data into database.

    private void InsertIntoDB()
    int count=0;

    SqlConnection con = new SqlConnection("DataBase=ENGSOFT;User Id=sa;Password=P@ssword9");
    SqlCommand cmd;
    DataTable dt = Session["Table"] as DataTable;

    if (dt.Rows.Count > 0)
    //Read each and everyrow of datatable and insert each and every row to SQL DataBase.
    foreach (DataRow dr in dt.Rows)
    string Ename = dr["ENAME"].ToString();
    string Job = dr["JOB"].ToString();
    string MGR = dr["MGR"].ToString();
    string Sal = dr["SAL"].ToString();
    string comm = dr["COMM"].ToString();

    //normal insert query.

    cmd = new SqlCommand("INSERT INTO EMP(ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(@ename,@job,@mgr,@hiredate,@sal,@comm,@deptno)", con);
    cmd.Parameters.AddWithValue("@ename", Ename);
    cmd.Parameters.AddWithValue("@job", Job);
    cmd.Parameters.AddWithValue("@mgr", MGR);
    cmd.Parameters.AddWithValue("@hiredate", DateTime.Now);
    cmd.Parameters.AddWithValue("@sal", Sal);
    cmd.Parameters.AddWithValue("@comm", comm);
    cmd.Parameters.AddWithValue("@deptno", "1");

    int n = cmd.ExecuteNonQuery();
    count = count + n;

    catch (Exception ex)
    lblResult.Text = count + " Records Inserted Successfully...!!!";

    If you want to compare the record is exist or not, then get the records from database and compare with excel data if not exists then insert it, other wise you can skip for insertion.

    Give respect to your work, Instead of trying to impress your boss.

    Blog :

Sign In to post your comments