当前位置:孟关伦博客 » 技术交流 »批处理实现从Excel导入Oracle
批处理实现从Excel导入Oracle
   作者:孟关伦  2015年8月15日 21:51 星期六  热度:2144°  字号:   评论:0 条
时间:2015-8-15 21:51   热度:2144°  评论:0 条 

之前孟关伦做过一个从Oracle导出到Excel的批处理,简单点说就是通过Sqlplus将表数据Spool到一个文本文件中或简化的Excel格式文档中,而这里我要做的事恰好相反,工具则是使用sqlldr, sqlldr的用法没有研究太多,这里主要用写写如何使用批处理来调用实现导入的自动化过程。

主要工作涉及到以下几块:

1. 将Excel转换成csv格式的文件,这里要借助工具xls2csv.exe实现excel到csv的转换;

xls2csv的用法: xls2csv excelfile.xls gbk

以上命令执行后,程序会将excel文件中的每一张sheet都生成一个csv文件,并以excel文件的名字_sheet名字.csv命名

2. 使用Sqlldr将数据导入Oracle,

Sqlldr的用法:sqlldr user/pass@tnsname control=example.ctl data=data.csv

以上命令执行后,sqlldr会将data.txt数据文件中数据按照control指定的规则插入到数据库中,具体的插入规则和表名都在control文件中指定。

Control文件写法:

load data 
infile 'data.csv' 
append 
into table t_tab_data 
fields terminated by ';' optionally enclosed by '"' 

filed1, 
filed2, 
filed3 
)

以上语法文件中指定了从文件data.csv中导数据,导入到表t_tab_data中,

fields terminated by ';' optionally enclosed by '"' 表示数据文件中字段以;分隔,各字段中的"忽略掉,这些规则也可以写到各字段后,分别对各字段进行约束。

另外,Control文件中的infile ‘data.csv’也可以写成infile ‘*', 这样data.csv中的数据可以直接放到control文件的最后,sqlldr会在读完control文件的规则部分后,再去读取数据部分导入数据库。

3. 将上面要做的工作写成批处理

@Echo Off 
Color 0A 
Title Network Config Assistant By Eric 
Pushd %CD% 
Prompt $G 
MODE CON COLS=100 LINES=30 
SETLOCAL ENABLEEXTENSIONS 
SETLOCAL ENABLEDELAYEDEXPANSION 
Set OraUserPass=icd/icd@Eric

Echo 1. Start importing table list to database... 
Echo ========================================================

Set /P xlsfilename=table List in Excel format(tables.xls as default): 
IF /I "%xlsfilename%"=="" Set xlsfilename=tables.xls

Echo Generating csv file for table list from excel... 
start /min /wait xls2csv.exe %xlsfilename% gbk

FOR /F %%i in ('DIR /B *.CSV /O:-D') DO ( 
    SET FILENAME=%%i 
    GOTO :EXITFOR 
)

:EXITFOR 
Echo Generate csv file "%FILENAME%" successfully.

Echo/

Set tmptableTab=t_test_table

REM ==================Check table for table Configed in IEX================= 
Echo Start checking table !tmptableTab!... 
Echo select * from !tmptableTab! where 1^<^>1;>%temp%/tmpCheckTab.sql 
Echo exit;>>%temp%/tmpCheckTab.sql 
Sqlplus %OraUserPass% @"%temp%/tmpCheckTab.sql" | find /I "ERROR">NUL && IF errorlevel 0  ( 
    Echo Table !tmptableTab! is not exist. 
    CALL :CreatetableTable 
    Goto :tableClear 
)

REM ==================Truncate table t_test_table================ 
Echo Table !tmptableTab! is already exist. 
Echo/

Echo Truncating table !tmptableTab!... 
Echo Truncate table !tmptableTab!;>%temp%/tmpTruncate.sql 
Echo Exit>>%temp%/tmpTruncate.sql 
Sqlplus %OraUserPass% @"%temp%/tmpTruncate.sql" 1>NUL 
Echo Truncate table !tmptableTab! finished.

:tableClear 
Echo/ 
REM ==================Import data from list to db================= 
REM ----Generate control file---- 
Set tmptableCtrlFile="%CD%/table.ctl"

REM Echo load data>%tmptableCtrlFile% 
REM Echo infile '%FILENAME%'>>!tmptableCtrlFile! 
REM Echo append>>!tmptableCtrlFile! 
REM Echo into table icd.t_test_table>>!tmptableCtrlFile! 
REM Echo fields terminated by ';' optionally enclosed by '^"'>>!tmptableCtrlFile! 
REM Echo (>>!tmptableCtrlFile! 
REM Echo tableid,>>!tmptableCtrlFile! 
REM Echo tablename,>>!tmptableCtrlFile! 
REM Echo tableskill>>!tmptableCtrlFile! 
REM Echo ^)>>!tmptableCtrlFile!

Echo Importing data to table !tmptableTab!... 
sqlldr %OraUserPass% control=table.ctl 1>NUL 
Echo Importing data to table !tmptableTab! finished.

Echo/

REM ============Create table t_test_table================ 
:CreatetableTable 
IF /I "%0"==":CreatetableTable" ( 
Echo Start creating table !tmptableTab!... 
Echo Create table t_test_table ^(>%temp%/tmptableTab.sql 
Echo tableid number(5^),>>%temp%/tmptableTab.sql 
Echo tablename varchar2(50^),>>%temp%/tmptableTab.sql 
Echo tableskill varchar2(2000^)>>%temp%/tmptableTab.sql 
Echo ^);>>%temp%/tmptableTab.sql 
Echo Exit>>%temp%/tmptableTab.sql

Sqlplus %OraUserPass% @"%temp%/tmptableTab.sql" 1>NUL 
Echo Create table !tmptableTab! successfully. 
Echo/ 
Goto :EOF 
)

Echo 2. Export data report of discrepancy... 
Echo ======================================================== 
Echo Exporting report from database, please wait... 
Start /WAIT ExportBySql.bat %OraUserPass% report.xls 
Echo Export report from database finished. 
Echo/ 
Echo/

:EXIT 
Pause


 您阅读这篇文章共花了: 
本文作者:孟关伦      文章标题: 批处理实现从Excel导入Oracle
本文地址:http://www.mengguanlun99.com/post-111.html
版权声明:若无注明,本文皆为“孟关伦博客”原创,转载请保留文章出处。 本文已被百度收录!