0

I've an excel sheet Book.xls like below:

   _________________________________________________________________
   |                                         |                      |
   |        INVOICE                          |        INVOICE_DATE  |
   |_________________________________________|______________________|
   |                                         |                      |
   |C-EDGE/SBI//BHO/(ATM)-013/2012–2013      |   11-Feb-2013        |
   |_________________________________________|______________________|
   |C-EDGE/SBI//BANG/(ATM)-013/2012–2013     |   13-Aug-2014        |
   |_________________________________________|______________________|

I am trying to insert Date from excel to MySQL table as below:

try {
    POIFSFileSystem fs = new POIFSFileSystem(input);
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    Row row;
    for (int i = 1; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        RichTextString CEDGE_INVOICE_NUMBER = row.getCell(0).getRichStringCellValue();
        Date INVOICE_DATE = row.getCell(1).getDateCellValue();
        System.out.println(INVOICE_DATE);
        String sql = "INSERT INTO tablename VALUES('" + CEDGE_INVOICE_NUMBER + "','" + INVOICE_DATE + "')";
        pstm = (PreparedStatement) con.prepareStatement(sql);
        pstm.execute();
        System.out.println("Import rows " + i);
    }
    con.commit();
    pstm.close();
    con.close();
    input.close();
    System.out.println("Success import excel to mysql table");
} catch (ClassNotFoundException e) {
    System.out.println(e);
} catch (SQLException ex) {
    System.out.println(ex);
} catch (IOException ioe) {
    System.out.println(ioe);
}

However, I got an exception:

"com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'Mon Feb 11 00:00:00 IST 2013' for column 'INVOICE_DATE' at row 1".

How is this caused and how can I solve it?

Improve this question
2
  • Try to parse INVOICE_DATE which your mysql database support.String date = new SimpleDateFormat("yyyy-MM-dd").format(row.getCell(1).getDateCellValue()); Commented Jun 3, 2015 at 9:39
  • Have a look here:- stackoverflow.com/questions/2400955/… Commented Jun 3, 2015 at 10:14

1 Answer 1

Reset to default
1

When using PreparedStatement, use ? placeholders for values; then you can use setDate method of the PreparedStatemet. E.g. in your code:

String sql ="INSERT INTO tablename VALUES(?,?)";
pstm = (PreparedStatement) con.prepareStatement(sql);

for(int i=1; i<=sheet.getLastRowNum(); i++){
    row = sheet.getRow(i);
    RichTextString CEDGE_INVOICE_NUMBER = row.getCell(0).getRichStringCellValue();
    String stringInvoiceNumber = ... // convert RichTextString to normal String

    Date INVOICE_DATE = row.getCell(1).getDateCellValue();
    // you have to use java.sql.Date instead of java.util.Date in PreparedStatement
    java.sql.Date sqlInvoiceDate = new java.sql.Date(INVOICE_DATE.getTime());

    pstm.setString(1, stringInvoiceNumber);
    pstm.setDate(2, sqlInvoiceDate);

    pstm.execute();

    ...    
}

See e.g. http://tutorials.jenkov.com/jdbc/preparedstatement.html for more info.

Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

Hello Jozef Thank you for your reply. If I want import only month and year what is the code. In my excel file had another column i.e., INVOICE_FOR MONTH and values are "Jan-2013". How can i import this month and year in MYSQL table.
And how does your mySQL table look like? Ideally add its CREATE TABLE statement to your question.
Hello Mr. Jozef I got a null pointer exception when my excel row is empty. It means the sheet has 190 rows in between those rows some are empty. In that situation I got java.lang.NullPointerException. Please help me this is my code.
@Lee001 I don't see any java code in your comment. Try to paste it e.g. in chopapp.com (or pastebin.com) and post a link here.
String sql ="INSERT INTO penality VALUES(?,?,?,?,?,?,?,?,?,?)"; pstm = (PreparedStatement) con.prepareStatement(sql); for(int i=1; i<=sheet.getLastRowNum(); i++){ row = sheet.getRow(i); RichTextString CEDGE_INVOICE_NUMBER = row.getCell(0).getRichStringCellValue(); String stringInvoiceNumber = "'"+CEDGE_INVOICE_NUMBER+"'";
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.