How to insert a date (12-dec-2020) from excel to mysql

Question

i got the data from in excel file and pet it into an array string, the date is in the format (12/12/2020) whine i print it withe the method toString() i get (12-dec-2020), and when i tried simpledateformat codes that i found i get the errors "unparseable date java" or "SimpleDateFormat Cannot format given Object as a Date" or "The import java.text.SimpleDateFormat cannot be resolved"can anyone help me please? my code:

for (int row =0;row<sheet.getLastRowNum()+1;row ++) {
XSSFRow erow = sheet.getRow(row);
for(int column =0;column<sheet.getRow(0).getLastCellNum();column++) {
XSSFCell ecolumn = erow.getCell(column);
exdata[row][column]= String.valueOf(ecolumn);
    }
} 
String stringDate = new SimpleDateFormat("MM/yyyy").format(exdata[1][5].toString());
Date date = (Date) new SimpleDateFormat("yyyy-MM-dd").parse(stringDate);
System.out.println(String.valueOf(date));

The Excel format appears to be `dd-MMM-yyyy`, but you may have issues with the fact that the month is in lower case. You should also be making use of `LocalDate` and the associated `java.time.*` APIs over the `java.util.*` based date/time APIs — MadProgrammer, Feb 22 '23 at 21:59
I strongly recommend that you don’t use `SimpleDateFormat` and `Date`. Those classes were troublesome and are fortunately long outdated. Can’t you simply use [`XSSFCell.getLocalDateTimeCellValue()`](https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/usermodel/XSSFCell.html#getLocalDateTimeCellValue--)? — Ole V.V., Feb 23 '23 at 05:53
I suggest that this is partially a duplicate of [Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2](https://stackoverflow.com/questions/43039614/insert-fetch-java-time-localdate-objects-to-from-an-sql-database-such-as-h2). For converting the `LocalDateTime` from the cell to a `LocalDate` (without time of day), simply use `ecolumn.getLocalDateTime().toLocalDate()`. — Ole V.V., Feb 23 '23 at 14:28

score 3 · Accepted Answer · answered Feb 22 '23 at 22:27

Start by making use of the java.time.* APIs over the, out-of-date, effectively deprecated, java.util.* date/time APIs.

Next, the Excel format seems to be in dd-MMM-yyy not, MM/yyyy. You may also have issues because the month is in lower case, lucky, there's a relatively easy way to get around this...

DateTimeFormatter formatter = new DateTimeFormatterBuilder()
        .parseCaseInsensitive()
        .appendPattern("dd-MMM-yyyy")
        .toFormatter();
LocalDate ld = LocalDate.parse("12-dec-2020", formatter);

Inserting the value into MySQL shouldn't be complicated, but will depend on the column data type. If you're using Date (as the column type - no I'm not a MySQL expert), then you can use something like java.sql.Date.valueOf(ld); to get a SQL Date which you could use as part of a PreparedStatement.

As stated here, "JDBC 4.2 compliant drivers can deal directly with java.time objects", so even better, just bind the ld object to the PreparedStatement!

If the column is text, then you're doing it wrong.

How to insert a date (12-dec-2020) from excel to mysql

1 Answers1