How to use dbSendQuery to import different CSV files into 1 SQL database table using R?

Question

Referring to this page,I am using following code trying to insert multiple csv files into SQL database table using R

#Step 1: Libraries
library(RPostgres)
library(data.table)
library(DBI)

#Step 2: make a dataframe of all files
file_names1 <- dir("C:/Users/Akanksha/Desktop/BSRN_TestRowsOnly/", full.names = TRUE, recursive = T) #where you have your files
my_data_frame <- do.call(rbind,lapply(file_names1,read.csv))


#Step 3: Establish Connection using RPostgres
dsn_database = "..."   # Specify the name of your Database
dsn_hostname = "localhost"  # Specify host name e.g.:"aws-us-east-1-portal.4.dblayer.com"
dsn_port = "5432"                # Specify your port number. e.g. 98939
dsn_uid = "..."         # Specify your username. e.g. "admin"
dsn_pwd = "..."        # Specify your password. e.g. "xxx"

tryCatch({
  drv <- dbDriver("Postgres")
  print("Connecting to Database…")
  connec <- dbConnect(drv, 
                      dbname = dsn_database,
                      host = dsn_hostname, 
                      port = dsn_port,
                      user = dsn_uid, 
                      password = dsn_pwd)
  print("Database Connected!")
},
error=function(cond) {
  print("Unable to connect to Database.")
})

#Step 4: Run and Test Queries using RPostgres
dbSendQuery(connec, "DROP TABLE IF EXISTS Population")  #Table name is Population

#Step 5: Importing
RPostgres::dbSendQuery(
  connec, 
  "INSERT INTO mytable (Population) 'my_data_frame';",
)

It returns an error:

Error: Failed to prepare query: ERROR:  syntax error at or near "'my_data_frame'"
LINE 1: INSERT INTO mytable (Population) 'my_data_frame';
                               ^
In addition: Warning message:
In result_create(conn@ptr, statement, immediate) :
  Closing open result set, cancelling previous query

I have tried different ways but no success. I am really stuck with this task, Could anyone please help me in resolving the present code or to suggest a new code to copy paste different csv files to 1 SQL table using R. R and SQL both are new things for me.

You're loading data.table but not using it. You're using the base R read.csv. Instead of my_data_frame <- do.call(rbind,lapply(file_names1,read.csv)) you could do my_data_frame <-rbindlist(lapply(file_names1, fread)) which should be much faster. Since data.tables are also data.frames you don't have to change anything else. — Dean MacGregor
– Dean MacGregor, Commented Jul 19, 2023 at 11:24

r2evans · Accepted Answer · 2023-07-18 10:29:41Z

1

The remote SQL server has no visibility or awareness of your local working environment, so passing a string literal 'my_data_frame' will not work. (It would be a huge security problem if that were the case.) Pass it the data.

Note: your SQL statements are fine by themselves but inconsistent drop table if exists Population suggests there is a table named Population, but insert into mytable (Population) ... suggests there is a table named mytable and a column named Population within that table. They are syntactically fine, and it is certainly feasible to have two tables mytable and Population where the former happens to have a column named Population, but ... it seems like it's likely to be a mistake.

Use dbAppendTable. Working example using mtcars and a local sqlite.

library(DBI)
sqlite <- dbConnect(RSQLite::SQLite())
dbWriteTable(sqlite, "mytable", mtcars[1,], create = TRUE)
dbAppendTable(sqlite, "mytable", mtcars[2:4,1:3])            # <-----
# [1] 3
dbGetQuery(sqlite, "select * from mytable")
#    mpg cyl disp  hp drat   wt  qsec vs am gear carb
# 1 21.0   6  160 110  3.9 2.62 16.46  0  1    4    4
# 2 21.0   6  160  NA   NA   NA    NA NA NA   NA   NA
# 3 22.8   4  108  NA   NA   NA    NA NA NA   NA   NA
# 4 21.4   6  258  NA   NA   NA    NA NA NA   NA   NA

answered Jul 18, 2023 at 10:29

r2evans

167k8 gold badges92 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael_Brun Over a year ago

Thank you very much for the information and explanation. It worked successfully

Collectives™ on Stack Overflow

How to use dbSendQuery to import different CSV files into 1 SQL database table using R?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related