excel导入导出ORACLE.doc

1.首先确定表结构和EXCEL文件结构,保证二者一一对应
表结构
create table DS_AREA_NUM_STATS
(
DS_SN CHAR(20),
DS_NAME VARCHAR2(50),
AREA_CODE VARCHAR2(20),
LINE_COUNT NUMBER(12),
STAT_DATE DATE,
DATA_BATCH VARCHAR2(8)
);

2.数据库生成表
SQL> drop table v_area_stats_result;

Table dropped

SQL>
SQL> create table v_area_stats_result(
2 NAME VARCHAR2(200),
3 DAITEM_NAME VARCHAR2(255),
4 DADIFF_NAME VARCHAR2(50),
5 DATA_BATCH VARCHAR2(8),
6 TOTAL_CNT NUMBER,
7 ERR_CNT NUMBER,
8 MODIFY_CNT NUMBER
9 )
10 ;

Table created

SQL> commit;

Commit complete

3.pl/sql developer打开sql window

4.点锁头,点+

5.全选EXCEL数据,ctrl_c拷贝

6.sql window中点中表的第一列,然后选paste

7. 点中第一条记录(实际是EXCEL里的表头),点减号,删掉它

点√, 进行CHECK和COMMIT

8. 关掉SQL WINDOW窗口

9.打开COMMAND WINDOW窗口,输入指令,查看导入结果
SQL> select * from v_area_stats_result;

NAME DAITEM_NAME DADIFF_NAME DATA_BATCH TOTAL_CNT ERR_CNT MODIFY_CNT
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------- ---------- ---------- ---------- ----------
宿迁 BOSS_SHLR_彩铃三方彩铃服务比对 SHLR独有数 20110615 443317 76 0
宿迁 BOSS_SHLR_彩铃三方彩铃服务比对 SHLR遗漏数 20110615 443317 7597 0
宿迁 BOSS_SHLR_彩铃三方彩铃服务比对 彩铃平台独有数 20110615 443317 1796 1796
宿迁 BOSS_SHLR_彩铃三方彩铃服务比对 彩铃平台遗漏数 20110615 443317 236 236
宿迁 BOSS_SHLR两方固话产品实例比对 BOSS为停机，SHLR平台为正常 20110615 901686 467 0
宿迁 BOSS_SHLR两方固话产品实例比对 BOSS系统独有数 20110615 901686 5055 5055
宿迁 BOSS_SHLR两方固话产品实例比对 SHLR独有数 20110615 901686 4137 4137
宿迁 BOSS_SHLR两方固话产品实例比对 逻辑号码一致，物理号码不一致 20110615 901686 3188 3188
宿迁 BOSS_彩铃平台_HLR三方被叫彩铃服务比对

ORACLE 导入EXCEL
1. 建立ORACLE ODBC数据源

2. EXCEL选择 数据/自其它来源

3.选择’来自数据连接向导’

选择ODBC DSN

点下一步,选择 第一步建好的ORACLE ODBC数据源orclscott

点下一步,输入用户名和密码,服务器输入ORACLE DATABASE
点确定
打开数据库表列表,选择需要导入的表

点下一步,

点完成

点确定

又要求输入一次密码

输入密码,点确定

Pl/sql developer打开sql window, 输入select * from 表名,F8,选择导出成CSV文件

导出CSV文件如下:
"NAME","DAITEM_NAME","DADIFF_NAME","DATA_BATCH","TOTAL_CNT","ERR_CNT","MODIFY_CNT"
"宿迁","BOSS_SHLR_彩铃三方彩铃服务比对","SHLR独有数","20110615","443317","76","0"
"宿迁","BOSS_SHLR_彩铃三方彩铃服务比对","SHLR遗漏数","20110615","443317","7597","0"
"宿迁","BOSS_SHLR_彩铃三方彩铃服务比对","彩铃平台独有数","20110615","443317","1796","1796"
"宿迁","BOSS_SHLR_彩铃三方彩铃服务比对","彩铃平台遗漏数","20110615","443317","236","236"
"宿迁","BOSS_SHLR两方固话产品实例比对","BOSS为停机，SHLR平台为正常","20110615","901686","467","0"
"宿迁","BOSS_SHLR两方固话产品实例比对","BOSS系统独有数","20110615","901686","5055","5055"
"宿迁","BOSS_SHLR两方固话产品实例比对","SHLR独有数","20110615","901686","4137","4137"
"宿迁","BOSS_SHLR两方固话产品实例比对","逻辑号码一致，物理号码不一致","20110615","901686","3188","3188"
"宿迁","BOSS_彩铃平台_HLR三方被叫彩铃服务比对","BOSS为停机，彩铃平台为正常","20110617","181516","6105","0"
"宿迁","BOSS_彩铃平台_HLR三方被叫彩铃服务比对","BOSS为正常，彩铃平台为停机","20110617","181516","254","0"
"宿迁","BOSS_彩铃平台_HLR三方被叫彩铃服务比对","BOSS系统独有数","20110617","181516","53","0"
"宿迁","BOSS_彩铃平台_HLR三方被叫彩铃服务比对","BOSS系统遗漏数","20110617","181516","59","0"
"宿迁","BOSS_彩铃平台_HLR三方被叫彩铃服务比对","C网HLR独有数","20110617","181516","6","0"
"宿迁","BOSS_彩铃平台_HLR三方被叫彩铃服务比对","C网HLR遗漏数","20110617","181516","8","0"
"宿迁","BOSS_彩铃平台_HLR三方被叫彩铃服务比对","彩铃平台独有数","20110617","181516","1186","0"
"宿迁","BOSS_彩铃平台_HLR三方被叫彩铃服务比对","彩铃平台遗漏数","20110617","181516","976","0"

EXCEL 进入’数据’,选择’自文本’
选择刚才PL/SQL导出的111.csv文件

点导入

点下一步

选择分隔符号,缺省是TAB键,改为逗号,CSV文件是以逗号分隔
点下一步

点完成.

点确定

看结果: