Use the shell to import a certain format TXT file into the database via SQL load

1. Ready to work, window can directly import files through Sqlload, Linux, need to have sqlload related software.

2. SQL Script (MID_DFDZ.CTL)

1. LOAD DATA
   Into TABLE Mid_t0_trans
   Truncate
   Fields TERMINATED by ' | '
   (
   Stldate,
   Merno,
   Termno,
   Pan
   Transamt,
   REFNO,
   TERMSSN,
   Batno,
   TransDate,
   Workdate,
   Fee
   )

3.shell Script (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 format files (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

Use the shell to import a certain format TXT file into the database via SQL load