oracle导åºexcel
ä¸ãoracle导åºexcel
æ¹æ³ä¸ï¼æç®åçæ¹æ³---ç¨å·¥å
·plsql dev
æ§è¡File =>newReport Window ãå¨sqlæ ç¾ä¸åå
¥éè¦çsqlï¼ç¹å»æ§è¡ææå¿«æ·é®F8ï¼ä¼å
ååºæ¥è¯¢ç»æãå¨å³ä¾§å·¥å
·æ ï¼å¯ä»¥éæ©æé®å¦å为htmlãcopy as htmlãexportresultsï¼å
¶ä¸export resultsæé®ä¸å°±å¯ä»¥å¯¼åºexcelæ件ãcsvæ件ãtsvæ件ãxmlæ件ã
æ¹æ³äºï¼æä¸ä¸çæ¹æ³---ç¨åå§å·¥å
·sqlplus
åæåè§ï¼
http://www.eygle.com/archives/2005/04/eoasqlplusieaae.htmlæåäºä¸ç¹ä¿®æ¹ï¼å¦ä¸æ示ï¼
1.main.sql
ç¨ä»¥è®¾ç½®ç¯å¢ï¼è°ç¨å
·ä½åè½èæ¬
2.åè½èæ¬-get_tables.sql
为å®ç°å
·ä½åè½ä¹èæ¬
éè¿è¿æ ·ä¸¤ä¸ªèæ¬å¯ä»¥é¿å
spoolä¸çåä½ä¿¡æ¯ï¼åè:
å¦ä½å»é¤SQLPLUSä¸SPOOLçåä½ä¿¡æ¯
示ä¾å¦ä¸:
1.main.sqlèæ¬:
[oracle@jumper utl_file]$ more main.sql
set linesize 200
set term off verify off feedback off pagesize 999
set markup html on entmap ON spool on preformat off
spool d:/tables.xls
@d:/get_tables.sql
spool off
exit
2.get_tables.sqlèæ¬:
[oracle@jumper utl_file]$ more get_tables.sql
select owner,table_name,tablespace_name,blocks,last_analyzed
from all_tables order by 1,2;
3.æ§è¡å¹¶è·å¾è¾åº:
[oracle@jumper utl_file]$ sqlplus "/ as sysdba" @d:/main.sql
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Apr 25 10:30:11 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
[oracle@jumper utl_file]$ ls -l tables.xls
-rw-r--r-- 1 oracle dba 69539 Apr 25 10:30 tables.xls
[oracle@jumper utl_file]$
æ¤å¤è¾åºä¸ºxlsæ件ï¼éè¿ä¸å¾æ们å¯ä»¥çå°è¾åºææ:
æmain.sqlèæ¬ä¸çï¼spooltables.xlsæ´æ¹ä¸ºspool tables.htm,æ们å¯ä»¥è·å¾htmæ ¼å¼è¾åºï¼ææå¦ä¸å¾:
æ¹æ³ä¸ï¼ææ²å§çæ¹æ³ï¼ä¹æ以称为æ²å§ï¼æ¯å 为è¿ä¸ªæ¹æ³å
¶å®å¾ç®åï¼ä½æ¯ä½ å¯è½æ²¡æ注æã
æå¼plsql devå·¥å
·ï¼æ§è¡file=>newsql window ï¼è¾å
¥sqlï¼ç¹å»å·¥å
·æ âæ§è¡âæé®ææå¿«æ·é®F8ãä¼æ¾ç¤ºåºç»æéã
ç¹å»ç»æéçå·¦ä¸æ¹ï¼å¯å
¨é¨éä¸ç»æéï¼ç¶åå³é®=>copyï¼ç´æ¥ç²è´´å°excelæ件ä¸å°±å¯ä»¥äºï¼
æ¹æ³åï¼æåªåçæ¹æ³ï¼è¿ç§æ¹æ³ç¨å¾®æç¹éº»ç¦ï¼ä½ä¸ç¨åèæ¬ï¼ä¹ä¸ç¨psql devå·¥å
·,åªç¨sql pluså°±å¯ä»¥äºã
å¨sqlplusä¸ï¼æ§è¡æ³è¦çsqlï¼æç»æécopyå°ææ¬æ件ä¸ï¼æè
ç´æ¥ç¨spoolå½ä»¤ç´æ¥è¾å
¥å°ææ¬æ件ä¸ï¼ï¼æä¸å¿
è¦çå符ãç©ºæ ¼æ¿æ¢æéå·","ï¼ç¶åå¦å为csvæ件ï¼æåå¨ç¨excelå¦å为exlæ件就å¯ä»¥äºã
äºãæexcelæ件æ°æ®å¯¼å
¥å°oracleçæ个表ä¸ã
æ¹æ³ä¸ï¼æçæ¶çæ¹æ³ï¼éè¦åå©plsql devå·¥å
·ã
a.å¤å¶æ´ä¸ªå·¥ä½ç°¿ä¸çæ°æ®å°æ个表ä¸ã
ç¹å»excelå·¥ä½åºå·¦ä¸è§ï¼å
¨é¨éä¸æ°æ®ï¼ctrl+c å¤å¶æ´ä¸ªå·¥ä½ç°¿ä¸çæ°æ®ãå¨plsql devä¸ï¼ç¼è¾è¡¨æ ¼è¡¨çæ°æ®ï¼ç¹å»æ°æ®å±ç¤ºåºå·¦ä¸è§ï¼ç´æ¥ç²è´´å°±å¯ä»¥äºï¼æ³¨æexcelç第ä¸ååºè¯¥ä¿æ为空ï¼å¦æä¸ä¸ºç©ºå¯ä»¥å¢å ä¸ç©ºåï¼ä¸ç¶ä½ å¤å¶çæ°æ®å°±ä¼å°ä¸åçï¼ï¼å¦ä¸å¾
æ示ï¼
b.å¤å¶æåçæ°æ®ã
è¿ä¸ªå¾å®¹æï¼éä¸excelæä¸åçæ°æ®ï¼å¤å¶ï¼éä¸oracleæ个表çæä¸åï¼ç´æ¥ç²è´´å°±å¯ä»¥äºãplsql devåexcelä¸çåå¯ä»¥ç¸äºå¤å¶ã
æ¹æ³äºï¼æä¸ä¸çæ¹æ³ï¼ç¨sql loaderã
åæå°åï¼
http://daniel-wuz.javaeye.com/blog/1981121.å½å
¥excel æµè¯è¡¨æ ¼ï¼test.xlsã
2.å¦å为.csvæ ¼å¼
3.å建sql*loaderæ§å¶æ件test.ctlï¼å
容å¦ä¸ï¼
Load data
Infile 'c:/test.csv'
insert Into table test Fields terminated by','(column1,column2,column3,column4,column5)
éè¦å°æ°æ®æ件æ·è´å°å¯¹åºä½ç½®
4.å°æ°æ®åºä¸å»ºç«å¯¹åºçæµè¯è¡¨test
create table test (
column1 Varchar2(10),
column2 Varchar2(10),
column3 Varchar2(10),
column4 Varchar2(10),
column5 Varchar2(10)
)
5.æ§è¡å¯¼å
¥å½ä»¤
Sqlldr userid = system/manager control='C:/test.ctl'
导å
¥æå!
éï¼
Sqlldrçå½æ°å
³é®å说æï¼
Userid --oracleç¨æ·å userid = username/password
Control --æ§å¶æ件å称 control =âe:/insert.ctlâ
Log â-æ¥å¿æ件å称 log = âe:/insert.logâ
Bad --æåæ件å称
Data --data file name
Discard --discard file name
Discardmax --number of discards to allow(é»è®¤å
¨é¨)
Skip --导å
¥æ¶è·³è¿çè®°å½è¡æ°ï¼é»è®¤0ï¼
Load --导å
¥æ¶å¯¼å
¥çè®°å½è¡æ°ï¼é»è®¤å
¨é¨ï¼
Errors --å
许é误çè®°å½è¡æ°ï¼é»è®¤50ï¼
ctlæ件å
容说æï¼
Load data
Infile âe:/test.csvâ --æ°æ®æºæ件å称
Append|insert|replace --appendå¨è¡¨å追å ï¼insertæå
¥ç©ºè¡¨ï¼replaceæ¿ä»£åæå
容
Into table test --è¦å¯¼å
¥çæ°æ®åºè¡¨å称
[when id = id_memo] --è¿æ»¤æ¡ä»¶
Fields terminated by Xâ09â --å段åé符
(id,name,telphone) --å段å称å表
æ¹æ³ä¸ï¼ææ²å§çæ¹æ³ï¼å建oracleå¤é¨è¡¨ï¼ä¸ºäºæexcelä¸çæ°æ®å¯¼å
¥å°æ°æ®ä¸èå»å»ºç«å¤é¨è¡¨ï¼å¤§é¢å°åäºï¼
å°excelæ件å¦å为csvæ件a.csvï¼ç¶åå建ä¸ä¸ªå¤é¨è¡¨tï¼æ°æ®æåa.csvãç¶åæ ¹æ®å¤é¨è¡¨å建ä¸ä¸ªæ®éç表ï¼
create table a asselect * from t ï¼è¿æ ·å°±å¯ä»¥ææåçexcelæ件导å
¥å°oracleä¸ç表äºã
æ¹æ³åï¼æå¤å
¸çæ¹æ³ï¼æ¼æ¥sqlè¯å¥ãå¦æä½ excelççè¯ï¼è¿ç§æ¹æ³ä¹ä¸éã
å¨excelä¸ï¼ææ°æ®æ¼æ¥æå¦ä¸sqlè¯å¥ï¼
insert into empvalues('1','2','3');
insert into empvalues('4','5','6');
insert into empvalues('7','8','9');
copy åºä»¥ä¸sqlï¼æ§è¡å°±å¯ä»¥äºï¼ï¼