CODEWITHSUNDEEP
javamysqlexcel
Sachin HR
Sachin HR

Reputation: 460

Not inserting from excel to mysql if particular column is null in java

I am trying to insert excel sheet data into mysql db.Everything works if column has data.But if column is null then the program stops execution and data is not inserted.

Here is how i am inserting excel data

TestApp.java

    public class TestApp {
    public static void main(String[] args) throws Exception {
    try {
    Class forName = Class.forName("com.mysql.jdbc.Driver");
    Connection con = null;
    con = 
    DriverManager.getConnection("jdbc:mysql://192.168.1.13:3307/db1",
    "sachin-LH", "s123");
    con.setAutoCommit(false);
    PreparedStatement pstm = null;
    FileInputStream input = new 
    FileInputStream("C:\\Users\\lh\\Downloads\\Student Details(2009-
    13).xls");
    POIFSFileSystem fs = new POIFSFileSystem(input);
    Workbook workbook;
    workbook = WorkbookFactory.create(fs);
    Sheet sheet = workbook.getSheetAt(0);
    Row row;
    for (int i = 1; i <= sheet.getLastRowNum(); i++) {
        row = (Row) sheet.getRow(i);
        String name = row.getCell(0).getStringCellValue();
        String email = row.getCell(1).getStringCellValue();
        String sql = "INSERT INTO user
        (firstName,email) VALUES('" + name + 
      "','"+email+"')";
        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 (IOException e) {
    }
    }
    }

My Excel Sheet

    Name  email
    test  [email protected]
    john  ----
    mike  [email protected]

Here for 2nd row there is no email thats y i am getting Null Pointer exception.Can anyone tell how to insert null value if value is not present for particular column.?

Upvotes: 0

Views: 995

Answers (1)

Satz
Satz

Reputation: 319

There may be few possibilities for this.

  1. You may have NOT NULL constraints in your DB column or
  2. You are trying to access null object on this line String email = row.getCell(1).getStringCellValue();

Small Work Around: Do a null check before accessing this value like

if(null != row.getCell(1)) {
   row.getCell(1).getStringCellValue();
}

Upvotes: 1

Related Questions