批处理实现从Excel导入Oracle-gjhgkh

色婆ijqwj

　　@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 agent list to database...
　　Echo ========================================================
　　Set /P xlsfilename=Agent List in Excel format(agents.xls as default):
　　IF /I "%xlsfilename%"=="" Set xlsfilename=agents.xls
　　Echo Generating csv file for agent 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 tmpAgentTab=t_test_agent
　　REM ==================Check table for Agent Configed in IEX=================
　　Echo Start checking table !tmpAgentTab!...
　　Echo select * from !tmpAgentTab! 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 !tmpAgentTab! is not exist.
　　CALL :CreateAgentTable
　　Goto :AgentClear
　　)
　　REM ==================Truncate table t_test_agent================
　　Echo Table !tmpAgentTab! is already exist.
　　Echo\
　　Echo Truncating table !tmpAgentTab!...
　　Echo Truncate table !tmpAgentTab!;>%temp%\tmpTruncate.sql
　　Echo Exit>>%temp%\tmpTruncate.sql
　　Sqlplus %OraUserPass% @"%temp%\tmpTruncate.sql" 1>NUL
　　Echo Truncate table !tmpAgentTab! finished.
　　:AgentClear
　　Echo\
　　REM ==================Import data from list to db=================
　　REM ----Generate control file----
　　Set tmpAgentCtrlFile="%CD%\agent.ctl"
　　REM Echo load data>%tmpAgentCtrlFile%
　　REM Echo infile '%FILENAME%'>>!tmpAgentCtrlFile!
　　REM Echo append>>!tmpAgentCtrlFile!
　　REM Echo into table icd.t_test_agent>>!tmpAgentCtrlFile!
　　REM Echo fields terminated by ';' optionally enclosed by '^"'>>!tmpAgentCtrlFile!
　　REM Echo (>>!tmpAgentCtrlFile!
　　REM Echo agentid,>>!tmpAgentCtrlFile!
　　REM Echo agentname,>>!tmpAgentCtrlFile!
　　REM Echo agentskill>>!tmpAgentCtrlFile!
　　REM Echo ^)>>!tmpAgentCtrlFile!
　　Echo Importing data to table !tmpAgentTab!...
　　sqlldr %OraUserPass% control=agent.ctl 1>NUL
　　Echo Importing data to table !tmpAgentTab! finished.
　　Echo\
　　REM ============Create table t_test_agent================
　　:CreateAgentTable
　　IF /I "%0"==":CreateAgentTable" (
　　Echo Start creating table !tmpAgentTab!...
　　Echo Create table t_test_agent ^(>%temp%\tmpAgentTab.sql
　　Echo agentid number(5^),>>%temp%\tmpAgentTab.sql
　　Echo agentname varchar2(50^),>>%temp%\tmpAgentTab.sql
　　Echo agentskill varchar2(2000^)>>%temp%\tmpAgentTab.sql
　　Echo ^);>>%temp%\tmpAgentTab.sql
　　Echo Exit>>%temp%\tmpAgentTab.sql
　　Sqlplus %OraUserPass% @"%temp%\tmpAgentTab.sql" 1>NUL
　　Echo Create table !tmpAgentTab! 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