Click here to Skip to main content
15,882,017 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i am inserting into database excel sheet, i have been able to upload with and without sheet names thanks to help from the group , i just want to know how can i prevent my loop from inserting data 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

i would like the data to look like this once inserted:

ID DOB NAME SURNAME

1 1/02/1998 jack turner

2 2/02/1989 jill blue

What I have tried:

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);
        
        }
Posted
Updated 3-May-19 2:22am
v3
Comments
[no name] 2-May-19 15:57pm    
I think you need more connections. Or, create CSV and "connect" later once you get the "extract" figured out.

If you can't validate your data beforehand, I'd imagine you need to look for a record before you insert it, to see if it exists?
 
Share this answer
 
Comments
Member 14183767 3-May-19 2:51am    
hi , how can i do that? can the data not be inserted into the datatable once only?
Christian Graus 3-May-19 3:39am    
https://www.techrepublic.com/blog/microsoft-office/prevent-duplicates-when-entering-data-into-excel/

To my surprise, I googled and found this
Member 14183767 3-May-19 3:43am    
hi this is for excel , the duplicates get inserted into the database table , they are not on the excel sheets
Christian Graus 3-May-19 3:44am    
Excel is not a database. Where do they go?
Member 14183767 3-May-19 3:46am    
the excel sheet is uploaded to data table then i use sql bulkcopy to insert it into my database table
so i removed the loop and the data no longer gets duplicated when i insert it into the database table, thanks

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
 
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