Spring batch to upload a CSV file and insert into database

Question

My project has this requirement where user uploads a CSV file which has to be pushed to sql server database. I know we can use Spring batch to process large number of records. But I'm not able to find any tutorial/sample code for this requirement of mine. All the tutorials which I came across just hardcoded the CSV file name and in-memory databases in it like below:

https://spring.io/guides/gs/batch-processing/

User Input file is available in shared drive location on schduled time with file name prefix as eg: stack_overlfow_dd-MM-yyyy HH:mm, on daily basis how can I poll the Network shared drive for every 5-10 minutes atleast for one hour daily if its matches with regex then upload to database. How can I take the csv file first from shared location and store it in memory or somewhere and then configure spring batch to read that as input. any help here would be appreciated. Thanks In advance

Mahmoud Ben Hassine · Answer 1 · 2018-09-03T08:56:15.250

All the tutorials which I came across just hardcoded the CSV file name and in-memory databases

You can find samples in the official repo here. Here is an example where the input file name is not hardcoded but passed as a job parameter.

How can I take the csv file first from shared location and store it in memory or somewhere and then configure spring batch to read that as input.

You can proceed in two steps: download the file locally then read/process/write it to the database (See https://stackoverflow.com/a/52110781/5019386).

how can I poll the Network shared drive for every 5-10 minutes atleast for one hour daily if its matches with regex then upload to database.

Once you have defined your job, you can schedule it to run when you want using:

a scheduler like Quartz
or using Spring's task scheduling features.
or using a combination of Spring Integration and Spring Batch. Spring integration would poll the directory and then launches a Spring Batch job when appropriate. This approach is described here.

More details on job scheduling here.

Gustavo Passini · Answer 2 · 2018-09-02T03:09:43.860

0

I believe your question have already been answered here.

The author of the question has even uploaded a repository of his working result : https://github.com/PriyankaBolisetty/SpringBatchUploadCSVFileToDatabase/tree/master/src/main/java/springbatch_example

You can retrieve and filter files' lists in a shared drive using JCIFS API method SmbFile.listFiles(String wildcard).

edited Sep 02 '18 at 03:09

answered Sep 01 '18 at 19:30

Gustavo Passini

2,348
19
25

Thanks for refering answers, I have couple of questions; our shared drive input file contains 3-4 millon records, Is there any approach to load it in in-memory copy? What is the significance of multipart file approach, why not we copy complete file at a time from shared drive and load to db using spring batch as given https://stackoverflow.com/questions/10599999/write-i-o-file-to-shared-network-drive-using-credentials? – MukthyarStack Sep 01 '18 at 21:12
You are welcome. The MultipartFile would do exactly what you are asking for: storing files contents in memory or temporarily on disk, depending on their sizes (source: https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/web/multipart/MultipartFile.html). I updated my answer hoping it would give you an idea of how to select the files you want to process. – Gustavo Passini Sep 02 '18 at 03:06

score 0 · Answer 3 · answered Sep 01 '18 at 19:34

You can make a service layer that can process excel file and read data from file and construct java object to save into DB. Here I have used apache POI to parse Excel data and read from excel sheet.

public class FileUploadService {

 @Autowired
 FileUploadDao fileUploadDao;

 public String uploadFileData(String inputFilePath) {
  Workbook workbook = null;
  Sheet sheet = null;
  try {

   workbook = getWorkBook(new File(inputFilePath));
   sheet = workbook.getSheetAt(0);

   /*Build the header portion of the Output File*/
   String headerDetails = "EmployeeId,EmployeeName,Address,Country";
   String headerNames[] = headerDetails.split(",");

   /*Read and process each Row*/
   ArrayList < ExcelTemplateVO > employeeList = new ArrayList < > ();
   Iterator < Row > rowIterator = sheet.iterator();

   while (rowIterator.hasNext()) {
    Row row = rowIterator.next();
    //Read and process each column in row
    ExcelTemplateVO excelTemplateVO = new ExcelTemplateVO();
    int count = 0;
    while (count < headerNames.length) {
     String methodName = "set" + headerNames[count];
     String inputCellValue = getCellValueBasedOnCellType(row, count++);
     setValueIntoObject(excelTemplateVO, ExcelTemplateVO.class, methodName, "java.lang.String", inputCellValue);
    }

    employeeList.add(excelTemplateVO);
   }
   fileUploadDao.saveFileDataInDB(employeeList);

  } catch (Exception ex) {
   ex.printStackTrace();
  }


  return "Success";
 }

Thanks, my major concern is how can we poll or listen the shared drive file with regex configured before we launch the spring batch load process. — MukthyarStack, Sep 01 '18 at 21:40

Spring batch to upload a CSV file and insert into database

3 Answers3