none
Import Data into table from excel file using c#. RRS feed

  • Question

  • when i am import data into table from excel sheet ...it giving error of Cannot insert explicit value for identity column in table 'OrderDetail' when IDENTITY_INSERT is set to OFF. 

    I did n't define my first column Name in my excel sheet ,because it number auto generating ,so how can i managed it before importing data into db table 

    Wednesday, July 24, 2019 3:38 PM

Answers

  • Hi Akhterhussain,

    Thanks for the feedback.

    According to the picture, I find the error and know how to solve it. Please use the following correct connectionstring. 

    string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";

    Note:Extended Properties should be Excel 12.0 instead of Excel 8.0.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Friday, July 26, 2019 5:28 AM
    Moderator

All replies

  • You are trying to insert data into a column that is marked as an identity column and therefore will be assigned a value automatically. In general your INSERT statement will list the columns to set, make sure you aren't trying to set the identity column. 

    If this doesn't help you then please post the code you're using and the table schema.


    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, July 24, 2019 3:59 PM
    Moderator
  • Hi Akhterhussain,

    Thank you for posting here.

    Based on your description, you want to solve the exception that you get when you import data into db table from excel file.

    Here is a simple code for your reference.

    private void Button1_Click(object sender, EventArgs e)
            {
                string con = "Data Source=(localdb)\\ProjectsV13;Initial Catalog=School;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
                string path = "D:\\t.xlsx";
                SqlConnection connection = new SqlConnection(con);
                connection.Open();
                SqlBulkCopy sqlBulk = new SqlBulkCopy(connection);
                sqlBulk.DestinationTableName = "Excel";
                var dt = Exceldatatable(path);
                sqlBulk.WriteToServer(dt);
                connection.Close();
                MessageBox.Show("success");
            }
            public  DataTable Exceldatatable(string path)
            {
                DataTable dt = new DataTable();
                string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
                    using (OleDbConnection conn = new OleDbConnection(connectionString))
                {
                    using (OleDbCommand comm = new OleDbCommand())
                    {
                        string sheetName = "Sheet1";
                        comm.CommandText = "Select * from [" + sheetName + "$]";
                        comm.Connection = conn;
                        using (OleDbDataAdapter da = new OleDbDataAdapter())
                        {
                            da.SelectCommand = comm;
                            da.Fill(dt);
                            return dt;
                        }
                    }
    
                }
            }

    Database Design:

    Excel File:

    SQL Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 25, 2019 2:00 AM
    Moderator
  • Hi Jack,

    Thanks For Reply but i do not want to mentioned orderdetail ID in excel sheet it is auto generated ...

    Regards,

    Akhter

    Thursday, July 25, 2019 4:47 AM
  • Hi Michael Taylor here is my complete code 

    using System;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.OleDb;
    using System.IO;
    
    
    namespace Appprd
    {
        public partial class ImportOD : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
    
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
    
                string file_name = Path.GetFileName(FileUpload1.PostedFile.FileName);
    
                // save the file on server(website)
    
                FileUpload1.SaveAs(Server.MapPath(file_name));
    
                // save file name in sessio objet
    
                Session["Book22"] = file_name;
    
                //refresht the page
    
                Response.Redirect("ImportOD.aspx");
            }
    
            protected void Button3_Click(object sender, EventArgs e)
            {
                // create some string variables and assign null values
    
                string ex_id = "";
                string ex_name = "";
                string ex_age = "";
               // string ex_location = "";
               // string ex_hiredate = "";
    
                //string myfile_name = Path.GetFileName(FileUpload1.PostedFile.FileName);
    
                // assign session object data to myfile_name variable
    
                string myfile_name = Session["Book22"].ToString();
    
                // get complete path of excel sheet and assing it Excel_path variable
    
                string Excel_path = Server.MapPath(myfile_name);
    
                // create connection with excel database 
    
                OleDbConnection my_con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Excel_path + ";Extended Properties=Excel 8.0;Persist Security Info=False");
    
                my_con.Open();
    
                try
                {
    
                    // get the excel file data and assign it in OleDbcoomad object(o_cmd)
    
                    OleDbCommand o_cmd = new OleDbCommand("select*from [Sheet1$]", my_con);
    
                    // read the excel file data and assing it o_dr object
    
                    OleDbDataReader o_dr = o_cmd.ExecuteReader();
    
                    while (o_dr.Read())
                    {
    
                        //get first row data and assign it ex_id variable
    
                        //get first row data and assign it ex_id variable
    
                        ex_id = o_dr[0].ToString();
    
                        //get second row data and assign it ex_name variable
                        ex_name = o_dr[1].ToString();
    
                        //get thirdt row data and assign it ex_name variable
    
                        ex_age = o_dr[2].ToString();
    
                        //get first row data and assign it ex_location variable
    
                        //ex_location = o_dr[3].ToString();
                        //ex_hiredate = o_dr[4].ToString();
                        // create a connection string with your sql database
    
                        SqlConnection con = new SqlConnection("Data Source=AT\\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=SilverProduction;MultipleActiveResultSets=True;");
    
                        con.Open();
    
                        //insert excel data in student table
    
                        SqlCommand cmd = new SqlCommand("MasterInsertOderDetial", con);
    
                        cmd.CommandType = CommandType.StoredProcedure;
    
                        cmd.Parameters.Add(new SqlParameter("@id", ex_id));
                        cmd.Parameters.Add(new SqlParameter("@sname", ex_name));
                        cmd.Parameters.Add(new SqlParameter("@sage", ex_age));
                        //cmd.Parameters.Add(new SqlParameter("@st_location", ex_location));
                      //  cmd.Parameters.Add(new SqlParameter("@shiredate", ex_hiredate));
                        DataSet ds = new DataSet();
    
                        int i = cmd.ExecuteNonQuery();
    
                        if (i > 0)
                        {
    
                            Label1.Text = "Data inserted successfully";
    
                        }
    
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
    
    
                        // Assign da object data to dataset (virtual table)
    
                        da.Fill(ds);
    
                        // assign dataset data to gridview control 
    
                        GridView1.DataSource = ds;
    
                        GridView1.DataBind();
    
                        my_con.Close();
    
                    }
    
                }
    
                catch (Exception ex)
                {
    
                    Label1.Text = ex.Message;
    
                }
    
            }
    
    
    
    
    
            protected void Button2_Click(object sender, EventArgs e)
            {
                //string myfile_name = Path.GetFileName(FileUpload1.PostedFile.FileName);
    
                // Assign session object data in myfile_name variable
    
                string myfile_name = Session["Book22"].ToString();
    
                // Get the file(excel)complete path and assign in Excel_path variable
    
                string Excel_path = Server.MapPath(myfile_name);
    
                // create connection with excel database 
    
                OleDbConnection my_con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Excel_path + ";Extended Properties=Excel 8.0;Persist Security Info=False");
                my_con.Open();
    
                try
    
                {
    
                    // get the excel file data and assign it in OleDbcoomad object(o_cmd)
    
                    OleDbCommand o_cmd = new OleDbCommand("select*from [Sheet1$]", my_con);
    
                    //create oledbdataadapter object
    
                    OleDbDataAdapter da = new OleDbDataAdapter();
    
                    // pass o_cmd data to da object
    
                    da.SelectCommand = o_cmd;
    
                    //create a dataset object ds
    
                    DataSet ds = new DataSet();
    
                    // Assign da object data to dataset (virtual table)
    
                    da.Fill(ds);
    
                    // assign dataset data to gridview control 
    
                    GridView1.DataSource = ds.Tables[0];
    
                    GridView1.DataBind();
    
                    my_con.Close();
    
                }
    
                catch (Exception ex)
    
                {
    
                    Label1.Text = ex.Message;
                }
    
            }
    
        }
    }

    My Table column is OderID,ItemCode,Orderqty.

    OderID is auto generated...which i am not mentioning in my excel sheet

    Thursday, July 25, 2019 4:54 AM
  • Hi Akhterhussain,

    Thanks for the feedback.

    >>Thanks For Reply but i do not want to mentioned orderdetail ID in excel sheet it is auto generated ...

    Could you describe it more clearly? I am still not sure what you mean. Where is your auto generated filed, in excel or in database table?

    If your initial sql table data is null , you could refer to the code I provided to add all the excel file data to the table.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 25, 2019 5:33 AM
    Moderator
  • Hi jack ,

    i tired your instruction followed ,,error is coming that database and object is read only...

       string con = "Data Source = ATLANTIC\\SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = SilverProduction; MultipleActiveResultSets = True; ";
                        string Excel_path = "Server.MapPath(myfile_name);";
                        SqlConnection connection = new SqlConnection(con);
                        connection.Open();
                        SqlBulkCopy sqlBulk = new SqlBulkCopy(connection);
                        sqlBulk.DestinationTableName = "OrderDetail";
                        var dt = Exceldatatable(Excel_path);
                        sqlBulk.WriteToServer(dt);
                        connection.Close();
                      //  MessageBox.Show("success");
                    }
            public DataTable Exceldatatable(string Excel_path)
            {
                DataTable dt = new DataTable();
                string connectionString=@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Excel_path + ";Extended Properties=Excel 8.0;Persist Security Info=False";
                using (OleDbConnection conn = new OleDbConnection(connectionString))
                {
                    using (OleDbCommand comm = new OleDbCommand())
                    {
                        string sheetName = "Sheet1";
                        comm.CommandText = "Select * from [" + sheetName + "$]";
                        comm.Connection = conn;
                        using (OleDbDataAdapter da = new OleDbDataAdapter())
                        {
                            da.SelectCommand = comm;
                            da.Fill(dt);
                            return dt;
                        }
                    }

    Thursday, July 25, 2019 6:04 AM
  • Hi Akhterhussain,

    Thanks for the feedback.

    Based on my search, you could try add 'READONLY=FALSE;' to Oledb connection string to solve the problem.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 25, 2019 6:41 AM
    Moderator
  • Hi Jack another issue is coming

    : 'Could not find installable ISAM

    Thursday, July 25, 2019 7:27 AM
  • Hi Akhterhussain,

    Thanks for the feedback.

    There are many reasons that could throw the exception, I am not sure which solution will solve your problem. Therefore, I suggest that you could use the following code to convert excel to datatable.

          public DataTable READExcel(string path)
            {
                Microsoft.Office.Interop.Excel.Application objXL = null;
                Microsoft.Office.Interop.Excel.Workbook objWB = null;
                objXL = new Microsoft.Office.Interop.Excel.Application();
                objWB = objXL.Workbooks.Open(path);
                Microsoft.Office.Interop.Excel.Worksheet objSHT = objWB.Worksheets[1];
    
                int rows = objSHT.UsedRange.Rows.Count;
                int cols = objSHT.UsedRange.Columns.Count;
                DataTable dt = new DataTable();
                int noofrow = 1;
    
                for (int c = 1; c <= cols; c++)
                {
                    string colname = objSHT.Cells[1, c].Text;
                    dt.Columns.Add(colname);
                    noofrow = 2;
                }
    
                for (int r = noofrow; r <= rows; r++)
                {
                    DataRow dr = dt.NewRow();
                    for (int c = 1; c <= cols; c++)
                    {
                        dr[c - 1] = objSHT.Cells[r, c].Text;
                    }
    
                    dt.Rows.Add(dr);
                }
    
                objWB.Close();
                objXL.Quit();
                return dt;
            }

    Note: please install Nuget Packages->Microsoft.Office.Interop.Excel

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 25, 2019 7:55 AM
    Moderator
  • i did my yet same problem

    'Could not find installable ISAM

    Thursday, July 25, 2019 8:30 AM
  • Hi 

    I noted that the code you provided has the following code.

    OleDbConnection my_con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Excel_path + ";Extended Properties=Excel 8.0;Persist Security Info=False");

    I want to know which line occurs the exception that 'Could not find installable ISAM' by using Oledb method.

    I am confused that why you used the same way with me, which will have different results.

    Note: Please check that if your computer Microsoft Access Database Engine 2010 Redistributable software. If not, please install it.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 25, 2019 8:51 AM
    Moderator
  • i have installed access engine same issue 

    Thursday, July 25, 2019 11:06 AM
  • Hi Akhterhussain,

    Thanks for the feedback.

    According to the picture, I find the error and know how to solve it. Please use the following correct connectionstring. 

    string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";

    Note:Extended Properties should be Excel 12.0 instead of Excel 8.0.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Friday, July 26, 2019 5:28 AM
    Moderator