批處理實現從Excel匯入Oracle

之前做過一個從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