R: Insert csv-file into database using RJDBC

Question

As RJDBC is the only package I have been able to make work on Ubuntu, I am trying to use it to INSERT a CSV-file into a database.

I can make the following work:

# Connecting to database
library(RJDBC)
drv <- JDBC('com.microsoft.sqlserver.jdbc.SQLServerDriver', 'drivers/sqljdbc42.jar', identifier.quote="'")
connection_string <- "jdbc:sqlserver://blablaserver;databaseName=testdatabase"
ch <- dbConnect(drv, connection_string, "username", "password")

# Inserting a row
dbSendQuery(ch, "INSERT INTO cpr_esben.CPR000_Startrecord (SORTFELT_10,OPGAVENR,PRODDTO,PRODDTOFORRIG,opretdato) VALUES ('TEST', 123, '2012-01-01', '2012-01-01', '2012-01-01')")

The insert works. Next I try to make an INSERT of a CSV-file with the same data, that is separated by the default "tab" and I am working on windows.

# Creating csv
df <- data.frame(matrix(c('TEST', 123, '2012-01-01', '2012-01-01', '2012-01-01'), nrow = 1), stringsAsFactors = F)
colnames(df) <- c("SORTFELT_10","OPGAVENR","PRODDTO","PRODDTOFORRIG","opretdato")
class(df$SORTFELT_10) <- "character"
class(df$OPGAVENR) <- "character"
class(df$PRODDTO) <- "character"
class(df$PRODDTOFORRIG) <- "character"
class(df$opretdato) <- "character"
write.table(df, file = "test.csv", col.names = FALSE, quote = FALSE)

# Inserting CSV to database
dbSendQuery(ch, "INSERT cpr_esben.CPR000_Startrecord FROM 'test.csv'")

Unable to retrieve JDBC result set for INSERT cpr_esben.CPR000_Startrecord FROM 'test.csv' (Incorrect syntax near the keyword 'FROM'.)

Do you have any suggestions to what I am doing wrong, when trying to insert the csv-file? I do not get the Incorrect syntax near the keyword 'FROM' error?

score 2 · Accepted Answer · answered Nov 08 '18 at 22:53

2

What if you create a statement from your data? Something like:

# Data from your example
df <- data.frame(matrix(c('TEST', 123, '2012-01-01', '2012-01-01', '2012-01-01'), nrow = 1), stringsAsFactors = F)
colnames(df) <- c("SORTFELT_10","OPGAVENR","PRODDTO","PRODDTOFORRIG","opretdato")
class(df$SORTFELT_10) <- "character"
class(df$OPGAVENR) <- "character"
class(df$PRODDTO) <- "character"
class(df$PRODDTOFORRIG) <- "character"
class(df$opretdato) <- "character"

# Formatting rows to insert into SQL statement
rows <- apply(df, 1, function(x){paste0('"', x, '"', collapse = ', ')})
rows <- paste0('(', rows, ')')

# SQL statement
statement <- paste0(
  "INSERT INTO cpr_esben.CPR000_Startrecord (", 
  paste0(colnames(df), collapse = ', '), 
  ')',
  ' VALUES ',
  paste0(rows, collapse = ', ')
)

dbSendQuery(ch, statement)

This should work for any number of rows in your df

answered Nov 08 '18 at 22:53

feebarscevicius

584
3
7

1

This is a very ugly solution if you have large dataframes, but I did end up doing something similar. I ended up writing the CSV-file to a file, and then used BCP to upload the data. – Esben Eickhardt Nov 09 '18 at 09:09
If no better solutions show up, then I will just give you the solution, as it does do the trick. – Esben Eickhardt Nov 09 '18 at 09:10
Yes, this is ugly, but I believe this is the only way to go using the `dbSendQuery` function. `dbWriteTable` does something similar under the hood, but you don't see the ugly statement it generates xD – feebarscevicius Nov 09 '18 at 11:23

score 0 · Answer 2 · answered Nov 04 '18 at 21:17

0

RJDBC is built on DBI, which has many useful functions to do tasks like this. What you want is dbWriteTable. Syntax would be:

dbWriteTable(ch, 'cpr_esben.CPR000_Startrecord', df, append = TRUE)

and would replace your write.table line.

I am not that familiar with RJDBC specifically, but I think the issue with your sendQuery is that you are referencing test.csv inside your SQL statement, which does not locate the file that you created with write.table as the scope of that SQL statement is not in your working directory.

answered Nov 04 '18 at 21:17

Chris

6,302
1
27
54

My issue when using the "write table" approach is that I need permission to create a new table in the database (even if I choose "append=TRUE"). The reason why I chose to refer to my csv-file in the SQL-query, was that I was inspired by this post https://stackoverflow.com/questions/19190744/how-to-quickly-export-data-from-r-to-sql-server – Esben Eickhardt Nov 05 '18 at 08:54
1

If you don't have write access nothing will work, what permissions do you have exactly ? – moodymudskipper Nov 05 '18 at 10:37
Agreed with Moody. If you want to replicate the example you linked, you also need to reflect the full path structure that they use. – Chris Nov 05 '18 at 15:59
I have write access, but I do not have access to create new tables. – Esben Eickhardt Nov 06 '18 at 12:37
How is `DBI::dbWriteTable(..., append=TRUE)` creating a new table? Typically, `create=TRUE,append=TRUE` is directly contradicting and will error. – r2evans Nov 10 '18 at 05:35

score 0 · Answer 3 · answered May 14 '19 at 12:15

Have you tried loading the file directly to the database as below.

library(RJDBC)
drv <- JDBC("connections")      
conn <- dbConnect(drv,"...")

query = "LOAD DATA INFILE 'test.csv' INTO TABLE test"
dbSendUpdate(conn, query)

You can also try to include other statements in the end like delimiter for column like "|" for .txt file and "," for csv file.

R: Insert csv-file into database using RJDBC

3 Answers3