Microsoft Excel as a Source and Target as Oracle in ODI
来源:互联网 发布:社交网络数据可视化 编辑:程序博客网 时间:2024/05/16 18:25
创建测试表格和目标表
导出scott用户的emp表为EMP.xlsx作为测试的元数据,结果如下
打开EMP.xlsx 公式→定义名称
创建目标表(来自scott.sql)
CREATE TABLE EMP( EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,SAL NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER(2));
定义数据源
控制面板→管理工具→数据源(ODBC)
双击数据源(ODBC)
最终结果如下
配置ODI Studio拓扑结构
上一步我们新建了数据源,现在我们使用ODI Studio创建拓扑结构
新建数据服务器:
配置如下,并测试
新建物理方案:
新建逻辑方案:
新建模型,并逆向工程
报错
原因:C:\WINDOWS\SysWOW64\odbcad32.exe不适odi64.exe
解决办法1:使用C:\Oracle\Middleware\Oracle_Home\odi\studio\ODI.exe
解决办法2:使用C:\Oracle\Middleware\Oracle_Home\odi\studio\ODI.exe
下载64 bit excel/access driver.并安装 下载地址 link.
(http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255 )
模型创建成功后,我们就可以看到EMP中的数据了
对目标表也进行逆向工程
新建接口
新建接口Excel_To_Oracle
运行
Caused By: java.sql.SQLException: Invalid Fetch Size
this is a bug in ODI 11.1.1.5. Oracle has provided a patch to resolve this ( patch number 13528165 ).
解决办法:去拓扑结构,修改数据excel数据服务器EMP的数组提取大小和批处理更新大小为0. 以前的默认值为30
重新运行,可以发现表中已经有数据了。
说明:
时间类型的HIREDATE 有问题,做实验的时候,可以去掉该列
参考
http://dwteam.in/microsoft-excel-as-a-source-and-a-target-as-oracle-in-odi-11-1-1-6/
- Microsoft Excel as a Source and Target as Oracle in ODI
- How To Generate An XML File As A Target Datastore Using ODI In An Integration Interface ? [ID 454268
- Finding all mappings that use a table as source, target and lookups
- Memory as a Programming Concept in C and C++
- Save a Microsoft Excel Workbook as a PDF File by Using PowerShell
- CareerCup Convert a BST to max heap without using extra memory and in as optimum time as possible
- as a
- Importing a Microsoft Excel Source Definition
- Importing a Microsoft Excel Source Definition
- Oracle: Select constant as a column
- Oracle WITH A AS 用法解析
- As easy as A+B
- As Easy As A+B
- As Easy As A+B
- As Easy As A+B
- As Easy As A+B
- As Easy As A+B
- As Easy As A+B
- linked list easy
- Redis详解(原理,安装,配置,使用,命令)
- Thinkpad E550的配置
- 通过HttpClient 调用ASP.NET Web API
- Android内存优化篇
- Microsoft Excel as a Source and Target as Oracle in ODI
- 微信小程序之登录页实例 —— 微信小程序实战系列(5)
- C++教程网miniftpd项目实战
- MongoDB Java基本操作
- Java枚举
- SCD Type2 in ODI
- linked list summary
- Effictive C++导读
- 动态规划法(一)