shell excel导入mysql_采用SHELL，通过SQL LOAD导入一定格式的txt文件至数据库中

1。

准备工作，window中可直接通过sqlload直接导入文件，linux下，需要有sqlload的相关软件。

2。 SQL脚本(MID_DFDZ.ctl)

LOAD DATA INTO TABLE MID_T0_TRANS

truncate

FIELDS TERMINATED BY '|'

(

stldate,

merno,

termno,

pan,

transamt,

refno,

termssn,

batno,

transdate,

workdate,

fee

)

3.shell脚本(getDFDZ.sh)

#!/bin/bash

if [ $# -lt 2 ]

then

echo "miss arguments"

exit -1

fi

#parameters

BATDATE=$1 SEQ_NO=$2

FILE_DFDZ=DFDZ_${BATDATE}

#FTPADDR=172.16.3.8

FTPADDR=144.131.254.186

FTPUSER=dc

FTPPSWD=dc2012

#home/liushui/

LOCAL_PATH="/100600/xqfdzwj/dfdz"

#FTP_PATH="/weblogic/100600/xqfdzwj/dfdznew/"

FTP_PATH="/dc/jy/dfdz/"

DFDZPATH=/dc/jy/dfdz

DFDZFILE=$DFDZPATH/"DFDZ_"$BATDATE.txt

LOCFILE=/dc/jy/ctl

ftp -in $FTPADDR << !

user $FTPUSER $FTPPSWD

bin

lcd $FTP_PATH

cd $LOCAL_PATH

echo pwd

get $FILE_DFDZ

bye

!

DBUSER=dc

DBPWD=dc_2015

DBNAME=dcdb

DFDZCTLFILE=$LOCFILE/MID_DFDZ.ctl

DFDZBADFILE=$LOCFILE/${BATDATE}_dfdz.bad

DFDZLOGFILE=$LOCFILE/${BATDATE}_dfdz.log

sqlplus -s

${DBUSER}/${DBPWD}@${DBNAME}<

ALTER TABLE TB_MAINTENANCE nologging

!

sqlldr userid=${DBUSER}/${DBPWD}@${DBNAME} control=$DFDZCTLFILE

data=$DFDZFILE log=$DFDZLOGFILE bad=$DFDZBADFILE 　　errors=1000000

rows=10000

RESULT=$?

if [ $RESULT -ne 0 ]

then

SetCmdRslt $SEQ_NO 3

exit -1

fi

RESULT=$?

if [ $RESULT -ne 0 ]

then

exit -1

fi

exit 0

4。 txt格式文件(T0_H_20151009.txt)

20151009|898340150399030|01086952|6214855491055386|1300.00|000789608053|000308|000001|133904|20151009|0.65

20151009|898320559990126|55447203|6225768706558846|4085.00|000789614773|001363|000001|134011|20151009|0.00

20151009|898620950130467|942E0166|6259654270166308|7930.00|000789615305|000218|000001|134011|20151009|3.97

20151009|898654059982123|65412446|6217858300020175452|42097.00|000789614807|000069|000001|134013|20151009|21.05

20151009|898430153980696|21183401|6222060012065499|97555.00|000789615795|000102|000001|134014|20151009|48.78

20151009|898210245112423|15061518|4062522603299967|59900.00|000789615371|000833|000001|134016|20151009|29.95

20151009|898411459982063|41145254|4512893437225106|16000.00|000789615837|000121|000003|134017|20151009|16.00

20151009|898532550394078|TF407801|4581242413584282|22000.00|000789614459|000361|000001|134020|20151009|11.00