现在准备用sqlldr命令导入一个csv文件,表的结构如下:
CREATE TABLE tab1(
FA VARCHAR2(10) NOT NULL,
FB VARCHAR2(50) NOT NULL,
FC VARCHAR2(100)
);
现有一个csv文件,格式为:
--------------------------
A |B |C |D
-----------------------------
A11 |B22 |C333 |D4444
A22 |B33 |C444 |D5555csv文件的列与表的列对应关系如下:
A:FA,B:FB,D:FC
也就是说csv中的C列是跳过的
现在想通过sqlldr导入这个csv文件,并在其控制文件中指定对应列的关系为:A:FA,B:FB,D:FC
当然,你一定会说,何必这么麻烦,把csv文件中的C列去掉不就成了,这当然可以解决问题
但如果是那样,我就不用来这里发贴了,我就是想研究其能不能设定列对应关系?期待高手解答!!
命令我都知道,就是不知道这个控制文件里的对应关系怎么写。
CREATE TABLE tab1(
FA VARCHAR2(10) NOT NULL,
FB VARCHAR2(50) NOT NULL,
FC VARCHAR2(100)
);
现有一个csv文件,格式为:
--------------------------
A |B |C |D
-----------------------------
A11 |B22 |C333 |D4444
A22 |B33 |C444 |D5555csv文件的列与表的列对应关系如下:
A:FA,B:FB,D:FC
也就是说csv中的C列是跳过的
现在想通过sqlldr导入这个csv文件,并在其控制文件中指定对应列的关系为:A:FA,B:FB,D:FC
当然,你一定会说,何必这么麻烦,把csv文件中的C列去掉不就成了,这当然可以解决问题
但如果是那样,我就不用来这里发贴了,我就是想研究其能不能设定列对应关系?期待高手解答!!
命令我都知道,就是不知道这个控制文件里的对应关系怎么写。
spool d:\a.csv
select a fa,b fb,d fc from tab1
spool off
--这样在d盘就要个a.csv文件
我是要把csv里指定列导到tab1里去,不是一回事
load data
infile d:\test.csv
append
into table tab1
fields terminated by ","
(
fa,
fb,
mycol filler,
fc
)
注:csv分隔符应该为逗号,如果为“|”,请将fields terminated by ","中逗号改为"|"
2、导入文件
c:\>sqlldr username/password@conn_str control=d:\test.ctl direct=true
注:direct=true采用直接路径加载,对于大数据量能极大的提高加载速度,很重要的一个参数。
更详细
test.ctlload data
infile 'csv文件位置'
append into table tab1
fields terminated by "," --csv的文件应该是逗号分割符
(fa,
fb,
tcol filler,
fc)dossqlldr user/pass control=完整路径\test.ctl log=test.log ---skip=n指定前几行不导入
怎么选择对应的数据导入呢??
Specifying Filler Fields
A filler field, specified by FILLER, is a datafile mapped field that does not correspond to a database column. Filler fields are assigned values from the data fields to which they are mapped.Keep the following in mind with regard to filler fields:The syntax for a filler field is same as that for a column-based field, except that a filler field's name is followed by FILLER.Filler fields have names but they are not loaded into the table.Filler fields can be used as arguments to init_specs (for example, NULLIF and DEFAULTIF).Filler fields can be used as arguments to directives (for example, SID, OID, REF, and BFILE).To avoid ambiguity, if a Filler field is referenced in a directive, such as BFILE, and that field is declared in the control file inside of a column object, then the field name must be qualified with the name of the column object. This is illustrated in the following example:LOAD DATA
INFILE *
INTO TABLE BFILE1O_TBL REPLACE
FIELDS TERMINATED BY ','
(
emp_number char,
emp_info_b column object
(
bfile_name FILLER char(12),
emp_b BFILE(constant "SQLOP_DIR", emp_info_b.bfile_name) NULLIF
emp_info_b.bfile_name = 'NULL'
)
)
BEGINDATA
00001,bfile1.dat,
00002,bfile2.dat,
00003,bfile3.dat,