R数据导入—充值记录

最新推荐文章于 2024-03-22 11:16:35 发布

Tina_DT

最新推荐文章于 2024-03-22 11:16:35 发布

阅读量328

点赞数

分类专栏： R代码

本文链接：https://blog.csdn.net/Tina_DT/article/details/79322197

版权

R代码专栏收录该内容

3 篇文章 0 订阅

订阅专栏

rm(list=ls())
workDir="E:/ddqc/ddqc";
rceDir=paste(workDir,"/refill",sep="");
setwd(workDir)
options(scipen=50)
library(rJava)
library(xlsxjars)
library(xlsx)
library(DBI)
library(RJDBC)
f_InsertValues<-function(total,conn){
total$浜ゆ槗鏃堕棿=substr(total$浜ゆ槗鏃堕棿,1,19)

for(h in 1:nrow(total)){
insertVal=""
for(i in 1:length(total)){
if(cols[2,i]!=0){
if(grepl("鏃堕棿",cols[1,i])) {#"to_date('','yyyy-mm-dd hh24:mi:si')"}
insertVal<-paste(insertVal,",to_date('",total[h,i],"','yyyy-MM-dd HH24:mi:ss')",sep="")
}
else{
insertVal<-paste(insertVal,",'",total[h,i],"'",sep="")
}
}
}
insertVal=substring(insertVal,2)
insertSql=paste(insertCol,insertVal,")",sep=" ")
#msg<-tryCatch({dbSendUpdate(conn,insertSql)},error=function(e){insertSql})
#return ( paste ( msg , collapse = "\n" ) )
dbSendUpdate(conn,insertSql)
}
}

first_category_name = list.files(rceDir)
dir = paste(rceDir,"/",first_category_name,sep="")
n = length(dir)
total=read.xlsx(file=dir[1],sheetIndex=1,encoding='UTF-8',startRow=1,colClasses= c(rep.int('character', 50)))
cols<-rbind(colnames(total),rep(0,length(colnames(total))))
for(i in 1:length(colnames(total))){
if(cols[1,i]=="鍗″彿") cols[2,i]="CHARGING_CARD"
if(cols[1,i]=="鎵嬫満鍙?") cols[2,i]="CELL_PHONE_NUMBER"
if(cols[1,i]=="璁㈠崟娴佹按鍙?") cols[2,i]="trade_num"
if(cols[1,i]=="涓氬姟娴佹按鍙?") cols[2,i]="service_number"
if(cols[1,i]=="绗笁鏂规敮浠樻祦姘村彿") cols[2,i]="third_pay_number"
if(cols[1,i]=="浜ゆ槗鍗曚綅") cols[2,i]="trade_company"
if(cols[1,i]=="浜ゆ槗绫诲瀷") cols[2,i]="trade_type"
if(grepl("鍏呭€奸噾棰?",cols[1,i])) cols[2,i]="refill_money"
if(grepl("鏀粯鏂瑰紡",cols[1,i])) cols[2,i]="PAY_TYPE"
if(cols[1,i]=="浜ゆ槗鏃堕棿") cols[2,i]="refill_time"
if(cols[1,i]=="鍏呭€肩姸鎬?") cols[2,i]="refill_state"
if(cols[1,i]=="鏄惁鍐叉") cols[2,i]="zheng_chong"
if(cols[1,i]=="鍏呭€间汉鍛?") cols[2,i]="refill_clerk"
if(cols[1,i]=="鏄惁鏃ョ粨") cols[2,i]="if_payfilish"
if(cols[1,i]=="澶囨敞") cols[2,i]="remark"

}
insertCol=""
for(i in 1:length(total)){
if(cols[2,i]!=0){
insertCol<-paste(insertCol,cols[2,i],sep=",")
}
}
insertCol=paste(paste("insert into REFILL_REPORT_yys(",substring(insertCol,2)),") values(")
insertCol
drv<-JDBC("oracle.jdbc.driver.OracleDriver",paste(workDir,"/ojdbc14.jar",sep=""), identifier.quote="/")
conn <- dbConnect(drv, "jdbc:oracle:thin:@192.168.1.102:1521:orcl","ddqc","Ddqc1234")
f_InsertValues(total,conn)
##########
for(i in 2:n){ #瀵逛簬姣忎釜涓€绾х洰褰?(鏂囦欢澶?)
total<-read.xlsx(file=dir[i],sheetIndex=1,encoding='UTF-8',startRow=1,colClasses= c(rep.int('character', 50)))
#total<-read.xlsx(file=dir[i],sheetIndex=1,encoding='UTF-8',colClasses= c(rep.int('character', 50)))
f_InsertValues(total,conn)
}
dbDisconnect(conn)
#write.csv(total,"F:/zjwork/ddqc/merge.csv",row.names = F,col.names= T)
#write.xlsx(total,"F:/zjwork/ddqc/merge.xlsx",row.names = F,col.names= T)#鍗曠嫭淇濆瓨鍦ㄦ瘡涓枃浠跺す涓?