|
登录注册 |
☦ 上海交通大学论坛 > 数据库 > 浏览当前帖子 | 手机版 关闭左侧栏 |
EXCEL与ORACLE间的数据互传法 |
【返回本版】 【发表帖子】 【回复帖子】 | 浏览量 5623 回帖数 0 |
风的翅膀 等级 ☆ 楼主 发表于 2012/5/6 18:07:16 编 辑 |
||
发信人waterbaby (水娃), 信区database 标 题EXCEL与ORACLE间的数据互传法 发信站饮水思源站 (Mon May 18 16:05:42 1998) , 站内信件 众所周知,MicrosoftExcel能很直观而方便地进行数 据输入,统计,生成图表,但它的数据管理能力有限,对 大量的数据查询能力不足,如果利用它数据计算方面的优 点和大型数据库ORACLE的数据查询优点,可以设计出功能 强大的数据处理系统。 设与Excel数据文件相对应的职工情况表EMP已在ORAC LE系统中创建,此表中各字段按照顺序分别是职工代号( Emp—No),职工姓名(Ename),工资(Salary),雇用 时间(Hiredate)。本文将通过此例来介绍Excel与ORACL E数据交换的具体步骤。 1、将Excel中的数据装入ORACLE数据库 通过ORACLE数据库系统的高级应用工具SQL*Loader 可以将原来的Excel中存储的数据直接装入ORACLE数据库的 表中。 首先,在MicrosoftExcel中完成表格数据输入后,选 择“保存文件”命令,在出现对话框的“文件类型”栏中 选取“格式化文本文件(空格分隔)”以形成一个标准格 式化文本文件(*.PRN)或是选取“CSV(逗号分隔)” 形成一个逗号定界文件(*.CSV)。这两者的区别是: 标准格式的文本文件中每个记录等长,数据间按原库文件 字段长度紧凑排列,字符型字段数据左对齐,数据型字段 数据右对齐,不足部分用空格补足。逗号定界文件特点是 各记录可不等长,字段按其定义顺序依次排列,字段间用 逗号分隔,字符型字段和数据型字段的左右端空格被消去 。因为这两种格式文件有不同的数据装入方法,假如我们 将职工情况EMP表存为EMP.CSV,数据变成如下形式: 120,JOE,1192.64,93/04/11 121,KERT,309.60,87/01/14 23,PETER,1500.25,84/03/12 124,SMITH,678.00,91/01/07 15,YOUNG,2390.12,80/05/03 接着,通过记事本建立以下控制文件,它包含了数据 文件的名称及其格式,文件字段如何定界,数据类型是怎 样等内容,将该装载控制文件保存为EMP.CTL: LOADDATA INFILE‘C:ιEMP.CSV’ BADFILE‘EMP.BAD’ DISCARDFILE‘EMP.DSC’ APPEND INTOTABLEEMP FILEDSTERMINATEDBY“,” (Emp-No,Ename,Salary,HiredateDATA(8)“Y Y/MM/DD”) 之后,双击SQL*Loader图标,进入ORACLE7的数据 装载工具,在弹出的SQLLOADER主屏幕对话框中,分别输 入用户登录名,密码,数据库名称和控制文件名,确认后 进行数据装载。这样SQL*Loader一边转换、装入数据,一 边将执行过程中的错误信息和统计信息存入登录文件(Lo gFile)中,把ORACLE拒绝装入的原始数据存入拒绝文件 (RejectFile)。用户可以在装载完成后,打开登录文件 文件查看数据的装载情况,了解因为何种原因使得数据被 拒绝,是因为数据本身不符合数据库的数表定义还是违反 了完整性原则等其它原因。由此可见,数据装载不仅快速 而且安全。 特别要提到的是可以通过SQL*Loader同时将数据文 件装入多个数表。例如在Excel表格中输入数据时,可以对 应输入该职工参加的项目代号(Proj-No),现在需要将 这些数据分别装入EMP表和PROJ表。数据文件按标准格式 存储如下所示,其中第5,6,7列均为该职工参加的项目代 号: 120JOE1192.6493/04/11101112 121KERT309.6087/01/142812 23PETER1500.2584/03/124017 124SMITH678.0091/01/07102021 15YOUNG2390.1280/05/03432617 编写的装载数据控制文件可以是: LOADDATA INFILE′C:ιEMP.PRN′ BADFILE′EMP.BAD′ DISCARDFILE′EMP.DSC′ APPEND INTOTABLEEMP (Emp-NoPOSITION(01:05)INTEGEREXTERNAL, EnamePOSITION(06:15)CHAR, SalaryPOSITION(16:25)DECIMALEXTERNAL, HiredatePOSITION(26:34)INTEGEREXTERNAL) INTOTABLEPROJ WHENProj-No!Κ′′ (Emp-NoPOSITION(1:4)INTEGEREXTERNAL, Proj-NoPOSITION(35:38)INTEGEREXTERNAL) INTOTABLEPROJ WHENProj-No!Κ′′ (Emp-NoPOSITION(1:4)INTEGEREXTERNAL, Proj-NoPOSITION(39:42)INTEGEREXTERNAL) INTOTABLEPROJ WHENProj-No!Κ′′ (Emp-NoPOSITION(1:4)INTEGEREXTERNAL, Proj-NoPOSITION(43:46)INTEGEREXTERNAL) 注意,CHAR,INTEGEREXTERNAL和DECIMALEXTERNAL这 些数据类型是指数据文件中的数据类型,而不是数据库中 数表的数据类型。数字字段的EXTERNAL暗示了它不是以二 进制格式存储的数字,而是以人可以识别的ASCII码格式 存储。 这样运行SQL*Loader之后,所需数据被分别装入两 个数表,非常方便。 2、将ORACLE数据文件转化为Excel文件 当需要在Excel下获取ORACLE数据时,可以通过ODBC (假设ORACLE是安装在WindowsNT操作系统下)。ODBC的 英文意思是OpenDatabaseConnectivity(开放式数据库连 接),它是Microsoft提供的一组标准应用程序编程接口 (API)。ODBC建立了一组应用程序直接操作数据库数据的 规范,允许用户的应用程序使用基于SQL语言的不同类型 的数据库管理系统。 在Excel的“数据”菜单中获取“获取外部数据”,M icrosoftQuery被启动。这是一个非常有用的数据查询程 序,它能在网络上以客户机/服务器形式快速查询数据。 在其中的File菜单中选取NewQuery,“SelectDataSource ”窗口出现,提示用户在DataSource列表中选取需获取的 数据所在的数据源。点按“Other”按钮,“ODBCDataSour ce”窗口出现,列出了机器中已被定义的所有ODBC数据源 ,如果在其中未能找到ORACLE数据源,说明对应于ORACLE 数据源的ODBCDriver驱动程序未有安装。此时,只要点选 New按纽,在出现的“AddDataSource”对话框中选取ORAC LE7.1Driver,“Oracle7ODBCSetup”窗口出现,分别输 入数据库名字和有效的SQL*Net主机连接字符串(格式如 :P/ServerName/InstantId),确认后MicrosoftQrery 就与ORACLE中的数据库相连。这之后,用户就可以象使 用本机上的数据一样,对服务器数据进行操作。查询得到 结果退出MicrosoftQuery时,选择“返回Excel”,数 据就被取回到Excel中,在那里进行报表,制图处理,输出 结果。 这样就可以非常方便地在Excel和ORACLE之间完成数 据互传,实现这两个软件取长补短,使其更好地为我们服 务。 |
1 |
论坛帮助 会员认证删帖申请 联系我们 |