Import Excel into Sql Database
-
Hi!! Could somebody help me in converting an excel spreadsheet to an sql server database. I need some working around this.. i hv it working in vb but the C# doesnt seem to fetch the same code. ... Thanks in advance !!
-
Hi there, IMO, you can use the ADO.NET to read data from an excel sheet, then insert it into SQL db. You can see the sample code below on how to use the ADO.NET to query data from an excel file: http://support.microsoft.com/kb/306572/[^]
Well my problem with that particular eg is tat it uses range.. I want the user to simply click the browse button which will gv the loc of the sheet/workbook and then by simply clickin the import the stuff shd rest accurately into the sql db. lots of egs are seen in Vb but c#gives import to xml and then to db. Help ideas..pls!!
-
Hi!! Could somebody help me in converting an excel spreadsheet to an sql server database. I need some working around this.. i hv it working in vb but the C# doesnt seem to fetch the same code. ... Thanks in advance !!
Hi, try this. string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strfilename + ";Extended Properties=Excel 8.0;"; OleDbConnection mycon = new OleDbConnection(strConn); try { //Get the data from selected excel sheet. OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM ["+lstEmpSheets.SelectedValue+"]", strConn); if (mycon.State != ConnectionState.Open) //if connection is not open, open it mycon.Open(); //DataSet myDataSet = new DataSet(); myCommand.Fill(dt); mycon.Close(); } catch(Exception ex) { lblResult.Visible = true; lblResult.Text = ex.Message.ToString(); } finally { if (mycon.State == ConnectionState.Open) //if connection is not open, open it mycon.Close(); } try { if(dt!=null) { con = new SqlConnection((string) ConfigurationSettings.AppSettings["ConnectionString"]); con.Open(); cmd = new SqlCommand(); cmd.Connection=con; //Get the data from data table and update the database. foreach(DataRow dr in dt.Rows) { //Need to write relative code here. string empId = dr[0].ToString().Trim(); errRefEmp =empId; string name = dr[1].ToString().Trim(); string project = dr[2].ToString().Trim(); string supervisor= dr[3].ToString().Trim(); string mailId= dr[4].ToString().Trim(); if((empId.CompareTo("")==0)||(supervisor.CompareTo("")==0)|| string query = " INSERT INTO EMPLOYEE "; query += " VALUES ('"+empId+"','"+name+"','"+project+"','"+supervisor+"','"+mailId+"')"; cmd.CommandText=query; cmd.ExecuteNonQuery(); con.Close(); Page.RegisterStartupScript("SuccessfulEmpUpdation","alert('Excel file uploaded Successfully.!')"); Thanks and regards Gowtham sen