Import ecxcel data into ms access database using asp,net c# RRS feed

  • Question

  • User2003675111 posted

    Hi All,

    I have an excel sheet with name and address column and I want to import excel data into ms access .

    before inserting data into table I want to create auto increment ID column with primary key means whenever I will insert name and address field of excel then in table one ID column dynamically created and its value will be auto increment.

    Please Help

    Thanks in Advance.

    Monday, May 27, 2019 6:07 PM

All replies

  • User-893317190 posted

    Hi Neeraj Yadav,

    If you want to import data from excel to ms access, you could refer to the code below.

       <form id="form1" runat="server">
            <asp:FileUpload ID="FileUpload1" runat="server" />
            <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />

    Code behind.

       protected void Button1_Click(object sender, EventArgs e)
                string conString = string.Empty;
                // string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
                string extension = ".xlsx";
                switch (extension)
                    case ".xls": //Excel 97-03
                        FileUpload1.SaveAs(Server.MapPath("/office/101.xls")); // save the excel , you could specify your path
    // set conString according to path conString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ Server.MapPath("/office/101.xls") + "s;Extended Properties='Excel 8.0;HDR=YES'"; break; case ".xlsx": //Excel 07 or higher FileUpload1.SaveAs(Server.MapPath("/office/101.xlsx")); conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ Server.MapPath("/office/101.xlsx") + ";Extended Properties='Excel 8.0;HDR=YES'"; break; } using (OleDbConnection connection = new OleDbConnection(conString)) { connection.Open(); // get the name of first sheet string sheet2 = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString(); DataTable dtExcelData = new DataTable(); dtExcelData.Columns.AddRange(new DataColumn[2] { new DataColumn("name", typeof(string)), new DataColumn("address",typeof(string)) }); using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT Name,address FROM [" + sheet2 + "]", connection)) { oda.Fill(dtExcelData); }
    // please specify your connection string to ms access string consString = ConfigurationManager.ConnectionStrings["EntityExeConnectionString"].ConnectionString; using (SqlConnection con = new SqlConnection(consString)) { con.Open(); for (int i = 0; i < dtExcelData.Rows.Count; i++) { using (SqlCommand com = new SqlCommand("insert into contact values(@name,@address)", con)) { com.Parameters.AddWithValue("name", dtExcelData.Rows[i]["name"]); com.Parameters.AddWithValue("address", dtExcelData.Rows[i]["address"]); com.ExecuteNonQuery(); } } } } }

    Since you are using  ms access and I use  sql server , so you should use  OleDbConnection instead of  SqlConnection .

    Please change my sqlconnection, sql command, to corresponding  OleDbConnection  and OleDbCommand.

    For more information please refer to https://forums.asp.net/t/2040693.aspx?Use+SQL+Bulk+Copy+to+update+MS+Access+

    Best regards,

    Ackerly Xu

    Tuesday, May 28, 2019 4:02 AM