我正在尝试导入使用Bteq脚本导出的数据,但无法加载到另一个表中。
.EXPORT report file=E:\t\tgt\report_excel_092702.csv;
.set width 200;
.SET TITLEDASHES OFF;
.SET SEPARATOR ',';
select empno,coalesce(ename,'no_name') as ename,coalesce(job,'no_job') as job ,coalesce(hiredate, date) as hiredate,COALESCE(mgr,0) as mgr,COALESCE(sal, 0) as sal,COALESCE(comm, 0) as comm,deptno from emps;
--.EXPORT reset
.LOGOFF
.EXIT
数据库万亿数据1
报告_Excel_092702.csv(导出的.csv文件)
EMPNO ename job hiredate mgr sal comm DEPTNO
7698 BLAKE MANAGER 81/11/17 7839 2850 0 30
7839 KING PRESIDENT 81/11/17 0 50000 0 10
7654 MARTIN SALESMAN 81/09/28 7698 1250 1400 30
Bteq脚本....
.import vartext ',' file='E:\t\tgt\report_excel_092702.csv',skip=1;
DATABASE tetra;
delete from emp_csv;
.QUIET ON
.REPEAT *
USING
empno (varchar(100))
,ename (varchar(100))
,hiredate (varchar(100))
INSERT INTO emp_csv(empno,ename,hiredate)
VALUES (
:empno,
:ename,
Cast((:hiredate (Date,Format 'dd.mm.yyyy')) As Date)
--cast(hiredate 'yy/mm/dd') as :hiredate
--CAST( CAST( :hiredate AS DATE FORMAT 'mm/dd/yyyy') AS DATE FORMAT 'mm/dd/yyyy')
);
.QUIT
.LOGOFF
1条答案
按热度按时间5cg8jx4n1#
我想知道为什么你不直接用大小写,因为yy/mm/dd隐式地转换成BET或ANSI模式下的DATE?它对我来说和你一样的日期格式也是有效的。
.
LOGON dbc, dbc; .import vartext '|' FILE = /root/ETL/new_revision/report.csv, skip=1; .REPEAT * USING ( EmpNo VARCHAR(2000) ,Name VARCHAR(2000) ,hire_date VARCHAR(2000) ) INSERT INTO retail.employee_import VALUES ( :EmpNo, :Name, CAST(:hire_date AS DATE), ); .QUIT .LOGOFF