怎样把大量的数据通过txt文件直接导入到数据库中 求解 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 你可以用import工具导入大量数据。 plsql developer工具中有专门的 数据导入工具,好用的很。 Sqlldr工具很方便,寫一個控制文件比較煩,不過可以從文本文件格式決定如何導入數據庫。 #!/bin/sh################################################################################# 模块: load_data.sh## 修改记录# 日期 修改人 修改描述# 20120703 ken 新建脚本#################################################################################set -xif [ $# -ne 6 ];then echo "Usage: `basename $0` <userid><tablename><filename><character:0 default;1 UTF-8><space><errors>" exit -1fi#数据库连接字符串userid=$1#目标表名table_name=`echo ${2}|tr [a-z] [A_Z]`#文件名load_data=$3#文件字符集charset=`echo ${4}|sed 's/0//g'|sed 's/1/CHARACTERSET '\'UTF8\''/g'`#文件分隔符space=$5#允许出错行数errors=$6logdate=`date +'%Y%m%d'`if [ ! -d $BIPROG_ROOT/log/load/$logdate ]then mkdir -p $BIPROG_ROOT/log/load/$logdatefi#日志路径logfile=/home/etl/log/load/${logdate}/${table_name}".log"collect_dt=$load_data$logdate"000000"file_time=`echo $collect_dt|awk '{print substr($0,index($0,20),14)}'|sed 's/[^0-9]//g'`#获取采集日期collect_dt=`echo $file_time|awk '{print substr($0,1,8)}'`file_time=`echo $file_time|awk '{print substr($0,1,4)"-"substr($0,5,2)"-"substr($0,7,2)" "substr($0,9,2)":"substr($0,11,2)":"substr($0,13,2)}'`echo "sqlplus $userid <<EOF">$logfileecho "spool $table_name".sql" desc $table_name " >>$logfileecho "EOF" >>$logfile. $logfiletmpctl=$table_name".ctl"tmpctl2=$table_name"tmp2.ctl"tmpctl3=$table_name"tmp3.ctl"echo "Load data $charset infile '"$load_data"' Append into table $table_name fields terminated by '"$space"' OPTIONALLY ENCLOSED BY '\'' TRAILING NULLCOLS (">$tmpctl#cat $table_name".sql"|sed '1,3d'|sed '/^$/d'|grep -v "SQL\>"|sed 's/NOT NULL//g'|sed 's/NUMBER([0-9]*)//g'|sed 's/NUMBER//g'|sed 's/VARCHAR2(2[0-5][0-5])//g'|sed 's/VARCHAR2(1[0-9][0-9])//g'|sed 's/VARCHAR2([0-9])//g'|sed 's/VARCHAR2([0-9][0-9])//g'|sed 's/VARCHAR2/CHAR/g'|awk '{print $1"\t"$2","}'>$tmpctl2cat $table_name".sql"|sed '1,3d'|sed '/^$/d'|grep -v "SQL\>"|sed 's/NOT NULL//g'|awk '{print $1"\t"$2","}'>$tmpctl2loops=1while [ $loops -le `cat ${tmpctl2}|wc -l` ]do if [ `cat ${tmpctl2}|sed -n $loops'p'|awk '{print substr($2,1,3)}'` = 'VAR' ] then len=`cat ${tmpctl2}|sed -n $loops'p'|awk '{print $2}'|sed 's/VARCHAR2(//g'|sed 's/)//g'|sed 's/,//g'` if [ $len -gt 255 ] then cat ${tmpctl2}|sed -n $loops'p'|sed 's/VARCHAR2/CHAR/g'>>$tmpctl3 else cat ${tmpctl2}|sed -n $loops'p'|awk '{print $1","}'>>$tmpctl3 fi else cat ${tmpctl2}|sed -n $loops'p'|awk '{print $1","}'>>$tmpctl3 fi loops=`expr $loops + 1`donecat $tmpctl3|sed '$d'>>$tmpctlcat $tmpctl3|sed -n '$p'|sed 's/,/)/g'>>$tmpctlsqlldr userid=$userid control=$table_name".ctl" log=$logfile errors=$errorsrm $tmpctlrm $tmpctl2rm $tmpctl3rm $table_name".sql"--自己改改吧 自动生成控制文件入库的 文件名:jphk.ctl文件內容:LOAD DATAinfile 'C:\Users\xyz\Documents\jphk.txt'badfile 'C:\Users\xyz\Documents\jphk.bad'discardfile 'C:\Users\xyz\Documents\jphk.dis'append into table user_name.JPHKfields terminated by ' '(no,hunic,hsu,kunic,ksu,gbks char(2000))導入命令: sqlldr user_name/password@orcl control=jphk.ctl log=jphk.log 除sqlldr外,也可以借助第三方工具,比如toad,plsqldev等,都可以 普通用户,如何查询oracle当前schema里面有多少个tablespace的名字? show parameter是不是从v$parameters取的? 全部家当放上了,紧急求救,关于mysql的跨库连接 oracle sql语句问题 一条SQL语句的优化 几万商品的几万条销售记录动态跟踪销售量 数据库导入时遇到 ORACLE error 942 存储过程调用 如何将9.2.0.1.0客户端升级到9.2.0.5.0? 给个Oracle9的下载地址! lsnrctl stop 监听启动和停止都报错,数据库能正常打开 oracle11g linux PLSQL 循环语句问题
################################################################################
# 模块: load_data.sh
#
# 修改记录
# 日期 修改人 修改描述
# 20120703 ken 新建脚本
#################################################################################set -xif [ $# -ne 6 ];then
echo "Usage: `basename $0` <userid><tablename><filename><character:0 default;1 UTF-8><space><errors>"
exit -1
fi#数据库连接字符串
userid=$1#目标表名
table_name=`echo ${2}|tr [a-z] [A_Z]`
#文件名
load_data=$3
#文件字符集
charset=`echo ${4}|sed 's/0//g'|sed 's/1/CHARACTERSET '\'UTF8\''/g'`
#文件分隔符
space=$5
#允许出错行数
errors=$6logdate=`date +'%Y%m%d'`
if [ ! -d $BIPROG_ROOT/log/load/$logdate ]
then
mkdir -p $BIPROG_ROOT/log/load/$logdate
fi#日志路径
logfile=/home/etl/log/load/${logdate}/${table_name}".log"
collect_dt=$load_data$logdate"000000"
file_time=`echo $collect_dt|awk '{print substr($0,index($0,20),14)}'|sed 's/[^0-9]//g'`#获取采集日期
collect_dt=`echo $file_time|awk '{print substr($0,1,8)}'`
file_time=`echo $file_time|awk '{print substr($0,1,4)"-"substr($0,5,2)"-"substr($0,7,2)" "substr($0,9,2)":"substr($0,11,2)":"substr($0,13,2)}'`echo "sqlplus $userid <<EOF">$logfile
echo "spool $table_name".sql"
desc $table_name
" >>$logfile
echo "EOF" >>$logfile
. $logfiletmpctl=$table_name".ctl"
tmpctl2=$table_name"tmp2.ctl"
tmpctl3=$table_name"tmp3.ctl"echo "Load data $charset infile '"$load_data"' Append into table $table_name fields terminated by '"$space"' OPTIONALLY ENCLOSED BY '\'' TRAILING NULLCOLS (">$tmpctl#cat $table_name".sql"|sed '1,3d'|sed '/^$/d'|grep -v "SQL\>"|sed 's/NOT NULL//g'|sed 's/NUMBER([0-9]*)//g'|sed 's/NUMBER//g'|sed 's/VARCHAR2(2[0-5][0-5])//g'|sed 's/VARCHAR2(1[0-9][0-9])//g'|sed 's/VARCHAR2([0-9])//g'|sed 's/VARCHAR2([0-9][0-9])//g'|sed 's/VARCHAR2/CHAR/g'|awk '{print $1"\t"$2","}'>$tmpctl2
cat $table_name".sql"|sed '1,3d'|sed '/^$/d'|grep -v "SQL\>"|sed 's/NOT NULL//g'|awk '{print $1"\t"$2","}'>$tmpctl2loops=1
while [ $loops -le `cat ${tmpctl2}|wc -l` ]
do
if [ `cat ${tmpctl2}|sed -n $loops'p'|awk '{print substr($2,1,3)}'` = 'VAR' ]
then
len=`cat ${tmpctl2}|sed -n $loops'p'|awk '{print $2}'|sed 's/VARCHAR2(//g'|sed 's/)//g'|sed 's/,//g'`
if [ $len -gt 255 ]
then
cat ${tmpctl2}|sed -n $loops'p'|sed 's/VARCHAR2/CHAR/g'>>$tmpctl3
else
cat ${tmpctl2}|sed -n $loops'p'|awk '{print $1","}'>>$tmpctl3
fi
else
cat ${tmpctl2}|sed -n $loops'p'|awk '{print $1","}'>>$tmpctl3
fi
loops=`expr $loops + 1`
donecat $tmpctl3|sed '$d'>>$tmpctlcat $tmpctl3|sed -n '$p'|sed 's/,/)/g'>>$tmpctlsqlldr userid=$userid control=$table_name".ctl" log=$logfile errors=$errorsrm $tmpctl
rm $tmpctl2
rm $tmpctl3
rm $table_name".sql"
--自己改改吧 自动生成控制文件入库的
文件內容:
LOAD DATA
infile 'C:\Users\xyz\Documents\jphk.txt'
badfile 'C:\Users\xyz\Documents\jphk.bad'
discardfile 'C:\Users\xyz\Documents\jphk.dis'
append into table user_name.JPHK
fields terminated by ' '
(no,hunic,hsu,kunic,ksu,gbks char(2000))導入命令: sqlldr user_name/password@orcl control=jphk.ctl log=jphk.log