#library(readxl, quietly=TRUE)
#df <- data.frame(read_excel("d:\\data.xlsx",col_names = TRUE),stringsAsFactors=FALSE)
#如果是文本文件或者CSV,建议使用data.table,好处是快,以及避免read_csv的各种编码坑
#connect MySQL
library(RMySQL)
con <- dbConnect(MySQL(),user="user", password="pwd",dbname="db"
, host="mysql.yourhost.com" ,port=3306)
library(stringi)
toutf8<-function(x)
{
if (is.factor(x))
{
x<-as.character(x)
}
if(is.character(x)){
if(!all( stri_enc_isutf8(x))){
x<-stri_encode(x, "", "UTF-8")
}
Encoding(x)<-"utf8"
}
x
}
#if R in Windows, convert columns and data to UTF-8
names(df)<-toutf8(names(df))
#下面的代码是偷懒,针对data.frame没有写,有兴趣的可以自己动手,使用data.table的好处还是快
library(data.table)
df<-as.data.table(df)
#if R in Windows, convert columns and data to UTF-8
if(Sys.info()[["sysname"]]=="Windows"){
names(df)<-toutf8(names(df))
df<-df[ , lapply(.SD, toutf8)]
dbGetQuery(con, "set NAMES utf8")
}
dbWriteTable(con, "data",df)