edbQuery.RODBC_Excel: Send and retrieve a query in an MS Excel database (referenced...

In easyrodbcexcel: Simple database interface for RODBC Excel.

Description

Send and retrieve a query in a SQLIte database (referenced by 'edb').

Usage

## S3 method for class 'RODBC_Excel'
edbQuery(edb, statement, formatCol = NULL, 


    testFiles = TRUE, readOnly = TRUE, verbose = FALSE, 


    ...)

Arguments

edb	An object of class 'edb', such as returned by edb.
statement	Single character string. SQL statement / SQL query to be passed to link[RODBC]{sqlQuery}.
formatCol	If not NULL, a named list of functions to be applied to certain columns after the data has been extracted from the database. The name of each list item gives the column to process, and the value of each item gives the function that must be applied. For instance formatCol = list("DATE"=as.Date) will apply the function as.Date to the column "DATE".
testFiles	Single logical. Should the function test for the presence (file.exist()) of the needed files in the folder before trying to fetch information from the database?
readOnly	Single logical. If TRUE, the Excel file will be considered as read only. See odbcConnectExcel or odbcConnectExcel2007.
verbose	Single logical. If TRUE, information on what is done are output on screen.
...	Additional parameters to be passed to link[RODBC]{sqlQuery}.

Value

The function returns the requested table.

Author(s)

Julien MOEYS <[e-mail]>

See Also

edb, link[RODBC]{sqlQuery}.

Examples

library( "easyrodbcexcel" ) 











# System check. Ingnore this or set 'testODBC' to TRUE 


testODBC <- (class(try(odbcDataSources())) != "try-error") & # Check (R)ODBC 


            (Sys.info()[[ "sysname" ]] == "Windows") &       # Only MS Windows


            (.Machine$"sizeof.pointer" == 4)                 # Only 32-bits 











### Windows only:


if( testODBC ){ 


    ### Make a copy of Excel example file:


    #   (a database of soil profile description) 


    file.copy( 


        from = system.file( "soils.xls", package = "easyrodbcexcel" ), 


        to   = "soils.xls" 


    )   


    


    # soils.xls is now in your working directory.


    


    


    


    library( "RODBC" ) # Because soils.db is a SQLite database...


    


    


    


    ### Describe the database (NB: this is not a connection)


    myDb <- edb( dbType = "RODBC_Excel", dbName = "soils.xls" ) 


    


    


    


    ### Use the database:





    ## Read data in a table in the database


    


    # Retrieve a table (data.frame style subsetting):


    myDb[ "WRB_SOIL_GROUP" ]


    


    # Same operation, but with edbRead()


    edbRead( edb = myDb, tableName = "WRB_SOIL_GROUP" ) 


    


    # Retrieve part of a table (with row constrains)


    myDb[ "WRB_SOIL_GROUP", list("ABBREV" = c("AC","CR","PL")), 


        verbose = TRUE ] 


    


    # Same operation, but with edbQuery() 


    edbQuery( edb = myDb, statement = 


        "SELECT * FROM [WRB_SOIL_GROUP] WHERE [ABBREV] IN ('AC','CR','PL')" ) 





    ### Clean-up


    file.remove( "soils.xls" ) 


    


    


    


    ### Access 2007 ---------------------------------------------


    file.copy( 


        from = system.file( "soils.xlsx", package = "easyrodbcexcel" ), 


        to   = "soils.xlsx" 


    )   


    


    # soils.db is now in your working directory.


    


    


    


    library( "RODBC" ) # Because soils.db is a SQLite database...


    


    


    


    ### Describe the database (NB: this is not a connection)


    myDb2 <- edb( dbType = "RODBC_Excel", dbName = "soils.xlsx", 


        excelVersion = 2007 ) 


    


    


    


    ### Use the database:





    ## Read data in a table in the database


    


    # Retrieve a table (data.frame style subsetting):


    myDb2[ "WRB_SOIL_GROUP" ]


    


    # Same operation, but with edbRead()


    edbRead( edb = myDb2, tableName = "WRB_SOIL_GROUP" ) 


    


    # Retrieve part of a table (with row constrains)


    myDb2[ "WRB_SOIL_GROUP", list("ABBREV" = c("AC","CR","PL")), 


        verbose = TRUE ] 


    


    # Same operation, but with edbQuery() 


    edbQuery( edb = myDb2, statement = 


        "SELECT * FROM [WRB_SOIL_GROUP] WHERE [ABBREV] IN ('AC','CR','PL')" ) 


    


    ### Clean-up


    file.remove( "soils.xlsx" ) 


}   #

easyrodbcexcel documentation built on May 2, 2019, 6:12 p.m.