Upload Monthly data

Question

Dear Sir, I have a table its contains EMP_code,date , status So i need to upload month data in a table can any one help to me Excel: Code 1/01/2018 2/01/2018 3/01/2018 4/01/2018 5/01/2018 1 P P P P A 2 P A P P P 3 P P P A P 4 P P P P P 5 P P P P P Through Excel sheet i have to save in the table please help to me sir

Dharmraj Thakur · Answer

Hellow, 1. Create following table in database 2. Read one by one rows from excel file. 3. Insert in below database tables. Database Tables: Employees ID Name 1 Yesubabu 2 Dharmraj Addendances ID EmployeeID Date Status 1 2 01/01/2018 1 2 2 02/01/2018 1 3 2 03/01/2018 1 4 2 04/01/2018 1 5 2 05/01/2018 0 6 1 01/01/2018 1 7 1 02/01/2018 0 8 1 03/01/2018 1 9 1 04/01/2018 1 10 1 05/01/2018 1

Altaf Ansari · Answer

Try This : private DataTable GetTableDataXl( string XlFile) { DataTable dt = new DataTable(); try { string Ext = Path.GetExtension(XlFile); string connectionString = "" ; if (Ext == ".xls" ) { //For Excel 97-03 connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" + XlFile + "; Extended Properties = 'Excel 8.0;HDR=YES'" ; } else if (Ext == ".xlsx" ) { //For Excel 07 and greater connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" + XlFile + "; Extended Properties = 'Excel 8.0;HDR=YES'" ; } OleDbConnection conn = new OleDbConnection(connectionString); OleDbCommand cmd = new OleDbCommand(); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(); cmd.Connection = conn; //Fetch 1st Sheet Name conn.Open(); DataTable dtSchema; dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null ); string ExcelSheetName = dtSchema.Rows[2][ "TABLE_NAME" ].ToString(); conn.Close(); //Read all data of fetched Sheet to a Data Table conn.Open(); cmd.CommandText = "SELECT * From [" + ExcelSheetName + "]" ; dataAdapter.SelectCommand = cmd; dataAdapter.Fill(dt); conn.Close(); } catch (Exception ex) { } return dt; }

Nitin Sontakke · Answer

You didn't mention the target database system. SQL Server, Oracle, MySQL? If it is SQL Server, you can use Import / Export wizard in SQL Server Management Studio.

Sasi Rekha · Answer

public ActionResult UploadExcel(HttpPostedFileBase FileUpload) { if (FileUpload != null) { if (FileUpload.ContentType == "application/octet-stream" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { string filename = FileUpload.FileName; string targetpath = Server.MapPath("~/Doc/"); FileUpload.SaveAs(targetpath + filename); string pathToExcelFile = targetpath + filename; var connectionString = ""; if (filename.EndsWith(".xls")) { connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", pathToExcelFile); } else if (filename.EndsWith(".xlsx")) { connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", pathToExcelFile); } var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString); var ds = new DataSet(); adapter.Fill(ds, "ExcelTable"); DataTable dtable = ds.Tables["ExcelTable"]; string sheetName = "Sheet1"; var excelFile = new ExcelQueryFactory(pathToExcelFile); var variableName= from a in excelFile.Worksheet (sheetName) select a; foreach (var a in variableName) { try { a.propertyname; a.propertyname2; }//try end } //forloop }//if }//if }//method

Bharathi Raja · Answer

id name status date end_date 1 test 1 5/01/2018 15/10/2019

Upload Monthly data

Answers (5)