txt文件与csv文件导入oracle

来源：互联网发布：淘宝秒杀店铺编辑：程序博客网时间：2024/05/16 12:42

现有文件testSet.txt，需要导入oracle数据库。部分内容如下：

"age";"job";"marital";"education";"default";"balance";"housing";"loan";"contact";"day";"month";"duration";"campaign";"pdays";"previous";"poutcome";"y"
58;"management";"married";"tertiary";"no";2143;"yes";"no";"unknown";5;"may";261;1;-1;0;"unknown";"no"
44;"technician";"single";"secondary";"no";29;"yes";"no";"unknown";5;"may";151;1;-1;0;"unknown";"no"
33;"entrepreneur";"married";"secondary";"no";2;"yes";"yes";"unknown";5;"may";76;1;-1;0;"unknown";"no"
47;"blue-collar";"married";"unknown";"no";1506;"yes";"no";"unknown";5;"may";92;1;-1;0;"unknown";"no"
33;"unknown";"single";"unknown";"no";1;"no";"no";"unknown";5;"may";198;1;-1;0;"unknown";"no"
35;"management";"married";"tertiary";"no";231;"yes";"no";"unknown";5;"may";139;1;-1;0;"unknown";"no"
28;"management";"single";"tertiary";"no";447;"yes";"yes";"unknown";5;"may";217;1;-1;0;"unknown";"no"
42;"entrepreneur";"divorced";"tertiary";"yes";2;"yes";"no";"unknown";5;"may";380;1;-1;0;"unknown";"no"
58;"retired";"married";"primary";"no";121;"yes";"no";"unknown";5;"may";50;1;-1;0;"unknown";"no"
43;"technician";"single";"secondary";"no";593;"yes";"no";"unknown";5;"may";55;1;-1;0;"unknown";"no"

PS:其中第一行为列名，第二行以下为要导入数据

1.首先需要在oracle中建立对应表，建表语句如下

create table TEST_SET(
AGE varchar2(128),
JOB varchar2(128),
MARITAL varchar2(128),
EDUCATION varchar2(128),
B_DEFAULT varchar2(128),
BALANCE varchar2(128),
HOUSING varchar2(128),
LOAN varchar2(128),
CONTACT varchar2(128),
DAY varchar2(128),
MONTH varchar2(128),
DURATION varchar2(128),
CAMPAIGN varchar2(128),
PDAYS varchar2(128),
PREVIOUS varchar2(128),
POUTCOME varchar2(128),
Y varchar2(128)
);

查看:select * from test_set

已经建立好了，这是表里没有任何数据。下面就需要把testSet.txt中数据导入到test_set表中

2.编写控制文件testCrl.crl,如下编写：

LOAD DATA
CHARACTERSET UTF8
truncate
INTO TABLE test_set
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS(
AGE ,
JOB OPTIONALLY ENCLOSED BY '"',
MARITAL OPTIONALLY ENCLOSED BY '"',
EDUCATION OPTIONALLY ENCLOSED BY '"',
B_DEFAULT OPTIONALLY ENCLOSED BY '"',
BALANCE ,
HOUSING OPTIONALLY ENCLOSED BY '"',
LOAN OPTIONALLY ENCLOSED BY '"',
CONTACT OPTIONALLY ENCLOSED BY '"',
DAY ,
MONTH OPTIONALLY ENCLOSED BY '"',
DURATION ,
CAMPAIGN ,
PDAYS ,
PREVIOUS ,
POUTCOME OPTIONALLY ENCLOSED BY '"',
Y OPTIONALLY ENCLOSED BY '"'
)

PS:由于数据由" " 括起来了 OPTIONALLY ENCLOSED BY '"' 方法去掉，各个列分隔符为; 故FIELDS TERMINATED BY ';'

PPS:详细编写方法请自行搜索控制文件写法

3.打开 cmd 执行 sqlldr命令。命令如下：

sqlldr uid/password data=F:\文本文件路径\testSet.txt control=F:\控制文件路径\testCrl.crl

其中uid 为数据库用户名，password为uid密码，data则为需要导入的数据文件路径，control为控制文件路径

4.再执行select 查看是否成功

csv文件也是一样的

0 0