Import a Excel sheet into MS SQL database

Question

I have Excel 2007 and Visual Web Developer Express 2010. I would like to import Sheet1 of an xlsx file the reader then add the data to a dataset and placing that data in a MS SQL database.

 string ExcelConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES"; string SQLConStr = "got connection string that works"; OleDbConnection ExcelConnection = new OleDbConnection(ExcelConStr); using (ExcelConnection) { string sql = string.Format("Select * FROM [{0}]", "Sheet1$"); OleDbCommand command = new OleDbCommand(sql, ExcelConnection); ExcelConnection.Open(); using (OleDbDataReader dr = command.ExecuteReader()) { using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SQLConStr)) { bulkCopy.DestinationTableName = "dbo.databaseName"; bulkCopy.WriteToServer(dr); } } }

I need something like bulkcopy that is free and easy to use if someone may make a recommendation.

Shirish · Accepted Answer · 2014-01-18 04:27:29Z

Without bulk copy it will also work.. try this it will work 100% for .csv and .xlsx both... I m using it..

 string header = "No"; string sql = string.Empty; DataTable dt = new DataTable(); string pathOnly = string.Empty; string fileName = string.Empty; pathOnly = Path.GetDirectoryName(path); fileName = Path.GetFileName(path); if (IsFirstRowHeader) { header = "Yes"; } String cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=" + header + ";IMEX=1;\""; OleDbConnection con = new OleDbConnection(cs); if (con.State == ConnectionState.Closed) con.Open(); #region use to find sheet and fire query on sheetname DataTable dtsheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); String[] excelSheets = new String[dtsheets.Rows.Count]; int i = 0; // Add the sheet name to the string array. foreach (DataRow row in dtsheets.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString(); i++; } if (extension == ".csv") sql = "SELECT * FROM [" + fileName + "]"; else sql = "SELECT * FROM [" + excelSheets[0] + "]"; #endregion OleDbCommand command = new OleDbCommand(sql, con); OleDbDataAdapter adapter = new OleDbDataAdapter(command); dt.Locale = CultureInfo.CurrentCulture; adapter.Fill(dt); con.Close();

Collectives™ on Stack Overflow

Import a Excel sheet into MS SQL database

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related