java load data infile query discarding wrong lines

Java application insert data to mysql using load data infile query. In csv file, some wrong formatted values like lacking rows, improper fields (string value for integer value). If there is non-correct record(line) on file, java application throws exception and do not add any records even correct lines. If execute same query for same file using heidi sql program (sql client program), add all records, even non-correct lines (put null value for lacking开发者_Python百科 fields etc). I just want to my java application behave like sql client, put all lines at least insert correctly well-formed lines.

Thanks.

sql

load data infile '/a.txt'  into table test_data fields terminated by ','

code snippet

public void query(String query, Connection conn) throws SQLException {

        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(query);
            ps.executeUpdate();
        } catch (MySQLTransactionRollbackException e) {
            logger.error("", e);
            throw new MySQLTransactionRollbackException();
        } catch (SQLException e) {
            logger.error("", e);
            throw new SQLException(e);
        } finally {

            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception e) {
                    logger.error("", e);
                }
            }

            try {
                conn.close();
            } catch (SQLException e) {
                logger.error("", e);
            }

        }
    }

It cannot discard wrong lines as they might causing parsing failure of the entire file.With very good algorithms it can be done, but i dont think there will be 100% accuracy.

ignore keyword is solution.

load data infile '/a.txt'  **ignore** into table test_data fields terminated by ','

ignore is correct, but supposing you need to know the details of the dropped records..

A long and tedious solution is to dump a file after the load and compare the PK values with an extract from your infile using diff say.

This will not distinguish key violations from referential integrity violations though.