,">TEST.NEW"); while(<FILE_OLD>) { chomp; if ($mycount%2 == 0) {print FILE_NEW $_." ";} else {print FILE_NEW $_."\n";} $mycount++; } 5、在命令窗口下执行 perl test.pl 6、得到一个新的文本文件:test.new,内容如下:
1 2 3 4 5 6 7 a b c d e f g
--------------- load data infile 'test.txt' concatenate(2) into table aa fields terminated by whitespace (FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6,FIELD7) ==============================================================
8 ***** 载入每行的行号
load data infile * into table t replace ( seqno RECNUM //载入每行的行号 text Position(1:1024)) BEGINDATA fsdfasj //自动分配一行号给载入 表t 的seqno字段 此行为 1 fasdjfasdfl // 此行为 2 ...
9 ***** 载入有换行符的数据 注意: unix 和 windows 不同 \\n & /n 还可以用 dbms_lob 和 bfile 看一个文件的回车 换行 等其他特殊字符
< 1 > 使用一个非换行符的字符 LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED "my_to_date( :last_updated )", COMMENTS "replace(:comments,'\n',chr(10))" // replace 的使用帮助转换换行符 ) BEGINDATA 10,Sales,Virginia,01-april-2001,This is the Sales\nOffice in Virginia 20,Accounting,Virginia,13/04/2001,This is the Accounting\nOffice in Virginia 30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting\nOffice in Virginia 40,Finance,Virginia,987268297,This is the Finance\nOffice in Virginia
< 2 > 使用fix属性 LOAD DATA INFILE demo17.dat "fix 101" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED "my_to_date( :last_updated )", COMMENTS ) demo17.dat 10,Sales,Virginia,01-april-2001,This is the Sales Office in Virginia 20,Accounting,Virginia,13/04/2001,This is the Accounting Office in Virginia 30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting Office in Virginia 40,Finance,Virginia,987268297,This is the Finance Office in Virginia // 这样装载会把换行符装入数据库 下面的方法就不会 但要求数据的格式不同
LOAD DATA INFILE demo18.dat "fix 101" INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED "my_to_date( :last_updated )", COMMENTS ) demo18.dat 10,Sales,Virginia,01-april-2001,"This is the Sales Office in Virginia" 20,Accounting,Virginia,13/04/2001,"This is the Accounting Office in Virginia" 30,Consulting,Virginia,14/04/2001 12:02:02,"This is the Consulting Office in Virginia" 40,Finance,Virginia,987268297,"This is the Finance Office in Virginia"
< 3 > 使用var属性 LOAD DATA INFILE demo19.dat "var 3" // 3 告诉每个记录的前3个字节表示记录的长度 如第一个记录的 071 表示此记录有 71 个字节 INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED "my_to_date( :last_updated )", COMMENTS ) demo19.dat 07110,Sales,Virginia,01-april-2001,This is the Sales Office in Virginia 07820,Accounting,Virginia,13/04/2001,This is the Accounting Office in Virginia 08730,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting Office in Virginia 07140,Finance,Virginia,987268297,This is the Finance Office in Virginia
< 4 > 使用str属性 // 最灵活的一中 可定义一个新的行结尾符 win 回车换行 : chr(13)||chr(10)
此列中记录是以 a|\r\n 结束的 select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual; 上一页 [1] [2] [3] [4] [5] 下一页 [ORACLE]oracle 使用杂记2
|