how to transfer data from excel to sql server table

Question

hi what i have here is a situation wherein i want to Using C# import Excel file into a SQL Server table . can someone tell how to go about doing this... thanx for the help. vic.

viktors · Answer

hey thanks everyone, i figured a way out..for all those who get in a situation like this..here is the code.. string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + //"Data Source=" + File1.Value + ";" + "Data Source=C:\Book1.xls;" + "Extended Properties=Excel 8.0;"; //You must use the $ after the object you reference in the spreadsheet OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn); DataSet myDataSet = new DataSet(); myCommand.TableMappings.Add("Table" , "ExcelInfo"); myCommand.Fill(myDataSet); DataGrid1.DataSource = myDataSet.Tables[0].DefaultView; DataGrid1.DataBind(); SqlConnection conn = new SqlConnection(ConnStr); SqlCommand cmdinsert = new SqlCommand ("sp_insertbooks",conn); cmdinsert.CommandType = CommandType.StoredProcedure; try { conn.Open(); foreach( DataRow dr in myDataSet.Tables[0].Rows) { SqlParameter param = new SqlParameter("@bookid", SqlDbType.Int,4); param.Value = dr[0]; cmdinsert.Parameters.Add(param); SqlParameter param1 = new SqlParameter("@bookname", SqlDbType.VarChar,255); param1.Value = dr[1]; cmdinsert.Parameters.Add(param1); SqlParameter param2 = new SqlParameter("@bookdesc", SqlDbType.VarChar,255); param2.Value = dr[2]; cmdinsert.Parameters.Add(param2); cmdinsert.ExecuteNonQuery(); cmdinsert.Parameters.Remove(param); cmdinsert.Parameters.Remove(param1); cmdinsert.Parameters.Remove(param2); } Response.Write(" Record Added "); } catch (Exception ex) { Response.Write(ex.ToString()); } finally { cmdinsert.Connection.Close(); } And here is the stored proc for insertion.. create proc dbo.sp_insertbooks @bookid int, @bookname varchar(255), @bookdesc varchar(255) as SET NOCOUNT ON insert into Book values(@bookid, @bookname,@bookdesc)

viktors · Answer

how can i update all the rows from the datagrid to a sql table or is there some other way.. ur help is appreciated.. this is the code i have done till now extraction from an excel sheet to a datagrid.. string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + File1.Value + ";" + "Extended Properties=Excel 8.0;"; Response.Write (strConn); //Response.End(); //You must use the $ after the object you reference in the spreadsheet OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn); DataSet myDataSet = new DataSet(); myCommand.TableMappings.Add("Table" , "ExcelInfo"); myCommand.Fill(myDataSet); DataGrid1.DataSource = myDataSet.Tables[0].DefaultView; DataGrid1.DataBind(); thanks, vic.

jinwolf · Answer

you can select the columns make a separate worksheet with only the columns you want, from this create a CSV file, then use the Import wizard to import the file.

viktors · Answer

what i want to do is read an excel file and extract selected columns from it and then eventually load it in a sql table. i have been able to read the excel file, show it in a datagrid and have the info. in a dataset. i dont know how to proceed....how to insert all this into the sql table....

viktors · Answer

i want to pull single cell values out and build up a table in sql . thanks for the help..

sjones · Answer

Do you want to store the whole workbook as a blob, or are you wanting to pull single cell values out and build multiple records?

how to transfer data from excel to sql server table

Answers (6)