Click here to Skip to main content
15,742,377 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hi, how to import excel to sql server using
I am using OLEDB Connection to import data.
The code-behind is

protected void BtnUpload_Click(object sender, EventArgs e)
            string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);


            string conString = string.Empty;

            string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);

            switch (extension)
                case ".xls": //Excel 97-03

                    conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;


                case ".xlsx": //Excel 07 or higher

                    conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;


            conString = string.Format(conString, excelPath);

            using (OleDbConnection excel_con = new OleDbConnection(conString))


                string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();

                DataTable dtExcelData = new DataTable();                
 string st = "Select [sno], [Id],[country],[Qtr]=case(substring([Qtr],1,2) as nvarchar(4)), [value]  from ["+sheet1+"] outer apply(values((N'Q1',Q1),(N'Q2',Q2)) P(Qtr,value)";              
  using (OleDbDataAdapter oda = new OleDbDataAdapter(st, excel_con))



                string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

                using (SqlConnection con = new SqlConnection(consString))

                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))

                        //Set the database table name

                        sqlBulkCopy.DestinationTableName = "[dbo].[exceldemo]";



                        lblUpload.Text = "Uploaded Succesfully";


while running the code I am getting the below error
"IErrorInfo.GetDescription failed with E_FAIL(0x80004005)."

at "oda.Fill(dtExcelData);"

below is the input and output format.[^]

please help me from this situation.

Thanks in advance.
Updated 13-Jul-15 2:55am

1 solution

You can use this using OLEDB dataprovider.
There are lots of solutions available over the internet. You need to follow one of any article which best suits your requirement. You can even download the demo projects (if any) and go through the code.
Please check these links-
How to Import Excel Sheet data into SQL Server using ASP.Net[^]
Import MS Excel data to SQL Server table using C#[^]
Import Excel Data to SQL Server in ASP.NET[^]

Hope, it helps :)
Share this answer
maddy472 29-Jun-15 3:17am    
Hi Shekhar, thank you for the solution. but my requirement is to transform data from columns to row like mentioned in the image (link). I hope you would help me
Suvendu Shekhar Giri 29-Jun-15 3:22am    
Oh! Actually those sites are blocked in my network. Will check the image later and try to come with a more appropriate solution.
maddy472 30-Jun-15 10:45am    
thank you for responding.

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