Click here to Skip to main content
15,920,801 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear Sir,

I am tring to import data from excel sheet named mangalregion to sqldatatable named UnicodExcel


written the following code pllzzzz tell me where m i doing wrong



WEBCONFIG

C#
<add name="Excel03ConString" connectionstring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'" />
    <add name="Excel07+ConString" connectionstring="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'" />
    <add name="conString" connectionstring="Data Source=DEL-INTRASQL;Initial Catalog=Test;User ID=testu;Password=test;" providername="System.Data.SqlClient" />





DEfault Aspx

ASP.NET
<asp:FileUpload ID="FileUpload1" runat="server" />
           <asp:Button Text="Upload" OnClick = "Upload" runat="server" />



Aspx.cs

C#
protected void Upload(object sender, EventArgs e)
        {
            
            string excelPath = Server.MapPath("~/hindiregion/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
            FileUpload1.SaveAs(@"c:\\users\\anurag\\documents\\visual studio 2015\\Projects\\EXCELTOSQL\\EXCELTOSQL\\hindiregion\\hindiregion.xls");

            string conString = string.Empty;
            string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            switch (extension)
            {
                case ".xls": 
                    conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                    break;
                case ".xlsx": 
                    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]["unicodeexcel"].ToString();
                DataTable unicodeexcel = new DataTable();

                ////[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
                //dtExcelData.Columns.AddRange(new DataColumn[2] { new DataColumn("Name", typeof(string)),
                //new DataColumn("Mangalregion",typeof(String)) });

                using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + hindiregion + "]", excel_con))
                {
                    oda.Fill(unicodeexcel);
                }
                excel_con.Close();

                string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                using (SqlConnection con = new SqlConnection(consString))
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                    {
                        
                        sqlBulkCopy.DestinationTableName = "UnicodeExcel";

                        sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                        sqlBulkCopy.ColumnMappings.Add("Mangalregion", "Mangalregion");
                        con.Open();
                        sqlBulkCopy.WriteToServer(unicodeexcel);
                        con.Close();
                    }
                }
            }
        }
    }
}




Please Revert Asap
humble request
Posted
Updated 5-Oct-15 21:47pm
v3
Comments
Member 11999718 6-Oct-15 3:02am    
Dear Sir,

please specify the Error m getting in line

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

error is
An exception of type 'System.ArgumentException' occurred in System.Data.dll but was not handled in user code

Additional information: Column 'unicodeexcel' does not belong to table Tables.

1 solution

please specify the Error m getting in line

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

error is
An exception of type 'System.ArgumentException' occurred in System.Data.dll but was not handled in user code

Additional information: Column 'unicodeexcel' does not belong to table Tables.


Look at the error message, it's pretty explicit:
Column 'unicodeexcel' does not belong to table Tables


So look at your database and see what tables and columns you have defined.
 
Share this answer
 

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