iamlaosong文
其实很简单,只要根据原始数据的格式,在控制文件中字段名后面加上日期格式字符串就行了。例如:
load data
infile '订单1012-1031.csv'
append into table emsapp_zywl_ddmx
fields terminated by ','
optionally enclosed by '"'
(ddh,xqdh,lsrq date 'YYYY-MM-DD hh24:mi:ss',lsjg,sfj,jsj,wpzl,wptj,wpjs,smjz,psfs,zzfw,mdqx,mdcs,mdsf,sfqx,sfcs,sfsf)
其中lsrq就是日期字段,内容是日期和24小时制的时间,如:
2017-10-12 15:00:31
这个控制文件由下面的shell脚本生成,本脚本负责将指定目录下的csv文件导入数据库中:
BAKDIR=/home/oracle/ems_zywl
CTLFILE=control.ctl
cd ${BAKDIR}
for csvfile in *.csv
do
# process csvfile
echo ${csvfile}
# read -p "csv file: press enter to continue..." var
if [ -f ${csvfile} ]; then
# generate control file
echo load data>${CTLFILE}
echo infile "'"${csvfile}"'">>${CTLFILE}
echo append into table emsapp_zywl_ddmx>>${CTLFILE}
echo fields terminated by "','">>${CTLFILE}
echo optionally enclosed by "'\"'">>${CTLFILE}
echo "(ddh,xqdh,lsrq date 'YYYY-MM-DD hh24:mi:ss',lsjg,sfj,jsj,wpzl,wptj,wpjs,smjz,psfs,zzfw,mdqx,mdcs,mdsf,sfqx,sfcs,sfsf)">>${CTLFILE}
# import data
sqlldr userid=emssxjk/emssxjk control=control.ctl skip=1
mv ${csvfile} all_csv
fi
done