你的位置:首页 > 软件开发 > 数据库 > 导入CSV格式的数据

导入CSV格式的数据

发布时间:2016-08-14 14:00:13
导入CSV格式的数据(参见http://dev.mysql.com/doc/refman/5.6/en/load-data.html)1.数据库表(st_pptn_r)CREATE TABLE st_pptn_r (STCD varchar(8) DEFAULT NU ...
导入CSV格式的数据(参见http://dev.mysql.com/doc/refman/5.6/en/load-data.html)1.数据库表(st_pptn_r)CREATE TABLE st_pptn_r (STCD     varchar(8)   DEFAULT NULL,TM     datetime     DEFAULT NULL,DRP     decimal(5,1)   DEFAULT NULL,INTV     decimal(5,2)   DEFAULT NULL,PDR     decimal(5,2)   DEFAULT NULL,DYP     decimal(5,1)   DEFAULT NULL,WTH     varchar(1)   DEFAULT NULL,DATETIME   datetime     DEFAULT NULL)2.数据从Oracle导出数据的SQL为(注意字段顺序):1.st_pptn_r表select stcd, to_char(tm,'yyyy-mm-dd hh24:mi:ss') tm, drp, intv, pdr, dyp, wth, to_char(datetime,'yyyy-mm-dd hh24:mi:ss') datetimefrom st_pptn_r;2.st_river_r表select stcd, to_char(tm,'yyyy-mm-dd hh24:mi:ss') tm , z, q, xsa, xsavv, xsmxv, flwchrcd, wptn, msqmt, msamt, msvmt from st_river_r;3.st_rsvr_r表select stcd, to_char(tm,'yyyy-mm-dd hh24:mi:ss') tm, rz, inq, w, blrz, otq, rwchrcd, rwptn, inqdr, msqmt from st_rsvr_r;最终的数据格式:1.st_pptn_r.csv文件tm,drp,intv,pdr,dyp,wth,datetime60948000,2014-05-02 04:00:00,0.5,1,,,7,60948000,2014-05-01 22:00:00,0,1,,,9,2.st_river_r.csv文件STCD,TM,Z,Q,XSA,XSAVV,XSMXV,FLWCHRCD,WPTN,MSQMT,MSAMT,MSVMT61906190,2014-05-02 00:00:00,336.77,,,,,,6,,,62208300,2014-05-03 10:00:00,24.25,,,,,,6,,,3.st_rsvr_r.csv文件STCD,TM,RZ,INQ,W,BLRZ,OTQ,RWCHRCD,RWPTN,INQDR,MSQMT61615000,2014-05-02 04:00:00,77.13,,56.966,,,,6,,61615810,2014-05-02 06:00:00,289.56,,71.289,,,,5,,3.命令操作  1.st_pptn_r表 > Load Data InFile 'D:/st_pptn_r.csv' Into Table ST_PPTN_R fields terminated by ',' lines terminated by '\r\n'ignore 1 lines (stcd, tm, @drp, @intv, @pdr, @dyp, @wth, @datetime)set drp=if(@drp='',NULL,@drp),intv=if(@intv='',NULL,@intv),pdr=if(@pdr='',NULL,@pdr),dyp=if(@dyp='',NULL,@dyp),wth=if(@wth='',NULL,@wth),datetime=if(@datetime='',NULL,str_to_date(@datetime, '%Y-%m-%d %H:%i:%s'));2.st_river_r表 > Load Data InFile 'D:/st_river_r.csv' Into Table ST_RIVER_R fields terminated by ',' lines terminated by '\r\n'ignore 1 lines (stcd,tm,@z,@q,@xsa,@xsavv,@xsmxv,@flwchrcd,@wptn,@msqmt,@msamt,@msvmt)set z=if(@z='',NULL,@z),q=if(@q='',NULL,@q),xsa=if(@xsa='',NULL,@xsa),xsavv=if(@xsavv='',NULL,@xsavv),xsmxv=if(@xsmxv='',NULL,@xsmxv),flwchrcd=if(@flwchrcd='',NULL,@flwchrcd),wptn=if(@wptn='',NULL,@wptn),msqmt=if(@msqmt='',NULL,@msqmt),msamt=if(@msamt='',NULL,@msamt),msvmt=if(@msvmt='',NULL,@msvmt);3.st_rsvr_r表 > Load Data InFile 'D:/st_rsvr_r.csv' Into Table ST_RSVR_R fields terminated by ',' lines terminated by '\r\n'ignore 1 lines (stcd, tm, @rz, @inq, @w, @blrz, @otq, @rwchrcd, @rwptn, @inqdr, @msqmt)set rz=if(@rz='',NULL,@rz),inq=if(@inq='',NULL,@inq),w=if(@w='',NULL,@w),blrz=if(@blrz='',NULL,@blrz),otq=if(@otq='',NULL,@otq),rwchrcd=if(@rwchrcd='',NULL,@rwchrcd),rwptn=if(@rwptn='',NULL,@rwptn),inqdr=if(@inqdr='',NULL,@inqdr),msqmt=if(@msqmt='',NULL,@msqmt);-----------------------------------------------------------------------------------------------------select * from test_infointo outfile '/tmp/test.csv'fields terminated by ',' optionally enclosed by '"' escaped by '"'lines terminated by '\r\n';load data infile '/tmp/test.csv'into table test_infofields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';------------------------------------------------------------------------------------------------------UPDATE st_pptn_r SET tm = STR_TO_DATE(CONCAT(DATE_FORMAT(tm,'%Y-%m-%d %H'),':00:00'),'%Y-%m-%d %H:%i:%s')DELETE FROM st_pptn_r WHERE tm IN(SELECT b.tm FROM (SELECT a.tm, COUNT(a.stcd) cou FROM st_pptn_r a GROUP BY a.tm) bWHERE b.cou<1000);DELETE FROM st_pptn_r WHERE tm IN(SELECT b.tm FROM (SELECT a.tm, COUNT(a.stcd) cou FROM st_pptn_r a WHERE a.drp<0.0001 GROUP BY a.tm) bWHERE b.cou>2800 );# DELETE FROM st_pptn_r WHERE tm < '2014-07-01 08:00:00' OR tm > '2014-08-01 08:00:00'DELETE FROM st_pptn_r WHERE tm<'2014-07-16 08:00:00' OR tm>'2014-07-26 08:00:00'------------------------------------------------------------------------------------------------------SELECT COUNT(*) cou, 'pptn' FROM st_pptn_rUNION SELECT COUNT(*) cou, 'river' FROM st_river_rUNION SELECT COUNT(*) cou, 'rsvr' FROM st_rsvr_rSELECT tm, stcd, MAX(drp) drp FROM st_pptn_r WHERE drp>10 GROUP BY tm, stcd ORDER BY drp DESCSELECT SUM(cou) cou, LEFT(tm, 11) tmm FROM(SELECT tm, COUNT(stcd) cou FROM st_pptn_r WHERE drp>10 GROUP BY tm) b GROUP BY tmm ORDER BY cou DESC

原标题:导入CSV格式的数据

关键词:

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

可能感兴趣文章

我的浏览记录