批处理实现从Excel导入Oracle-gjhgkh
@Echo OffColor 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
页:
[1]