Removing Comma from .csv column field and import to db

Question

The below class will import the .csv into database table.it is working fine. But when, it come across the numeric values like this 2,345. It result in error.

In my .csv files there are 3 columns like this:

Datatypes for these columns in db2 table "COMPUTER" is COL_A(VArchar 50), COL_B(Double), COL_C(Varchar 50)

COL_A | COL_B | COL_C

---

KKGG56 | 7,567 | JUNE2013

GGHHK2 | 259,024 | MAY 2012

So, how to remove the these comma from the particular column while importing into the db table and where to place the code in my program? Please help.

public class CSVLoader {

private static final 
    String SQL_INSERT = "INSERT INTO OPM.${table}
         (${keys})      VALUES(${values})";

private static final String TABLE_REGEX = "\\$\\{table\\}";

private static final String KEYS_REGEX = "\\$\\{keys\\}";

private static final String VALUES_REGEX = "\\$\\{values\\}";

private Connection connection;

private char seprator;

public CSVLoader(Connection connection) {

    this.connection = connection;

    //Set default separator

    this.seprator = ',';
}

      public void loadCSV(String csvFile, String tableName) throws Exception {

    CSVReader csvReader = null;

    if(null == this.connection) {

        throw new Exception("Not a valid connection.");
    }

    try {

        csvReader = new CSVReader(new FileReader(csvFile), this.seprator);

    } catch (Exception e) {

        e.printStackTrace();

        throw new Exception("Error occured while executing file. "

                   + e.getMessage());

              }

        String[] headerRow = csvReader.readNext();

    if (null == headerRow) {

        throw new FileNotFoundException(


                        "No columns defined in given CSV file." +

                         "Please check the CSV file format.");
    }

    String questionmarks = StringUtils.repeat("?,", headerRow.length);

    questionmarks = (String) questionmarks.subSequence(0, questionmarks

            .length() - 1);


    String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);

    query = query
            .replaceFirst(KEYS_REGEX, StringUtils.join

             (headerRow,   ","));

    query = query.replaceFirst(VALUES_REGEX, questionmarks);

            System.out.println("Query: " + query);

    String[] nextLine;

    Connection con = null;

    PreparedStatement ps = null;

    try {
        con = this.connection;

        con.setAutoCommit(false);

        ps = con.prepareStatement(query);

                       final int batchSize = 1000;

                     int count = 0;

        Date date = null;

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

            System.out.println( "inside while" );

            if (null != nextLine) {

                int index = 1;

                for (String string : nextLine) {

                    date = DateUtil.convertToDate(string);

        if (null != date) {

                    ps.setDate(index++, new java.sql.Date(date

                    .getTime()));

                     } else {

                  ps.setString(index++, string);

    System.out.println( "string" +string);

                    }

                }

                ps.addBatch();

            }

            if (++count % batchSize == 0) {

                ps.executeBatch();

            }

                     }


        ps.executeBatch(); // insert remaining records

        con.commit();

    } catch (Exception e) {

        con.rollback();

        e.printStackTrace();

        throw new Exception(

        "Error occured while loading data 

                from file                to                      database."

               + e.getMessage());

    } finally {

             if (null != ps)


            ps.close();

        if (null != con)

            con.close();

            System.out.println("csvReader will be closed");

        csvReader.close();

    }

}

public char getSeprator() {

    return seprator;

}

public void setSeprator(char seprator) {

    this.seprator = seprator;

}


         }

Answer 1

To answer your question:
You have to parse your CSV text using Double.parseDouble("2,345".replaceAll(",","")) but you have to call ps.setDouble() to store a double in database, not ps.setString()!

for (String string : nextLine) {
  date = DateUtil.convertToDate(string);

  if (null != date) {
    ps.setDate(index++, new java.sql.Date(date.getTime()));
  } 
  else {
     try {
       final double doubleValue = Double.parseDouble(string.replaceAll(",",""));

       ps.setDouble(index++, doubleValue);
     }
     catch(NumberFormatException e) {
       // For invalid double
       ps.setString(index++, string);
     }
  }

This code is not so robust, if you have a date or a number in 3rd column you will go into trouble! look at https://stackoverflow.com/questions/18067934/parsing-csv-file-with-java/18068238#18068238 to use advance mapping solution you you know data structure in advance.

Answer 2

Assuming there is only one column with comma in it, extract the first N column values from the string(going in a forward direction). Then extract the last value(going in the reverse direction). Whatever remains is the middle column.