0

I am trying to load a dataframe from r to sql and am having trouble getting the NAs to load to their equivalent NULL in sql. They are coming up as blank cells instead. Example data:

data.frame(name = c('Sara', 'Matt', 'Kyle', 'Steve', 'Maggie', NA, 'Alex', 'Morgan'),
student_id = c(123,124,125,126,127,128,129,130),
           score = c(78, 83, 91, NA, 88, 92, NA, 77))

Table schema: student_score with columns name (varchar), student_id (int), and score (int)

R code: 

load = "Insert into schema.student_score (name, student_id, score) values"  
data = list()  
for (i in seq(nrow(df))) {  
info = paste0("('", df$name[i], "','",  
df$student_id[i], "','",  
df$score[i], "')")  
data[[i]] = info  
}  
rows = do.call(rbind, data)  
values = paste(rows[,1], collapse = ',')  
send = paste0(load, values)  
dbSendQuery(conn, send)

and when they are loaded in sql it comes out

name    student_id    score
Sara    123           78
Matt    124           83
Kyle    125           91
Steve   126           
Maggie  127           88
        128           92
Alex    129           
Morgan  130           77

I want the blank values to be replaced by NULL

theofficefan12
  • 121
  • 7

1 Answers1

1

In your code NA gets translated as "NA". You need to replace all 'NA' in send to NULL. Simply add below code at the end -

send <- gsub("'NA'", "NULL", send)
send

"Insert into schema.student_score (name, student_id, score) values('Sara','123','78'),('Matt','124','83'),('Kyle','125','91'),('Steve','126',NULL),('Maggie','127','88'),(NULL,'128','92'),('Alex','129',NULL),('Morgan','130','77')"
Shree
  • 10,835
  • 1
  • 14
  • 36