sqlldr+awk+shell的一次記錄

 

近期，筆者想對公司伺服器維保期的時間進行一個維護，也就是將維保要過期的伺服器及時郵件通知系統管理員。其實在筆者的知識範圍內，用awk實現最好不過，但是卻對awk的兩個日期比較，大為不解。於是準備將伺服器維保期的excel表格匯入oracle資料庫進行處理，在結合linux shell+awk解決。

<!--more--&gt

 

現寫此文以記之。

 

1、先將excel中空單元格按照下述方法，隨便填充一個資料，這樣做的目的是為了使excel表格的資料保持一致性。

1.1按下圖，找到定位條件:

1.2如下操作：

 

1.3 然後在其中任一個空單元格中隨便輸入一個值，按ctl+Enter確認，即可將所有空單元格填充資料。

2、現用sqlldr將excel匯入oracle，需要先在資料庫中建立一張表，透過如下方法建立。

2.1 選取excel欄位名，如下方式進行行列轉換：

 

 

 

 

2.2 在欄位名上加雙引

可以改一下單元格格式，為自定義，"“"@"”"

 

 

 

2.3 生產建表語句

 

 

 

3、將excel表格另存為jwt.csv，上傳到oracle伺服器/home/oracle目錄下。

4.1當改成以csv結尾的檔案後，如想將其改成和linux系統相容的utf8格式（這樣文件中的中文就能在linux系統中顯示了），如下：

 

 

 

 

 

4、使用sqlldr匯入資料

4.1 在/home/oracle目錄下建立控制檔案1.ctl

cat /home/oracle/1.ctl

 

4.2 在oracle使用者下執行：

node1-> sqlldr scott/tiger control=/home/oracle/1.ctl bad=/home/oracle/bad.txt log=/home/oracle/log.txt  SKIP=1

執行上面的命令後，就會在oracle資料庫中建立一張名字叫jwt的表。

 

5、編寫shell指令碼，滿足條件的發郵件：

 

 

 

小記：

如果發現郵件發不出去，檢查/var/spool/mqueue目錄下面是否有排隊的郵件，並檢視/var/log/maillog日誌資訊。我遇到發不出去郵件時，我重啟一下服務就好了（service sendmail restart）。

 

完。

 

更新1：
最近又重新做了一下。
SQL> desc jwt;
 GLY                                                                                                                                                                   VARCHAR2(100)
 MAIL                                                                                                                                                                  VARCHAR2(100)
 XYNAME                                                                                                                                                                VARCHAR2(200)
 IP                                                                                                                                                                    VARCHAR2(100)
 WBRQ                                                                                                                                                                  VARCHAR2(100)


[oracle@centos-msgw2 ~]$ head jwt1.csv
gly,mail,xyname,ip,wbrq
xx,xx@hn.com,xx系統,10.99.32.133,2013/7/21


[oracle@centos-msgw2 ~]$ cat 1.ctl 
LOAD DATA
INFILE '/home/oracle/jwt1.csv'
truncate  INTO TABLE jwt
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(gly ,mail,xyname,ip,wbrq)

[oracle@centos-msgw2 ~]$ sqlldr scott/tiger control=/home/oracle/1.ctl bad=/home/oracle/bad.txt log=/home/oracle/log.txt  SKIP=1 

[oracle@centos-msgw2 ~]$ cat mail.sh 
#!/bin/bash

sqlplus -s 'scott/tiger'<<EOF

set linesize 1000

col gly for a15

col mail for a25

col xyname for a50

col ip for a28

set heading off

spool /tmp/a.txt

select  * from jwt where (to_date(wbrq,'yyyy/mm/dd')-sysdate)<30 and (to_date(wbrq,'yyyy/mm/dd')-sysdate)>0 ;

spool off

exit;

EOF

cat /tmp/a.txt |grep -v "^$" |grep -v select |awk -F" " '{print "echo -n    親愛的"$1",""您維護的"$3",""ip地址為"$4", 伺服器維保期將在"$5"過期,請您及時和業務單位聯絡維保事宜,謝謝""|""mail -s server_expired "$2,"zhix_chen@hnair.com","lfang_wu@hnair.com" }' > /tmp/b.sh

sed -i 's/親愛的/"親愛的/' /tmp/b.sh

sed -i 's/謝謝/謝謝"/' /tmp/b.sh

/bin/bash /tmp/b.sh

 

更新2：

2015年7月20日星期一

 

    今天，我又做了一次伺服器維保過期告警提醒。這次我完全用awk做的。

    資料的excel表是這樣的：

   

 

    然後將起儲存中csv格式，然後再將這個csv的檔案，右鍵點選編輯開啟後，再另存一下，將其編碼格式轉換為utf8.最後將這個csv檔案上傳到linux目錄下。我的檔名為/home/oracle/jwt0720.csv。

    對了，注意上面截圖中的時間戳一列，我是將維保日期透過excel轉化成了時間戳，這樣是為了方便在linux中透過awk進行時間的運算。

 

    EXCEL中把日期轉為unix時間戳方法：INT((B2-70*365-19)*86400-8*3600)

還有一點，就是我為了判斷excel裡面有透過ALT+Enter換行的單元格，我先把上面csv表格透過sqlldr匯入進oracle資料庫，然後檢視bad.txt的行，這些行說明有問題，然後我再一一手工到csv表格中去對比分析，看是什麼問題並改正。

 

create table jwt (

gly varchar2(255),

mail varchar2(255),

xyname varchar2(255),

ip varchar2(50),

jw varchar2(100),

jixing varchar2(100),

xulh varchar2(255),

wbrq varchar2(100)

);

 

 

[oracle@centos-msgw2 ~]$ cat 1.ctl

LOAD DATA

INFILE '/home/oracle/jwt0720.csv'

truncate  INTO TABLE jwt

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

trailing nullcols  ##如要匯入原始檔此列內容為空，在匯入到資料庫表中，此列內容就是null

(

gly,

mail,

xyname,

ip,

jw,

jixing,

xulh,

wbrq

)

 

sqlldr scott/tiger control=/home/oracle/1.ctl bad=/home/oracle/bad.txt log=/home/oracle/log.txt  SKIP=1

 

 

 

    下面開始在linux上編寫指令碼：