locked
insert excel sheet to database , prevent data from being inserted multiple times causing dupliacates RRS feed

  • Question

  • User5146561 posted

    i am inserting excel sheet to database using datatables and OLEDB , the sheets have different names so i am using a data table to check for sheet names and select data from them ,the problem is the data gets inserted multiple times when the loop runs causing duplicates. i would like the data to be inserted once but I'm not sure how to implement that on the code. Would appreciate the help, here's my code : 

    public void up(string sFileName = @"filename")
            { 
            
            string ssqltable = "[dbo].[PB]";
            //string sFileName = @"filename";
    
        try{
            string sConStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES';", sFileName);
            DataTable dt = new DataTable();
            SqlConnection sqlconn = new SqlConnection(strConnString);
    
             sqlconn.Open();
            using (OleDbConnection connection = new OleDbConnection(sConStr))
            {
                connection.Open();
               dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
               var sheets = dt.Rows[0].Field<string>("TABLE_NAME");
                foreach(var sheet in sheets) //loop through the collection of sheets ;)
                {
                    //your logic here...
                            string myexceldataquery = string.Format("Select * FROM [{0}]; ", sheets);
                            //get data
    
                            OleDbConnection oledbconn = new OleDbConnection(sConStr);
                            OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
                            oledbconn.Open();
                            
                                                 
                            OleDbDataReader dr = oledbcmd.ExecuteReader();
                                 {
                                  //sql bulk copy here!
                                    
                                    DataTable table = new DataTable("benlist");
                                    table.Load(dr);
    
                                    // add two extra columns to data table to be added to database table 
                                    table.Columns.Add("Filename",typeof(string));
                                    table.Columns.Add("User",typeof(string));
    
                                    //set path
                                    string CurrentFilePath = Path.GetFullPath(fuAttachment.PostedFile.FileName);
                                    // add data to additional columns
                                    foreach (DataRow row in table.Rows){
    
                                    row["Filename"] = Path.GetFileNameWithoutExtension(CurrentFilePath);
                                    row["User"] = Session["Username"].ToString();
                    }
    
                    Session["filename"] = Path.GetFileNameWithoutExtension(CurrentFilePath);
    
                    SqlBulkCopy bulkcopy = new SqlBulkCopy(strConnString);
                    bulkcopy.DestinationTableName = ssqltable;
              
                    
                    ////Mapping Table column    
    
                    
                    bulkcopy.ColumnMappings.Add("ParentId", "[ParentId]");
                    bulkcopy.ColumnMappings.Add("PPRDate", "[PPRDate]");
                    bulkcopy.ColumnMappings.Add("Caption", "[Caption]");
                    bulkcopy.ColumnMappings.Add("Filename","Filename");
                    bulkcopy.ColumnMappings.Add("User","User");
    
    
                    //sqlcmd.ExecuteNonQuery();
                //    while (dr.Read())
                //    {
                        bulkcopy.WriteToServer(table);
    
                //    }
                    connection.Close();
                    sqlconn.Close();
    
                                 }
                            
                        }
                    }
            
            
                }
                catch (Exception){}
                ClientScript.RegisterStartupScript(GetType(), "alert", "alert('File Uploaded');", true);
            

    Thursday, May 2, 2019 7:55 AM

All replies

  • User1520731567 posted

    Hi zenani,

    zenani

    the problem is the data gets inserted multiple times when the loop runs causing duplicates.

    According to your desrciptions,If your excel has no duplicate values,

    I suggest you could add breakpoints in your loop,and add if-else clause to judge.

    zenani

    bulkcopy.WriteToServer(table);

    Or you could filter table before this line,such as: Distinct()...

    Best Regards.

    Yuki Tao

    Friday, May 3, 2019 8:02 AM
  • User5146561 posted

    hi Yuki , thank you for your response 

    how can i use an if statement in the loop so it runs only once?

    Friday, May 3, 2019 9:09 AM
  • User5146561 posted

    hi Yuki thanks for your advice, i decided to remove the loop ,the data no longer gets duplicated when i insert it into the database table, 

    reference: href="https://stackoverflow.com/questions/1438083/getting-the-first-sheet-from-an-excel-document-regardless-of-sheet-name-with-ole"

    using (OleDbConnection connection = new OleDbConnection(sConStr))
        {
            connection.Open();
            /// get sheet name
           dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
           //var sheets = dt.Rows[0].Field<string>("TABLE_NAME");
           // foreach(var sheet in sheets) //loop through the collection of sheets ;)
           // {
           var sheets = dt.Rows[0].Field<string>("TABLE_NAME");
                //your logic here...
                        string myexceldataquery = string.Format("Select * FROM [{0}]; ", sheets);
                        //get data
    Friday, May 3, 2019 12:33 PM