Limitation of OpenCSV Reader-Java

Question

I used OpenCSV Reader - java to load my CSV File (file size:-1.47 GB (1,585,965,952 bytes)).

However, inside my coding, whenever it only manage to insert 10950 record to PostgreSQL database.

  CSVReader csvReader = new CSVReader(new FileReader(csvFilename));
    String[] row = null;

    String sqlInsertCSV = "insert into ip2location_tmp_test
    (ip_from, ip_to, xxxxx, "
    + "xxxxx, xxxxx,xxxxx, "
    + "xxxxx,xxxxx, xxxxx, xxxxx, xxxxx, xxxxx,xxxxx,xxxxx)"
    + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; 

    while((row = csvReader.readNext()) != null) {

    PreparedStatement insertCSV = conn.prepareStatement(sqlInsertCSV);
    insertCSV.setLong(1, Long.parseLong(row[0]));
    ....
    ....
    insertCSV.setString(14, row[13]); // usage_type     
    insertCSV.executeUpdate();  
}
    csvReader.close();
    PreparedStatement insertCSV = conn.prepareStatement(sqlInsertCSV); 
    insertCSV.executeUpdate(); 

   } 

Is there any limitation of OpenCSV?

I need to use setString function to cater for single quote in PostgreSQL.

Answer 1

It has no error. It just stop like that

Hi Craig, COPY command need to be super user. Has tried it before

Answer 2

First question: you are telling us how many bytes are in the file but how many records (lines) are in the file? If the file has 10950 lines then everything is working great .

Second question: Why do you have the prepareStatement/executeUpdate outside the while loop? It seems to me that would try to either insert the last record twice or insert a empty record because once you are outside the while you have no data because the csvReader returned null.

Using the record at a time method you are using there is no limit to the number of records you can read. Check your data file to see if there is no accidental carriage returns in the file around line 10950.