Load a csv file of date and time into oracle database using java

Question

import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

import au.com.bytecode.opencsv.CSVReader;

public class ImportingDate {

    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        try{
        /* Create Connection objects */
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection conn= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/xe","SYSTEM","sandp");
        /* Create the insert statement */
        String insertQuery = "Insert into date_tab(workdate) values(to_date(?,'dd/mon/yyyy hh24:mi:ss'))";
        PreparedStatement pstmt = conn.prepareStatement(insertQuery);
        CSVReader reader = new CSVReader(new FileReader("D:\\datedata.csv"), ',');
        String[] nextLine;
        int i = 0;
        while((nextLine = reader.readNext()) != null)
        {
            i++;
            if (nextLine.length == 1){
                pstmt.setString(1,nextLine[0]);
                i=pstmt.executeUpdate();
            }
        }
        System.out.println("Data Successfully Uploaded");
            pstmt.close();
            conn.commit();
            conn.close();
        }

        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

}

i am getting an error as follows=

java.sql.SQLException: ORA-01843: not a valid month

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1169)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
at a.ba.ImportingDate.main(ImportingDate.java:32)

i m using eclipse and oracle 10g how can i fix the problem and insert into table into timestamp datatype. i have created a table named date_tab(workdate timestamp)

CSV file contains as follows= 15-02-15 17:54:45

18-02-15 18:19:33

20-06-15 18:38:56

23-09-15 19:00:18

22-02-15 19:21:08

26-07-15 19:40:04

21-05-15 20:03:07

25-01-15 20:25:59

28-02-15 20:48:12

27-04-15 00:05:11

The format in your to_date statement is not the same as the format of the dates in your CSV file. — VGR
– VGR, Commented May 4, 2015 at 13:01

André R. · Accepted Answer · 2015-05-04 13:49:20Z

2

i can't try this on my own right now, but i suppose your format-string 'dd/mon/yyyy hh24:mi:ss' is not matching your Date-Strings '15-02-15 17:54:45'.

try to use 'dd-mon-yyyy hh24:mi:ss' instead.

edited May 4, 2015 at 13:49

answered May 4, 2015 at 13:05

André R.

1,66710 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Trinimon Over a year ago

yyyy won't work due to two-digits year 15 - use rr instead

André R. Over a year ago

@VGR you're right .. overlooked the two digit year !

Trinimon · Accepted Answer · 2015-05-04 13:07:35Z

1

Replace (dd/mon/yyyy)

String insertQuery = 
  "Insert into date_tab(workdate) values(to_date(?,'dd/mon/yyyy hh24:mi:ss'))";

by (dd-mm-rr)

String insertQuery = 
  "Insert into date_tab(workdate) values(to_date(?,'dd-mm-rr hh24:mi:ss'))";

mon represents the three letter abbreviation and mm is 01 to 12. Check this overview for more.

p.s.: as per examples you should use rr instead of yyyy and - instead of /

answered May 4, 2015 at 13:07

Trinimon

14k9 gold badges46 silver badges61 bronze badges

Comments

ARoy · Accepted Answer · 2015-05-04 13:10:00Z

0

update the following code and it will run successfully String insertQuery = "Insert into date_tab(workdate) values(to_date(?,'dd/mm/yy hh24:mi:ss'))";

answered May 4, 2015 at 13:10

ARoy

1813 silver badges23 bronze badges

Collectives™ on Stack Overflow

Load a csv file of date and time into oracle database using java

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related