Schedule a Task(Upload Excel Data To Database) in a Specified Time Period
Get the source code form this path DOwnload Code
CallSchduler.java
//Main Class
package Scheduler; import java.io.*; import java.util.*; public class CallSchduler { UpdateSchduler upObj = null; public void stopScheduler() { upObj.cancelSchedule(); } public static void main(String args[]) { // System.out.println("Scheduler is called by main method"); try { File propertyFile = new File("D:\\Demo File\\Resources.properties"); if (propertyFile.exists()) { Properties properties = new Properties(); FileInputStream fileInputStream = new FileInputStream(propertyFile); properties.load(fileInputStream); int minutes = Integer.parseInt(properties.getProperty("Minutes")); String uploadFilePath = properties.getProperty("myExcelPath").toString(); String failedRecordsPath = properties.getProperty("failedFiles").toString(); UpdateSchduler upObj = new UpdateSchduler(minutes); upObj.updateFile(uploadFilePath, failedRecordsPath); } else { System.out.println("File not found!"); } } catch (Exception ex) { System.out.println("in Exception: " + ex.getMessage()); } } } UpdateSchduler.java package Scheduler; import java.io.File; import java.io.FileInputStream; import java.util.Properties; import java.util.Timer; import java.util.TimerTask; public class UpdateSchduler { private final Timer timer = new Timer(); private final int minutes; private String uploadFilePath = ""; private String FailedRecordsPath = ""; public UpdateSchduler(int minutes) { this.minutes = minutes; } public void start() { timer.schedule(new TimerTask() { public void run() { fileSelection(uploadFilePath, FailedRecordsPath); } }, 0, minutes * 60 * 1000); } public void cancelSchedule() { timer.cancel(); } public void updateFile(String uploadFilePath, String FailedRecordsPath) { this.uploadFilePath = uploadFilePath; this.FailedRecordsPath = FailedRecordsPath; start(); } public void stopUpdating() { cancelSchedule(); } public void fileSelection(String uploadRecordPath, String failedRecordsPath) { System.out.println("Upload File Path:" + uploadRecordPath); System.out.println("Failed Record File Path:" + failedRecordsPath); String filesInTheUplaodPath = null; File uplaodFile = new File(uploadRecordPath); if (uplaodFile.exists()) { if (uplaodFile.listFiles().length != 0) { File[] listOfFiles = uplaodFile.listFiles(); System.out.println("No. Files In the UPload Record path:" + listOfFiles.length); for (int i = 0; i < listOfFiles.length; i++) { if (listOfFiles[i].isFile()) { filesInTheUplaodPath = listOfFiles[i].getName(); //System.out.println("the files are " + files); JavaSheet readObj = new JavaSheet(); readObj.javaSheet(uploadRecordPath + filesInTheUplaodPath, failedRecordsPath + filesInTheUplaodPath); } } } else { System.out.println("No files Found"); } } else { System.out.println("File Path Not Found"); } } } JavaSheet .java package Scheduler; import java.sql.Statement; import java.io.DataInputStream; import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.util.List; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import org.apache.commons.fileupload.FileItemFactory; import org.apache.commons.fileupload.disk.DiskFileItemFactory; import org.apache.commons.fileupload.servlet.ServletFileUpload; /** * * @author Sravan */ public class JavaSheet { StringBuffer sb = new StringBuffer(); int count = 0; String EmpName = ""; String Dept = ""; String mobileNO = ""; String file_name = ""; String extension = ""; InputStream input = null; Connection con = null; FileItemFactory factory = new DiskFileItemFactory(); // System.out.println("1"); ServletFileUpload upload = new ServletFileUpload(factory); //System.out.println("2"); List items = null; CallableStatement insertQuery; DataInputStream in; private boolean isFileWrite = false; private FileInputStream fis; private File inputWorkbook; public void javaSheet(String file_name1, String failedFIle) { this.file_name = file_name1; sb.append("Employee Name" + ","); sb.append("Department" + ","); sb.append("MobileNO" + ","); sb.append("Reason" + ","); sb.append("\n"); extension = file_name.substring(file_name.indexOf("."), file_name.length()); System.out.println("" + extension); try { if (extension.equalsIgnoreCase(".xls") || extension.equalsIgnoreCase(".xlsx")) { inputWorkbook = new File(file_name); fis = new FileInputStream(inputWorkbook); Workbook w; ResultSet rs = null; w = Workbook.getWorkbook(fis); Sheet sheet = w.getSheet(0); String reason[] = new String[sheet.getRows() + 1]; String rowValues[] = new String[sheet.getColumns()]; System.out.println("no of rows :" + sheet.getRows()); if (sheet.getRows() > 1) { for (int row = 1; row < sheet.getRows(); row++) { for (int col = 0; col < sheet.getColumns(); col++) { Cell cell = sheet.getCell(col, row); rowValues[col] = cell.getContents(); } String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; String connectionURL = "jdbc:sqlserver://Ip:portNo;databaseName=Dbname;user=sa;password=xyz"; Class.forName(driver); con = DriverManager.getConnection(connectionURL); reason[row] = ""; EmpName = rowValues[0]; Dept = rowValues[1]; mobileNO = rowValues[2]; if (EmpName == null || EmpName.trim().equals("")) { reason[row] = reason[row] + "Emp Name is Null"; } if (Dept == null || Dept.trim().equals("")) { reason[row] = reason[row] + "Department Value is Null"; } if (mobileNO == null || mobileNO.trim().equals("")) { reason[row] = reason[row] + "Mobile No is Null"; } if (reason[row] == null) { String insertQuery = "insert into EMPTable (EMP_Name ,EMP_Dept ,EMP_MobileNO) values ('" + EmpName + "','" + Dept + "','" + mobileNO + "')"; System.out.println(insertQuery); Statement stmt = con.createStatement(); int res = stmt.executeUpdate(insertQuery); stmt.close(); if (res == 0) { reason[row] = "Failed When Inserting"; } else { count++; System.out.println("rows inserted:" + count); } } else { System.out.println(" In Fail records"); // // sb.append("Employee Name" + ","); // sb.append("Department" + ","); // sb.append("MobileNO" + ","); // sb.append("Reason" + ","); // sb.append("\n"); //END isFileWrite = true; if (EmpName == null || EmpName.equals("")) { EmpName = " "; } if (Dept == null || Dept.equals("")) { Dept = " "; } if (mobileNO == null || mobileNO.equals("")) { mobileNO = " "; } sb.append(EmpName + ","); sb.append(Dept + ","); sb.append(mobileNO + ","); sb.append(reason[row] + ","); sb.append("\n"); } System.out.println("ZZZZZZZZZ" + isFileWrite); System.out.println("" + sb.length()); System.out.println("" + sb); if (isFileWrite || sb.length() != 0) { new FailedRecords().getFialedRecords(failedFIle, sb.toString()); } } System.out.println("END"); } else { System.out.println(" Empty XLS File"); } } else { System.out.println(" Invalid XLS File"); } } catch (Exception e) { System.out.println("Pls Select The Given format of xl file" + e.getMessage()); e.printStackTrace(); } finally { try { if (insertQuery != null) { insertQuery.close(); } if (con != null) { con.close(); } if (fis != null) { fis.close(); inputWorkbook.delete(); } } catch (Exception e) { System.out.println("EXCEPTION IN CLOSING CONNECTION:" + e); } } System.out.println("END"); } } FailedRecords.java package Scheduler; import java.io.BufferedWriter; import java.io.File; import java.io.FileWriter; import java.text.SimpleDateFormat; /** * * @author Sravan */ public class FailedRecords { public void getFialedRecords(String failedRecordsFile, String FailedRecords) { System.out.println("Inisde Fialed Records"); String file_name = failedRecordsFile; File file = new File(file_name); System.out.println("Filename::::" + file_name); String buffer = FailedRecords; String date = ""; String filename = ""; SimpleDateFormat df1 = new SimpleDateFormat("dd-MMM-yyyy-kk.mm.ss"); date = df1.format(new java.util.Date()); try { BufferedWriter out = new BufferedWriter(new FileWriter(file)); out.write(buffer); out.close(); } catch (Exception e) { System.out.println("Ex:" + e); } } }