找到你要的答案

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

Q:加载一个日期和时间的CSV文件导入Oracle数据库使用java

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

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();
        }
    }

}

我得到一个错误如下

难道是ora-01843:不是一个有效的月

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

answer1: 回答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 /

取代(DD /月/年)

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

由(DD毫米RR)

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

周一代表三个字母缩写,采购经理是01到12。检查此概述更多。

注:根据实例,你应该使用RR代替YYYY和代替/

answer2: 回答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.

我不能尝试这自己的现在,但我想你的格式字符串的DD /月/年hh24:MI:学生不匹配你的日期字符串的15-02-15 17:54:45”。

尝试使用“DD一年hh24:MI:SS”代替。

answer3: 回答3:

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'))";

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'))";

java  oracle  csv  exception