3

This is probably a very stupid question for SQL stalwarts, but I just want one SQL command.

Details,

I am using a data analysis tool called R, this tool uses ODBC to read data from XLS. I am now trying to read data from an XLS file. The ODBC tool in R accepts SQL commands.

Question,

Can someone give me an SQL command that will read data from an XLS file's - Specified sheet - Specified column [by name] - Specified row [Specified just by Row Index]

Thanks ...

Alphaneo
  • 12,079
  • 22
  • 71
  • 89

3 Answers3

5

With the query below you can read the data from row 61 of cloumn A, & G is supposed to read all columns till G.

SELECT * FROM [Sheet1$a61:G]
Rashid
  • 51
  • 1
  • 1
3

Once you have set the connection to the file, you can use following statement:

select [columnname] from [sheetname$] where [columnname] = 'somevalue'

Not sure about the row index thing. But you can make use of where clause if each row in the file has serial number or any such unique value.

danish
  • 5,550
  • 2
  • 25
  • 28
3

Here's a sample query:

SELECT [sheet1$.col1], [sheet1$.col2], [sheet2$.col1] 
FROM   [sheet1$], [sheet2$] 
WHERE  [sheet1$.col1] = [sheet2$.col2]

This assumes an excel document with 2 sheets (sheet1 and sheet2). Each sheet has 2 columns, with the first row as headers (col1 and col2 in each sheet).

Here's the complete code:

> library(RODBC)
> conn <- odbcConnectExcel('c:/tmp/foo.xls')
> query <- "select [sheet1$.col1], [sheet1$.col2], [sheet2$.col1] 
            from [sheet1$], [sheet2$] 
            where [sheet1$.col1] = [sheet2$.col2];"
> result <- sqlQuery(conn, query)
> odbcClose(conn)
> result
  col1 col2 col1.1
1    1    3      5
2    2    4      6
3    3    5      7

I've never found a way to deal with row numbers. I just create an extra column and fill down sequentially. Not sure if that works for you.

ars
  • 120,335
  • 23
  • 147
  • 134