Import Data from Excel file

Question

Hello Guys,

I m Having a problem while uploading and Importing data from Excel to SQL Server table.

please help....

i am using following code....

 

 

 

if(file_upload_ex.HasFile)

{

//Upload and save the file

string excelPath = Server.MapPath("~/Files") + Path.GetFileName(file_upload_ex.PostedFile.FileName);

file_upload_ex.SaveAs(excelPath);

string conString = string.Empty;

string extension = Path.GetExtension(file_upload_ex.PostedFile.FileName);

switch (extension)

{

case ".xls": //Excel 97-03

conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;

break;

case ".xlsx": //Excel 07 or higher

conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;

break;

}

conString = string.Format(conString, excelPath);

using (OleDbConnection excel_con = new OleDbConnection(conString))

{

excel_con.Open();

string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["questions"].ToString();

DataTable dtExcelData = new DataTable();

//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.

dtExcelData.Columns.AddRange(new DataColumn[13] { new DataColumn("set_id", typeof(int)),

new DataColumn("center_id", typeof(int)),

new DataColumn("ques_no", typeof(int)),

new DataColumn("exam_id", typeof(int)),

new DataColumn("category_id", typeof(string)),

new DataColumn("subcategory_id", typeof(string)),

new DataColumn("ques", typeof(string)),

new DataColumn("option_A", typeof(string)),

new DataColumn("option_B", typeof(string)),

new DataColumn("option_C", typeof(string)),

new DataColumn("option_D", typeof(string)),

new DataColumn("correct_ans", typeof(string)),

new DataColumn("marks",typeof(decimal)) });

using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))

{

oda.Fill(dtExcelData);

}

excel_con.Close();

string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

using (SqlConnection con = new SqlConnection(consString))

{

using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))

{

//Set the database table name

sqlBulkCopy.DestinationTableName = "online_exam.questions";

//[OPTIONAL]: Map the Excel columns with that of the database table

sqlBulkCopy.ColumnMappings.Add("set_id", "set_id");

sqlBulkCopy.ColumnMappings.Add("center_id", "center_id");

sqlBulkCopy.ColumnMappings.Add("ques_no", "quesion_no");

sqlBulkCopy.ColumnMappings.Add("exam_id", "exam_id");

sqlBulkCopy.ColumnMappings.Add("category_id", "category_id");

sqlBulkCopy.ColumnMappings.Add("subcategory_id", "subcategory_id");

sqlBulkCopy.ColumnMappings.Add("ques", "questions");

sqlBulkCopy.ColumnMappings.Add("option_A", "option_A");

sqlBulkCopy.ColumnMappings.Add("option_B", "option_B");

sqlBulkCopy.ColumnMappings.Add("option_C", "option_C");

sqlBulkCopy.ColumnMappings.Add("option_D", "option_D");

sqlBulkCopy.ColumnMappings.Add("correct_ans", "correct_answer");

sqlBulkCopy.ColumnMappings.Add("marks", "marks");

con.Open();

sqlBulkCopy.WriteToServer(dtExcelData);

con.Close();