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.

What happens after 10950 records? Is there an Exception? If yes, please post it (with stack trace) — Thomas Stets, Jun 16 '15 at 09:20
Side note: you should use batch record processing. See http://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/ for example. — Radek Postołowicz, Jun 16 '15 at 09:49
Are you familiar with PostgreSQL's `COPY` command? There's support for it in PgJDBC. — Craig Ringer, Jun 16 '15 at 23:35

score 0 · Answer 1 · answered Jun 17 '15 at 06:47

0

It has no error. It just stop like that

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

answered Jun 17 '15 at 06:47

score 0 · Answer 2 · answered Jun 23 '15 at 20:52

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.

Limitation of OpenCSV Reader-Java

2 Answers2