how to import excel to sqlserver with column transforming to row.

See more:

ASP.NET

MS-Excel

SQL-Server-2012

Hi, how to import excel to sql server using asp.net
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);

            FileUpload1.SaveAs(excelPath);

            string conString = string.Empty;

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

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

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

                    break;

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

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

                    break;
            }

            conString = string.Format(conString, excelPath);

            using (OleDbConnection excel_con = new OleDbConnection(conString))
            {

                excel_con.Open();

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

                {
                    oda.Fill(dtExcelData);
                }

                excel_con.Close();

                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]";
                       
                        con.Open();

                        sqlBulkCopy.WriteToServer(dtExcelData);

                        con.Close();

                        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.
http://i61.tinypic.com/217pk0.jpg[^]

please help me from this situation.



Thanks in advance.