3

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?

Paul Hiemstra
  • 59,984
  • 12
  • 142
  • 149
Prasun Velayudhan
  • 537
  • 1
  • 7
  • 19

3 Answers3

5

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' )
Vincent Zoonekynd
  • 31,893
  • 5
  • 69
  • 78
  • 4
    Added a year later. Note that the latest version of RSQLite no longer replaces dots with underscores. – G. Grothendieck Dec 30 '14 at 15:34
  • 2
    @G.Grothendieck how to handle such a situation then? – user4786271 May 20 '15 at 14:53
  • 8
    Put the name in double quotes: `sqldf( 'SELECT "Company.1" FROM test' )` . This also works: `sqldf( 'SELECT [Company.1] FROM test' )` – G. Grothendieck May 20 '15 at 14:59
  • 1
    I think [Company.1] is safer than "Company.1" because if you misspell "Company.1" you will not get a column not found error, it will just be interpreted as a string (and in SQL "stringname1" < 5 is a valid comparison). OTOH if you misspell [Company.1] you will get a column not found error. – Rasmus Larsen Mar 01 '17 at 13:04
1

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")
talat
  • 68,970
  • 21
  • 126
  • 157
Hazem HASAN
  • 1,598
  • 2
  • 21
  • 38
1

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.

Community
  • 1
  • 1
user4786271
  • 1,555
  • 13
  • 18