ODBC Connection error for merging files in R

Question

I am trying to read excel files using odbcConnectExcel2007 function in R from RODBC package. While reading individual file, it's working. But when I am trying to run using for loop function, it's throwing following error

3 stop(sQuote(tablename), ": table not found on channel") 
2 odbcTableExists(channel, sqtable) 
1 sqlFetch(conn1, sqlTables(conn1)$TABLE_NAME[1]) 

Below is the code:-

file_list <- list.files("./Raw Data")
file_list

for (i in 1:length(file_list)){

     conn1 = odbcConnectExcel2007(paste0("./Raw Data/",file_list[i])) # open a   connection to the Excel file
     sqlTables(conn1)$TABLE_NAME
     data=sqlFetch(conn1, sqlTables(conn1)$TABLE_NAME[1])
     close(conn1)

     data <- data[,c("Branch","Custome","Category","Sub Category","SKU"
                      "Weight","Order Type","Invoice Date")]


     if(i==1) alldata=data else{
     alldata = rbind(alldata,data)
    }

}

I would appreciate any kind of help. Thanks in advance.

Answer 1

I think it's getting messed up with the table name having quotes returned from the sqlTables(conn1)$TABLE_NAME object. Try manipulating the table name by removing the quotes. Something like this:

table <- sqlTables(conn1)$TABLE_NAME
  table <- noquote(table)
  table <- gsub("\'", "", table)

And then just do:

data=sqlFetch(conn1, table)