how to fix duplicate insert of excel records to database

Question

I am inserting into database excel sheet, I have been able to upload with and without sheet names, I just want to know how can I prevent the data from being inserting multiple times e.g. if my sheet has 2 records the loop inserts it twice and the table ends up looking like this:

ID  DOB       NAME  SURNAME 
1   1/02/1998 jack  turner
2   2/02/1989 jill  blue
1   1/02/1998 jack  turner
2   2/02/1989 jill  blue

Code:

public void up(string sFileName = @"filename") { 

    string ssqltable = "[dbo].[My_Table]";
    //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();
                {
                    DataTable table = new DataTable("benlist");
                    table.Load(dr);
                    // add two extra columns to data table to be added to database table
                    table.Columns.Add("name",typeof(string));
                    table.Columns.Add("surname",typeof(string));
                    // add data to additional columns
                    foreach (DataRow row in table.Rows){
                        row["name"] =Session["Username"].ToString();
                        row["surname"] = Session["Username"].ToString();
                    }
                    SqlBulkCopy bulkcopy = new SqlBulkCopy(strConnString);
                    bulkcopy.DestinationTableName = ssqltable;
                    ////Mapping Table column    
                    bulkcopy.ColumnMappings.Add("IDNumber", "[IDNumber]");
                    bulkcopy.ColumnMappings.Add("DOB", "[DOB]");
                    bulkcopy.ColumnMappings.Add("name", "[name]");
                    bulkcopy.ColumnMappings.Add("surname", "[surname]");

                    //sqlcmd.ExecuteNonQuery();
                    //while (dr.Read())
                    //{
                        bulkcopy.WriteToServer(table);
                    //}
                    connection.Close();
                    sqlconn.Close();
                }
            }
        }   
    }
    catch (Exception){}
    ClientScript.RegisterStartupScript(GetType(), "alert", "alert('File Uploaded');", true);
}

I expect the data to be inserted once no duplicates e.g

ID  DOB       NAME  SURNAME 
1   1/02/1998 jack  turner
2   2/02/1989 jill  blue

hi , i can remove duplicates from the data table DataTable table = new DataTable("benlist") which i load my excel data to? can you please show me an example of ToTable(true)? — Makaziwe, May 03 '19 at 07:24
From [docs.microsoft](https://learn.microsoft.com/en-us/dotnet/api/system.data.dataview.totable?view=netframework-4.8#System_Data_DataView_ToTable_System_Boolean_System_String___). — xdtTransform, May 03 '19 at 07:29
the problem is the data gets inserted multiple times when the loop runs, is there a way i can fix this so that the loop runs and once the data is inserted to datatable the first time it stops — Makaziwe, May 03 '19 at 09:14
The dupe is not magic it's from the datasource, so you need to clear the data before inserting dupe. Either have a collection that doesn't allow duplicate value or remove them. — xdtTransform, May 03 '19 at 09:17
It it was me, Excell to IEnumerable, Clear the list make the proccess needed project to the database object. Then Gravell's fast member to SqlCopy. — xdtTransform, May 03 '19 at 09:20
not sure if we on the same page , the data source doesn't have duplicates , i know this cuz i inserted using "Select * FROM [sheet1$] where i knew my sheet names and twas fine, i added the loop for GetOleDbSchemaTable in order to upload sheets where i dont know the sheet names — Makaziwe, May 03 '19 at 09:26
Sheet2 has the same data than Sheet1? As I said no magic duplicate either the dupe are in table ( bulkcopy.WriteToServer(table); ) or you are doing it twice. — xdtTransform, May 03 '19 at 09:40
there's only one sheet in the excel file , Im uploading an excel sheet to my database, the sheet names are unknown so i used dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); and loop through to find sheet names. if the sheet has 2 records my loop will run twice causing the data to be inserted twice — Makaziwe, May 03 '19 at 09:52

score 0 · Answer 1 · answered May 03 '19 at 12:20

so i removed the loop and the data no longer gets duplicated when i insert it into the database table, thanks

reference: Getting the first sheet from an Excel document regardless of sheet name with OleDb

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

how to fix duplicate insert of excel records to database

1 Answers1