sqldf (version 0.4-5)

read.csv.sql: Read File Filtered by SQL

Description

Read a file into R filtering it with an sql statement. Only the filtered portion is processed by R so that files larger than R can otherwise handle can be accommodated.

Usage

read.csv.sql(file, sql = "select * from file", header = TRUE, sep = ",", row.names, eol, skip, filter, nrows, field.types, comment.char, dbname = tempfile(), drv = "SQLite", ...)
read.csv2.sql(file, sql = "select * from file", header = TRUE, sep = ";", row.names, eol, skip, filter, nrows, field.types, comment.char, dbname = tempfile(), drv = "SQLite", ...)

Arguments

file

A file path or a URL (beginning with http:// or ftp://). If the filter argument is used and no file is to be input to the filter then file can be omitted, NULL, NA or ""

sql

character string holding an SQL statement. The table representing the file should be referred to as file.

header

As in read.csv.

sep

As in read.csv.

row.names

As in read.csv.

eol

Character which ends line.

skip

Skip indicated number of lines in input file.

filter

If specified, this should be a shell/batch command that the input file is piped through. For read.csv2.sql it is by default the following on non-Windows systems: tr , .. This translates all commas in the file to dots. On Window

nrows

Number of rows used to determine column types. It defaults to 50. Using -1 causes it to use all rows for determining column types. This argument is rarely needed.

field.types

A list whose names are the column names and whose contents are the SQLite types (not the R class names) of the columns. Specifying these types improves how fast it takes. Unless speed is very important this argument is not normally used.

comment.char

If specified this character and anything following it on any line of the input will be ignored.

dbname

As in sqldf except that the default is tempfile(). Specifying NULL will put the database in memory which may improve speed but will limit the size of the database by the available memory.

drv

This argument is ignored. Currently the only database SQLite supported by read.csv.sql and read.csv2.sql is SQLite. Note that the H2 database has a builtin SQL function, CSVREAD, which can be used in place of

...

Passed to sqldf.

Value

• If the sql statement is a select statement then a data frame is returned.

Details

Reads the indicated file into an sql database creating the database if it does not already exist. Then it applies the sql statement returning the result as a data frame. If the database did not exist prior to this statement it is removed. Note that it uses facilities of SQLite to read the file which are intended for speed and therefore not as flexible as in R. For example, it does not recognize quoted fields as special but will regard the quotes as part of the field. See the sqldf help for more information. read.csv2.sql is like read.csv.sql except the default sep is ";" and the default filter translates all commas in the file to decimal points (i.e. to dots). On Windows, if the filter argument is used and if Rtools is detected in the registry then the Rtools bin directory is added to the search path facilitating use of those tools without explicitly setting any the path. A gawk script csv.awk is included in the sqldf package and the AWKPATH is set so that csv.awk is on it allowing it to be referenced without a pathname. The csv.awk script supports the input variables isep, osep, quote, escape, newline and trim. The first two default to comma, the next two default to double quote and the last one defaults to 1. The input file is assumed to use isep as the field separator and the output uses osep. Quoted fields use the indicated quote character and quotes within quoted fields must be escaped using the indicated escape character. Newlines may appear within quoted fields unless newline is the empty string. White space is trimmed from the beginning and end of fields but this may be turned off by setting trim=0. See the example in the examples section.

Examples

# might need to change eol
write.csv(iris, "iris.csv", quote = FALSE, row.names = FALSE)
iris2 <- read.csv.sql("iris.csv", 
	sql = "select * from file where Sepal.Length > 5", eol = "\n")

write.csv(iris, "irisq.csv", quote = TRUE, row.names = FALSE)
csv.awk <- system.file("csv.awk", package = "sqldf")
irisq <- read.csv.sql("irisq.csv", sep = ";", eol = "\n",
    filter = paste("gawk -v OFS=; -f", csv.awk))

cat('A,B
"Joe, Jr.","Mac ""The Knife"""
', file = "temp.csv")
temp.out <- read.csv.sql("temp.csv", sep = ";", eol = "\n",
      filter = "gawk -v osep=; -f csv.awk")