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_insertbooksconn); cmdinsert.CommandType = CommandType.StoredProcedure; try { conn.Open(); foreach( DataRow dr in myDataSet.Tables[0].Rows) { SqlParameter param = new SqlParameter(@bookid SqlDbType.Int4); param.Value = dr[0]; cmdinsert.Parameters.Add(param); SqlParameter param1 = new SqlParameter(@bookname SqlDbType.VarChar255); param1.Value = dr[1]; cmdinsert.Parameters.Add(param1); SqlParameter param2 = new SqlParameter(@bookdesc SqlDbType.VarChar255); 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?