Oracle自动导入多个excel,将Excel数据批量导入oracle数据库方法

有两种思路:

一.采用JExcelAPI或apache POI来读取Excel中的每行记录,通过程序插入以数据库中;  (

可参考

二.将Excel转化成.csv格式,然后利用Oracle中的sqlldr命令成批导入,具体步骤如下:

1.采用XL2ExternalTables,apache POI 将Excel数据转化为csv格式

测试方法之一:

注意:要将ExternalTableGenerator类中的方法改为public就行了

import java.io.FileInputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class Test {

public static void getCSV(String spreadsheet) {

String sheet = spreadsheet.substring(spreadsheet.lastIndexOf(“/”));

POIFSFileSystem fs;

HSSFWorkbook wb;

try {

fs = new POIFSFileSystem(new FileInputStream(spreadsheet));

//绝对路径

wb = new HSSFWorkbook(fs);

ExternalTableGenerator generator generator = new ExternalTableGenerator(sheet);

//文件名

generator.processWorkook(wb);

//要将ExternalTableGenerator类中的方法改为public就行了

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public static void main(String args[]) {

getCSV(“C:/Documents and Settings/XXXX/workspace/Test/test.xls”);

}

}

测试方法之二:

java -cp XL2ExternalTables.jar;jakarta-poi-1.5.1-final-20020615.jar;jakarta-poi-contrib-1.5.1-final-20020615.jar com.saternos.database.utilities.ExternalTableGenerator new_department_data.xls

2.再利用oracle的sqlldr命令导入数据效率高。

test.ctl

load data

infile ‘TEST.csv’

into table TEST

REPLACE

fields terminated by ‘,’  OPTIONALLY ENCLOSED BY ‘”‘

trailing nullcols

(

TEST_ID   “SE_AIRCRAFT.nextval” ,

TEST      “upper(:TEST)”,

TEST_USER  “to_number(:TEST_user)”,

TEST_DATE  DATE “yyyy-mm-dd hh24:mi:ss”,

TEST_NOTE

)

sqlldr admin/password@DATABASE control=ctl/TEST.ctl log=out/TEST.log data=data/TEST.csv  bad=out/TEST.bad skip=1

    原文作者:weixin_39522408
    原文地址: https://blog.csdn.net/weixin_39522408/article/details/116349542
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞