EXCEL与ORACLE间的数据互传法

文章作者 100test 发表时间 2007:03:14 13:29:13
来源 100Test.Com百考试题网

众所周知，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　FILEDS TERMINATED BY "，" （Emp－No，Ename，Salary，HiredateDATA（8） "Y Y/MM/DD"）

　　之后，双击SQL*Loader图标，进入ORA�.CLE7的数据装载工具，在弹出的SQLLOADER主屏幕对话框中，分别输入用户登录名，密码，数据库名称和控制文件名，确认后进行数据装载。这样SQL*Loader一边转换、装入数据，一边将执行过程中的错误信息和统计信息存入登录文件（LogFile）中，把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 INFILEC：\EMP.PRN 　BADFILEEMP.BAD DISCARDFILE EMP.DSC APPEND INTOTABLE EMP（Emp－NoPOSITION（01：05）INTEGER EXTERNAL，EnamePOSITION（06：15）CHAR， SalaryPOSITION（16：25）DECIMAL EXTERNAL， HiredatePOSITION（26：34）INTEGEREXTERNAL） INTOTABLEPROJ WHENProj－No！K （Emp－NoPOSITION（1：4）INTEGEREXTERNAL， Proj－NoPOSITION（35：38）INTEGEREXTERNAL） INTOTABLEPROJ WHENProj－No！K （Emp－NoPOSITION（1：4）INTEGEREXTERNAL， Proj－NoPOSITION（39：42）INTEGEREXTERNAL） INTOTABLEPROJ WHENProj－No！K （Emp－NoPOSITION（1：4）INTEGEREXTERNAL， Proj－NoPOSITION（43：46）INTEGEREXTERNAL）

　　注意，CHAR，INTEGEREXTERNAL和DECIMALEXTERNAL这些数据类型是指数据文件中的数据类型，而不是数据库中数表的数据类型。数字字段的EXTERNAL暗示了它不是以二进制格式存储的数字，而是以人可以识别的ASCII码格式存储。

　　这样运行SQL*Loader之后，所需数据被分别装入两个数表，非常方便。

　　2、将ORACLE数据文件转化为Excel文件 当需要在Excel下获取ORACLE数据时，可以通过ODBC （假设ORACLE是安装在Win�.dowsNT操作系统下）。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之间完成数据互传，实现这两个软件取长补短，使其更好地为我们服务。