read data from excel file and save into sql server table

Question

HI I have excel file (.xls), i want read data from excel file and save into sql server database table in c# console. EmpId FirstName LastName Desc Remark 101 A B 101 - A AA

Amit Mohanty · Answer

Try this System.Data.OleDb.OleDbConnection ExcelConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\your_path\Import_List.xls;Extended Properties=Excel 8.0;"); ExcelConnection.Open(); string expr = "SELECT * FROM [Sheet1$]"; OleDbCommand objCmdSelect = new OleDbCommand(expr, ExcelConnection); OleDbDataReader objDR = null; SqlConnection SQLconn = new SqlConnection(); string ConnString = "Data Source=.;Initial Catalog=your_dbname;Trusted_Connection=True;"; SQLconn.ConnectionString = ConnString; SQLconn.Open(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SQLconn)) { bulkCopy.DestinationTableName = "your-table-name"; try { objDR = objCmdSelect.ExecuteReader(); bulkCopy.WriteToServer(objDR); ExcelConnection.Close(); SQLconn.Close(); } catch (Exception ex) MessageBox.Show(ex.ToString()); }

Leon D · Answer

You can refer to the below article to read data from Excel to a datatable, then insert the datatable into the database. https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Excel-Import/Export-Import-Data-from-Excel-to-Datatable.html

Vishal Yelve · Answer

Please refer my below article, how to read data from excel file. https://www.c-sharpcorner.com/article/how-to-read-data-from-an-excel-sheet/

Vishal Joshi · Answer

Hello Please check the below article for the same. https://www.c-sharpcorner.com/blogs/import-excel-spreadsheet-data-into-sql-server-table-using-c-sharp Thanks

EmpId	FirstName	LastName	Desc	Remark
101	A	B	101 - A	AA