sql command for reading a particular sheet, column
Solution 1
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.
Solution 2
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]
Solution 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.
Alphaneo
An electrical engineer, who is a programmer by profession
Updated on June 05, 2022Comments
-
Alphaneo almost 2 years
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 ...