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

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.

Answer 1

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" />
    </form>

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