How to separate two dates from one columns - Read from external file

Question

So far I have been able to read from an external excel file which has two columns.

ID Date
1 12/12/2018 13/12/2018
2 12/12/2018 13/12/2018
3 12/12/2018 13/12/2018
4 12/12/2018 13/12/2018
5 12/12/2018 13/12/2018
6 12/12/2018 13/12/2018
7 12/12/2018 13/12/2018

My question is, is there any way possible to split that one column which contains two dates into another column or separate it in different method perhaps? The above is a prototype, the answer method will be used on big data. Any help will be highly appreicated, please.

Code used so far:

package readexcel;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;


public class Readexcel{

@SuppressWarnings({ "unchecked", "unchecked" })
public static void main(String[] args) throws Exception {

//
// An excel file name. You can create a file name with a full
// path information.
//
String filename = "C:\\filepath...xls";

//
// Create an ArrayList to store the data read from excel sheet.
//
List sheetData = new ArrayList();
FileInputStream fis = null;
try {
//
// Create a FileInputStream that will be use to read the
// excel file.
//
fis = new FileInputStream(filename);

//
// Create an excel workbook from the file system.
//
HSSFWorkbook workbook = new HSSFWorkbook(fis);
//
// Get the first sheet on the workbook.
//
HSSFSheet sheet = workbook.getSheetAt(0);

//
// When we have a sheet object in hand we can iterator on
// each sheet's rows and on each row's cells. We store the
// data read on an ArrayList so that we can printed the
// content of the excel to the console.
//
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
Iterator cells = row.cellIterator();

List data = new ArrayList();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
data.add(cell);
}

sheetData.add(data);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (fis != null) {
fis.close();
}
}

showExcelData(sheetData);
}

private static void showExcelData(List sheetData) {
//
// Iterates the data and print it out to the console.
//
for (int i = 0; i < sheetData.size(); i++) {
List list = (List) sheetData.get(i);
for (int j = 0; j < list.size(); j++) {
Cell cell = (Cell) list.get(j);
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.print(cell.getNumericCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
System.out.print(cell.getRichStringCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
System.out.print(cell.getBooleanCellValue());
}
if (j < list.size() - 1) {
System.out.print(", ");
}
}
System.out.println("");
}
}
}

Answer 1

    Pattern twoDates = Pattern.compile("(\\d+/\\d+/\\d+)\\W+(\\d+/\\d+/\\d+)");
    DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("d/M/u");

    String[] dateStrings = {
            "12/12/2018 13/12/2018",
            "12/12/2018-13/12/2018",
            "12/12/2018 - 13/12/2018"
    };
    for (String dateString : dateStrings) {
        Matcher m = twoDates.matcher(dateString);
        if (m.matches()) {
            try {
                String ds1 = m.group(1);
                LocalDate ld1 = LocalDate.parse(ds1, dateFormatter);
                String ds2 = m.group(2);
                LocalDate ld2 = LocalDate.parse(ds2, dateFormatter);
                System.out.println("First date: " + ld1 + " Second date: " + ld2);
            } catch (DateTimeParseException dtpe) {
                System.out.println("Invalid date in string " + dateString
                        + ": " + dtpe.getMessage());
            }
        } else {
            System.out.println("Not two dates: " + dateString);
        }
    }

This outputs:

First date: 2018-12-12 Second date: 2018-12-13
First date: 2018-12-12 Second date: 2018-12-13
First date: 2018-12-12 Second date: 2018-12-13

I first use a regular expression to pick out the two dates from the string. Next I want to use LocalDate.parse for parsing and validating the dates. You may want to add a range check for further validation. If so, use LocalDate.isBefore and/or .isAfter.