CODEWITHSUNDEEP
rsqldf
Prasun Velayudhan
Prasun Velayudhan

Reputation: 547

How to handle column names not supported by sqldf in R

I've a data frame where some of the column names are of the format . format. For ex: Company.1 when i'm using that column in a sqldf function it throws an error

data=sqldf(select Company.1 from test)
Error in sqliteExecStatement(con, statement, bind.data) : 
RS-DBI driver: (error in statement: near ".1": syntax error)

Any workaround so that i can use the column name as it is?

Upvotes: 3

Views: 7438

Answers (3)

user4786271
user4786271

Reputation: 1565

The solution for the latest update of sqldf is answered here

We only need to write the SQL statement between single quotes, and the column names including dots between double quotes or backticks/backquotes interchangeably.

Upvotes: 1

Hazem HASAN
Hazem HASAN

Reputation: 1658

This problem is about the . in your column name, if you change it to Company_1 it works:

data = sqldf("select Company_1 from test")

Upvotes: 1

Vincent Zoonekynd
Vincent Zoonekynd

Reputation: 32351

The dot has another meaning in SQL (e.g., separating table name from column name) and is replaced by an underscore before sending the data to SQLite.

library(sqldf)
test <- data.frame( "Company.1" = 1:10 )
sqldf( 'SELECT Company_1 FROM test' )

Upvotes: 6

Related Questions