infomax导入到oracle

首先 把infomax的导出来 导成unl或者txt怎么都行  设置好自己的分隔符  我这里设置的是以$换行 以^分割列

然后我这里用的是sqlldr的方式。 生成ctl然后执行

如果你的表很少  可以参照这个例子：

 

 Load data

  infile 'C:\Users\UcJmh\Desktop\syj_data\food\TABLE_NAME.unl'  "str X'24'"--表示$换行

into table TABLE_NAME       

fields terminated by(COL1,COL2);

把上面的放到一个a.ctl里

然后cmd里 sqlldr uesrid=username/password 

control=a.ctl

 

这样就加载进去了

 

 

如果表多的话

可以参照我写的一个配合的Java程序

getCon.java

package DAO;
import java.sql.*;
public class getCon {
 public static Connection Getcon()
 {
  Connection con=null;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   String url="jdbc:oracle:thin:@localhost:1521:orcl";
   con=DriverManager.getConnection(url,"food","food");
  } catch (Exception e) {
   // TODO: handle exception
   System.out.println(e.getMessage());
  }
  return con;
 }
 public static void freeStotck(Connection con,Statement stmt,ResultSet rs)
 {
  try {
   if(rs!=null)
   {
    rs.close();
   }
   if(stmt!=null)
   {
    stmt.close();
   }
   if(con!=null)
   {
    con.close();
   }
  } catch (Exception e) {
   // TODO: handle exception
   System.out.println(e.getMessage());
  }
 }
 
}

 

 

 

test1.java

 

 

package DAO;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class test1 {
 
 public static void main(String[] args) {
  String aaa = "";
  String sql="select * from user_tables";
  String colsql="";
  Connection con=null;
  Statement stmt=null;
  ResultSet rs = null;
  PreparedStatement ps = null;
  String str1="";
  Statement stmtcol=null;
  ResultSet rscol = null;
  PreparedStatement pscol = null;

  try {
   con=getCon.Getcon();
   stmt=con.createStatement();
   ps = con.prepareStatement(sql);
   rs = ps.executeQuery();//rs.getString(1)
      while(rs.next()) {
   //rs.next();
       colsql="select table_name,case when data_type = 'DATE' THEN column_name||' \"to_date(substr(:'||column_name||',0,19),''''''yyyy-mm-dd hh24:mi:ss'''''')\" ' WHEN data_type = 'VARCHAR2' AND DATA_LENGTH >255 THEN column_name||' CHAR(40000) ' WHEN data_type = 'CLOB' THEN column_name||' CHAR(40000) ' ELSE column_name END AS column_name from  all_tab_columns where OWNER = 'FOOD' AND TABLE_NAME = '"+rs.getString(1)+"' order by table_name,COLUMN_ID";
       stmtcol=con.createStatement();
     pscol = con.prepareStatement(colsql);
     rscol = pscol.executeQuery();
     colsql="";
     
     while(rscol.next()) {
      //System.out.println(rscol.getString(2));
      str1+=rscol.getString(2)+",";
     }
     str1=str1.substring(0,str1.length()-1);
     //System.out.println(str1);
      aaa = "d:\\ctl\\"+rs.getString(1)+".ctl";
       File file = new File(aaa);
       try {
        file.createNewFile();
       } catch (IOException e) {
        e.printStackTrace();
       }

       try {
        FileWriter fw = new FileWriter(file, true);
        BufferedWriter bw = new BufferedWriter(fw);
        bw.write("Load data \r\n");
        bw.write("infile 'C:\\Users\\UcJmh\\Desktop\\syj_data\\food\\"+rs.getString(1).toLowerCase()+".unl' \"str X'24'\"  \r\n");
        bw.write("into table "+rs.getString(1)+" \r\n");
        bw.write("fields terminated by '^'  \r\n");
        bw.write("(");
        bw.write(str1);
        bw.write(")  \r\n");
        bw.flush();
        bw.close();
        fw.close();
         str1="";
        //System.out.println(rs.getString(1)+"       "+str1);
       } catch (IOException e) {
        e.printStackTrace();
       }
     }

         aaa="";
     
  } catch (Exception e) {
   // TODO: handle exception
   System.out.println(e.getMessage());
  }
 }
}

 

 

 

test2.java

 

package DAO;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class test2 {
 
 public static void main(String[] args) {
  String sql="select * from user_tables";
  Connection con=null;
  Statement stmt=null;
  ResultSet rs = null;
  PreparedStatement ps = null;
  try {
   con=getCon.Getcon();
   stmt=con.createStatement();
   ps = con.prepareStatement(sql);
   rs = ps.executeQuery();//rs.getString(1)
      while(rs.next()) {
        System.out.println("sqlldr userid=food/food rows=1000000 bindsize=1024000000  readsize=1024000000 control="+rs.getString(1)+".ctl ");
      } // mm/dd/yyyy
  }catch (Exception e) {
   // TODO: handle exception
  }
   }
}

 

 

 

infomax的lvarchar写成varchar2(4000)或者clob 然后导的时候 在列名后面跟CHAR(40000)   就可以导进去了。  具体sqlldr参数的意思请百度。