Displaying 20 results from an estimated 11000 matches similar to: "Advantages of using SQLite for data import in comparison to csv files"
2009 Feb 20
2
importing data to SQLite database with sqldf
Hi all,
I am attempting to learn SQL through sqldf...
One task I am particularly interested in is merging separate
(presumably large) files into a single table without loading these
files into R as an intermediate step (by loading them into SQLite and
merging them there).
Taking a step back, I've considered these alternatives:
1) I know if I use straight SQLite commands I might use the
2011 Jul 14
1
SQldf with sqlite and H2
SQldf with sqlite and H2
I have a large csv file (about 2GB) and wanted to import the file into R and do some filtering and analysis. Came across sqldf ( a great idea and product) and was trying to play around to see what would be the best method of doing this. csv file is comma delimited with some columns having comma inside the quoation like this "John, Doe".
I tried this first
2010 Nov 15
5
How to Read a Large CSV into a Database with R
Hi, I'm working in R 2.11.1 x64 on Windows x86_64-pc-mingw32. I'm trying to
insert a very large CSV file into a SQLite database. I'm pretty new to
working with databases in R, so I apologize if I'm overlooking something
obvious here.
I'm trying to work with the American Community Survey data, which is two
1.3GB csv files. I have enough RAM to read one of them into memory,
2012 Jan 03
1
sqldf and not converting integers to floating point in SQLite
Hi,
I have following 2 tables:
Table 1:
POSTAL | VALUE
1000|49
1010|100
1020|50
Table 2:
INSEE | POSTAL
A|1000
B|1000
C|1010
D|1020
I would like to convert this to the following:
INSEE | VALUE_SPREAD
A|24.5
B|24.5
C|100
D|50
I can achieve this with a nested SQL query (through counting the
number of POSTAL that belong to any given INSEE, and diving the value
of the postal in that INSEE by
2009 Mar 30
1
Importing csv file with character values into sqlite3 and subsequent problem in R / RSQLite
Dear all,
I'm trying to import a csv file into sqlite3 and from there into
R. Everything looks fine exepct that R outputs the character values in
an odd fashion: they are shown as "\"CHARACTER\"" instead of
"CHARACTER", but only if I show the character variable as a
vector. Does someone know why this happens? Below is a sample
code. The first part is written in
2017 Sep 11
0
Case statement in sqldf
2018-03-3 in your code should be 2018-03-31.
The line
then'201415'
needs to be fixed.
When posting please provide minimal self-contained examples. There was
no input provided and library statements not relevant to the posted
code were included.
Fixing the invalid date and bad line, getting rid of those library
statements that are unnecessary and providing some test input, it
works
2012 Nov 06
0
How to read all table in r using dbreadTable, autimatically read in whenever I save new table in SQLite db
HI,
I got the problem.
I use the following query to read table individually, how can I read all tables autimatically rather than typing "week42" "week33" in dbReadTable?
library(RSQLite)
library(sqldf)
drv <- dbDriver("SQLite")
con<-dbConnect(drv, "sqlite.db")
dbListTables(con)
#dbRemoveTable(con, "week39")
dbDisconnect(con)
2009 Jul 30
2
Question regarding sqldf
Hello,
I am having a problem using sqldf. I'm trying to choose a subset of
observations from a data set based on the date and maximum value of a
variable by date.
Here is the code I am using:
test<-sqldf("select distinct * from bextuse group by sdate having
bext=max(bext)",method="raw");
The result I get back is a data frame with 0 rows and 0 columns. I have
tried
2012 Nov 06
1
how Can make function for selecting the products
HI.
I make this code:
getdata<-function('a','b','c' ,'d','e','f'){
drv <- dbDriver("SQLite")
con<-dbConnect(drv, "sqlite.db")
lt<-dbListTables(con)
myf<-data.frame(NULL)
for (i in 1:length(lt))
{
myfile<-dbReadTable(con,lt[i])
myfile1<-myfile[-c(14:44)]
myfile1$MODEL<-gsub(" ",
2014 Dec 09
3
Optimizar paste0()?
Gracias, Javier.
Los datos "d" corresponden un archivo de texto de ~1.92GB. Voy a explorar
la posibilidad con sqldf, aunque confieso que mi conocimiento de SQL es
bastante limitado.
Saludos cordiales,
Jorge.-
2014-12-09 23:50 GMT+11:00 "Marcuzzi, Javier Rubén" <
javier.ruben.marcuzzi en gmail.com>:
> Estimado Jorge Velez
>
> ¿Que pasa si usa sqldf que
2013 May 16
1
connect to local mySQL database
Hi,
I would like to access a local mysql database.
In python using the sqlite3 library it is quite straightforward. I just have to pass the path to the sqlite-file;
sqlite3.connect('.../xy.sqlite')
Is there something similar in R (or specifically in the RMySQL package)?
It seems like I have to use the MySQL function in the RMySQL package in order to ...
"initializes a MySQL
2007 Aug 01
1
New R package sqldf
sqldf is an R package for running SQL select
statements on one or more R data frames. It is
optimized for convenience making it useful
for ad hoc queries against R data frames.
Given an SQL select statement whose tables
are the names of R data frames it:
- sets up the database (by default it transparently
sets up an in memory SQLite database using RSQLite;
however, MySQL via RMySQL, can be
2007 Aug 01
1
New R package sqldf
sqldf is an R package for running SQL select
statements on one or more R data frames. It is
optimized for convenience making it useful
for ad hoc queries against R data frames.
Given an SQL select statement whose tables
are the names of R data frames it:
- sets up the database (by default it transparently
sets up an in memory SQLite database using RSQLite;
however, MySQL via RMySQL, can be
2011 Sep 29
1
sqldf syntax, selecting rows, and skipping
I am using the example in this post:
https://stat.ethz.ch/pipermail/r-help/2010-October/257204.html
# create a file
write.table(iris,"iris.csv",row.names=FALSE,sep=",",quote=FALSE)
# this does not work
# has the syntax changed or is there a mistake in my usage?
# the line from the post above is:
# read.csv.sql("myfile.csv, sql = "select * from file 2000,
2012 Sep 05
2
using sqldf's read.csv.sql to read a file with "NA" for missing
I'm trying to use sqdf's function read.csv.sql to read CSV files in which the missing values are represented by NA's.
Plain old read.csv works fine on these files, but they are rather large and I'd like to filter using sql-like statements.
However, even if I specify field.types correctly and nrows=-1, it still turns the columns with NA's into chars or 0.
I'm trying to make
2009 Mar 23
3
read in large data file (tsv) with inline filter?
I have a very large tab-delimited file, too big to store in memory via
readLines() or read.delim(). Turns out I only need a few hundred of those
lines to be read in. If it were not so large, I could read the entire file
in and "grep" the lines I need. For such a large file; many calls to
read.delim() with incrementing "skip" and "nrows" parameters, followed by
grep()
2012 Feb 02
9
sqldf for Very Large Tab Delimited Files
Hi All,
I have a very (very) large tab-delimited text file without headers. There
are only 8 columns and millions of rows. I want to make numerous pieces of
this file by sub-setting it for individual stations. Station is given as in
the first column. I am trying to learn and use sqldf package for this but am
stuck in a couple of places.
To simulate my requirement, I have taken iris dataset as an
2020 Oct 08
1
Lahman Baseball Data Using R DBI Package
This is really a feature of SQL, not R. SQL requires that you double quote
column names that start with numbers, include spaces, etc., or that are SQL
key words. E.g.,
> d <- data.frame(Order=c("sit","stay","heel"),
Where=c("here","there","there"), From=c("me","me","you"))
>
2011 Nov 08
3
Reading a specific column of a csv file in a loop
Dear all:
I have two larges files with 2000 columns. For each file I am
performing a loop to extract the "i"th element of each file and create
a data frame with both "i"th elements in order to perform further
analysis. I am not extracting all the "i"th elements but only certain
which I am indicating on a vector called "d".
See an example of my code below
2018 Apr 18
1
merge two data frame based on equal and unequal comparisons
Dear R users,
I need to merge two data frames based on both equal and unequal comparisons. The "sqldf" package used to work well , but today, I cannot resolve the following error by reinstallation of the sqldf package. Can anyone suggest a different way to perform this kind of merge function?
Thank you,
Ding
> DMRlog2pbde47DMS <- sqldf("select * from DMR_log2pbde47 as a