[R] Reading large files

Gabor Grothendieck ggrothendieck at gmail.com
Sun Feb 7 02:37:45 CET 2010 

file= is the input data file. filter= is just a command string that
specifies a program to run (not a data file).

1. If Filename.tmp is the name of a temporary file (that it creates)
it runs a batch command similar to this:
      paste("cmd /c", filter, "<", file, ">", Filename.tmp)

2. Then it reads Filename.tmp into the database (which it creates for
you) and does this without involving R and

3. finally it reads the table in the database that was created into R,
as an R dataframe, and destroys the database.


On Sat, Feb 6, 2010 at 7:53 PM, Vadlamani, Satish {FLNA}
<SATISH.VADLAMANI at fritolay.com> wrote:
> Gabor:
> It did suppress the message now and I was able to load the data. Question.
>
> 1. test_df <- read.csv.sql(file="3wkoutstatfcst_small.dat", filter="perl parse_3wkout.pl")
>
> In the statement above, should the filename in file= and the file name that the perl script uses through the filter= command be the same? I would think not.  I would say that if filter= is passed to the statement, then the filename should be ignored. Is this how it works?
>
> Thanks.
> Satish
>
>
> -----Original Message-----
> From: Gabor Grothendieck [mailto:ggrothendieck at gmail.com]
> Sent: Saturday, February 06, 2010 4:58 PM
> To: Vadlamani, Satish {FLNA}
> Cc: r-help at r-project.org
> Subject: Re: [R] Reading large files
>
> I have uploaded another version which suppresses display of the error
> message but otherwise works the same.  Omitting the redundant
> arguments we have:
>
> ibrary(sqldf)
> # next line is only needed once per session to read in devel version
> source("http://sqldf.googlecode.com/svn/trunk/R/sqldf.R")
>
> test_df <- read.csv.sql(file="3wkoutstatfcst_small.dat", filter="perl
> parse_3wkout.pl")
>
>
> On Sat, Feb 6, 2010 at 5:48 PM, Vadlamani, Satish {FLNA}
> <SATISH.VADLAMANI at fritolay.com> wrote:
>> Gabor:
>> Please see the results below. Sourcing your new R script worked (although with the same error message). If I put eol="\n" option, it is adding a "\r" to the last column. I took out the eol option below. This is just some more feedback to you.
>>
>> I am thinking that I will just do an inline edit in Perl (that is create the csv file through Perl by overwriting the current file) and then use read.csv.sql without the filter= option. This seems to be more tried and tested. If you have any suggestions, please let me know. Thanks.
>> Satish
>>
>>
>> BEFORE SOURCING YOUR NEW R SCRIPT
>>> test_df <- read.csv.sql(file="3wkoutstatfcst_small.dat", sql = "select * from file", header = TRUE, sep = ",", filter="perl parse_3wkout.pl")
>> Error in readRegistry(key, maxdepth = 3) :
>>  Registry key 'SOFTWARE\R-core' not found
>>> test_df
>> Error: object 'test_df' not found
>>
>> AFTER SOURCING YOUR NEW R SCRIPT
>>> source("f:/dp_modeling_team/downloads/R/sqldf.R")
>>> test_df <- read.csv.sql(file="3wkoutstatfcst_small.dat", sql = "select * from file", header = TRUE, sep = ",", filter="perl parse_3wkout.pl")
>> Error in readRegistry(key, maxdepth = 3) :
>>  Registry key 'SOFTWARE\R-core' not found
>> In addition: Warning messages:
>> 1: closing unused connection 5 (3wkoutstatfcst_small.dat)
>> 2: closing unused connection 4 (3wkoutstatfcst_small.dat)
>> 3: closing unused connection 3 (3wkoutstatfcst_small.dat)
>>> test_df
>>   allgeo area1 zone dist ccust1 whse bindc ccust2 account area2 ccust3
>> 1       A     4    1   37     99 4925  4925     99      99     4     99
>> 2       A     4    1   37     99 4925  4925     99      99     4     99
>>
>> -----Original Message-----
>> From: Gabor Grothendieck [mailto:ggrothendieck at gmail.com]
>> Sent: Saturday, February 06, 2010 4:28 PM
>> To: Vadlamani, Satish {FLNA}
>> Cc: r-help at r-project.org
>> Subject: Re: [R] Reading large files
>>
>> The software attempts to read the registry and temporarily augment the
>> path in case you have Rtools installed so that the filter can access
>> all the tools that Rtools provides.  I am not sure why its failing on
>> your system but there is evidently some differences between systems
>> here and I have added some code to trap and bypass that portion in
>> case it fails.  I have added the new version to the svn repository so
>> try this:
>>
>> library(sqldf)
>> # overwrite with development version
>> source("http://sqldf.googlecode.com/svn/trunk/R/sqldf.R")
>> # your code to call read.csv.sql
>>
>>
>> On Sat, Feb 6, 2010 at 5:18 PM, Vadlamani, Satish {FLNA}
>> <SATISH.VADLAMANI at fritolay.com> wrote:
>>>
>>> Gabor:
>>> Here is the update. As you can see, I got the same error as below in 1.
>>>
>>> 1. Error
>>>  test_df <- read.csv.sql(file="out_small.txt", sql = "select * from file", header = TRUE, sep = ",", filter="perl parse_3wkout.pl", eol="\n")
>>> Error in readRegistry(key, maxdepth = 3) :
>>>  Registry key 'SOFTWARE\R-core' not found
>>>
>>> 2. But the loading of the bigger file was successful as you can see below. 857 MB, 333,250 rows, 227 columns. This is good.
>>>
>>> I will have to just do an inline edit in Perl and change the file to csv from within R and then call the read.csv.sql.
>>>
>>> If you have any suggestions to fix 1, I would like to try them.
>>>
>>>  system.time(test_df <- read.csv.sql(file="out.txt"))
>>>   user  system elapsed
>>>  192.53   15.50  213.68
>>> Warning message:
>>> closing unused connection 3 (out.txt)
>>>
>>> Thanks again.
>>>
>>> Satish
>>>
>>> -----Original Message-----
>>> From: Gabor Grothendieck [mailto:ggrothendieck at gmail.com]
>>> Sent: Saturday, February 06, 2010 3:02 PM
>>> To: Vadlamani, Satish {FLNA}
>>> Cc: r-help at r-project.org
>>> Subject: Re: [R] Reading large files
>>>
>>> Note that you can shorten #1 to read.csv.sql("out.txt") since your
>>> other arguments are the default values.
>>>
>>> For the second one, use read.csv.sql, eliminate the arguments that are
>>> defaults anyways (should not cause a problem but its error prone) and
>>> add an explicit eol= argument since SQLite can have problems with end
>>> of line in some cases.  Also test out your perl script separately from
>>> R first to ensure that it works:
>>>
>>> test_df <- read.csv.sql(file="3wkoutstatfcst_small.dat", filter="perl
>>> parse_3wkout.pl", eol = "\n")
>>>
>>> SQLite has some known problems with end of line so try it with and
>>> without the eol= argument just in case.  When I just made up the
>>> following gawk example I noticed that I did need to specify the eol=
>>> argument.
>>>
>>> Also I have added a complete example using gawk as Example 13c on the
>>> home page just now:
>>> http://code.google.com/p/sqldf/#Example_13._read.csv.sql_and_read.csv2.sql
>>>
>>>
>>> On Sat, Feb 6, 2010 at 3:52 PM, Vadlamani, Satish {FLNA}
>>> <SATISH.VADLAMANI at fritolay.com> wrote:
>>>> Gabor:
>>>>
>>>> I had success with the following.
>>>> 1. I created a csv file with a perl script called "out.txt". Then ran the following successfully
>>>> library("sqldf")
>>>> test_df <- read.csv.sql(file="out.txt", sql = "select * from file", header = TRUE, sep = ",", dbname = tempfile())
>>>>
>>>> 2. I did not have success with the following. Could you tell me what I may be doing wrong? I could paste the perl script if necessary. From the perl script, I am reading the file, creating the csv record and printing each record one by one and then exiting.
>>>>
>>>> Thanks.
>>>>
>>>> Not had success with below..
>>>> #test_df <- read.csv2.sql(file="3wkoutstatfcst_small.dat", sql = "select * from file", header = TRUE, sep = ",", filter="perl parse_3wkout.pl", dbname = tempfile())
>>>> test_df
>>>>
>>>> Error message below:
>>>> test_df <- read.csv2.sql(file="3wkoutstatfcst_small.dat", sql = "select * from file", header = TRUE, sep = ",", filter="perl parse_3wkout.pl", dbname = tempfile())
>>>> Error in readRegistry(key, maxdepth = 3) :
>>>>  Registry key 'SOFTWARE\R-core' not found
>>>> In addition: Warning messages:
>>>> 1: closing unused connection 14 (3wkoutstatfcst_small.dat)
>>>> 2: closing unused connection 13 (3wkoutstatfcst_small.dat)
>>>> 3: closing unused connection 11 (3wkoutstatfcst_small.dat)
>>>> 4: closing unused connection 9 (3wkoutstatfcst_small.dat)
>>>> 5: closing unused connection 3 (3wkoutstatfcst_small.dat)
>>>>> test_df <- read.csv2.sql(file="3wkoutstatfcst_small.dat", sql = "select * from file", header = TRUE, sep = ",", filter="perl parse_3wkout.pl", dbname = tempfile())
>>>> Error in readRegistry(key, maxdepth = 3) :
>>>>  Registry key 'SOFTWARE\R-core' not found
>>>>
>>>> -----Original Message-----
>>>> From: Gabor Grothendieck [mailto:ggrothendieck at gmail.com]
>>>> Sent: Saturday, February 06, 2010 12:14 PM
>>>> To: Vadlamani, Satish {FLNA}
>>>> Cc: r-help at r-project.org
>>>> Subject: Re: [R] Reading large files
>>>>
>>>> No.
>>>>
>>>> On Sat, Feb 6, 2010 at 1:01 PM, Vadlamani, Satish {FLNA}
>>>> <SATISH.VADLAMANI at fritolay.com> wrote:
>>>>> Gabor:
>>>>> Can I pass colClasses as a vector to read.csv.sql? Thanks.
>>>>> Satish
>>>>>
>>>>>
>>>>> -----Original Message-----
>>>>> From: Gabor Grothendieck [mailto:ggrothendieck at gmail.com]
>>>>> Sent: Saturday, February 06, 2010 9:41 AM
>>>>> To: Vadlamani, Satish {FLNA}
>>>>> Cc: r-help at r-project.org
>>>>> Subject: Re: [R] Reading large files
>>>>>
>>>>> Its just any Windows batch command string that filters stdin to
>>>>> stdout.  What the command consists of should not be important.   An
>>>>> invocation of perl that runs a perl script that filters stdin to
>>>>> stdout might look like this:
>>>>>  read.csv.sql("myfile.dat", filter = "perl myprog.pl")
>>>>>
>>>>> For an actual example see the source of read.csv2.sql which defaults
>>>>> to using a Windows vbscript program as a filter.
>>>>>
>>>>> On Sat, Feb 6, 2010 at 10:16 AM, Vadlamani, Satish {FLNA}
>>>>> <SATISH.VADLAMANI at fritolay.com> wrote:
>>>>>> Jim, Gabor:
>>>>>> Thanks so much for the suggestions where I can use read.csv.sql and embed Perl (or gawk). I just want to mention that I am running on Windows. I am going to read the documentation the filter argument and see if it can take a decent sized Perl script and then use its output as input.
>>>>>>
>>>>>> Suppose that I write a Perl script that parses this fwf file and creates a CSV file. Can I embed this within the read.csv.sql call? Or, can it only be a statement or something? If you know the answer, please let me know. Otherwise, I will try a few things and report back the results.
>>>>>>
>>>>>> Thanks again.
>>>>>> Saitsh
>>>>>>
>>>>>>
>>>>>> -----Original Message-----
>>>>>> From: jim holtman [mailto:jholtman at gmail.com]
>>>>>> Sent: Saturday, February 06, 2010 6:16 AM
>>>>>> To: Gabor Grothendieck
>>>>>> Cc: Vadlamani, Satish {FLNA}; r-help at r-project.org
>>>>>> Subject: Re: [R] Reading large files
>>>>>>
>>>>>> In perl the 'unpack' command makes it very easy to parse fixed fielded data.
>>>>>>
>>>>>> On Fri, Feb 5, 2010 at 9:09 PM, Gabor Grothendieck
>>>>>> <ggrothendieck at gmail.com> wrote:
>>>>>>> Note that the filter= argument on read.csv.sql can be used to pass the
>>>>>>> input through a filter written in perl, [g]awk or other language.
>>>>>>> For example: read.csv.sql(..., filter = "gawk -f myfilter.awk")
>>>>>>>
>>>>>>> gawk has the FIELDWIDTHS variable for automatically parsing fixed
>>>>>>> width fields, e.g.
>>>>>>> http://www.delorie.com/gnu/docs/gawk/gawk_44.html
>>>>>>> making this very easy but perl or whatever you are most used to would
>>>>>>> be fine too.
>>>>>>>
>>>>>>> On Fri, Feb 5, 2010 at 8:50 PM, Vadlamani, Satish {FLNA}
>>>>>>> <SATISH.VADLAMANI at fritolay.com> wrote:
>>>>>>>> Hi Gabor:
>>>>>>>> Thanks. My files are all in fixed width format. They are a lot of them. It would take me some effort to convert them to CSV. I guess this cannot be avoided? I can write some Perl scripts to convert fixed width format to CSV format and then start with your suggestion. Could you let me know your thoughts on the approach?
>>>>>>>> Satish
>>>>>>>>
>>>>>>>>
>>>>>>>> -----Original Message-----
>>>>>>>> From: Gabor Grothendieck [mailto:ggrothendieck at gmail.com]
>>>>>>>> Sent: Friday, February 05, 2010 5:16 PM
>>>>>>>> To: Vadlamani, Satish {FLNA}
>>>>>>>> Cc: r-help at r-project.org
>>>>>>>> Subject: Re: [R] Reading large files
>>>>>>>>
>>>>>>>> If your problem is just how long it takes to load the file into R try
>>>>>>>> read.csv.sql in the sqldf package.  A single read.csv.sql call can
>>>>>>>> create an SQLite database and table layout for you, read the file into
>>>>>>>> the database (without going through R so R can't slow this down),
>>>>>>>> extract all or a portion into R based on the sql argument you give it
>>>>>>>> and then remove the database.  See the examples on the home page:
>>>>>>>> http://code.google.com/p/sqldf/#Example_13._read.csv.sql_and_read.csv2.sql
>>>>>>>>
>>>>>>>> On Fri, Feb 5, 2010 at 2:11 PM, Satish Vadlamani
>>>>>>>> <SATISH.VADLAMANI at fritolay.com> wrote:
>>>>>>>>>
>>>>>>>>> Matthew:
>>>>>>>>> If it is going to help, here is the explanation. I have an end state in
>>>>>>>>> mind. It is given below under "End State" header. In order to get there, I
>>>>>>>>> need to start somewhere right? I started with a 850 MB file and could not
>>>>>>>>> load in what I think is reasonable time (I waited for an hour).
>>>>>>>>>
>>>>>>>>> There are references to 64 bit. How will that help? It is a 4GB RAM machine
>>>>>>>>> and there is no paging activity when loading the 850 MB file.
>>>>>>>>>
>>>>>>>>> I have seen other threads on the same types of questions. I did not see any
>>>>>>>>> clear cut answers or errors that I could have been making in the process. If
>>>>>>>>> I am missing something, please let me know. Thanks.
>>>>>>>>> Satish
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> End State
>>>>>>>>>> Satish wrote: "at one time I will need to load say 15GB into R"
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> -----
>>>>>>>>> Satish Vadlamani
>>>>>>>>> --
>>>>>>>>> View this message in context: http://n4.nabble.com/Reading-large-files-tp1469691p1470667.html
>>>>>>>>> Sent from the R help mailing list archive at Nabble.com.
>>>>>>>>>
>>>>>>>>> ______________________________________________
>>>>>>>>> R-help at r-project.org mailing list
>>>>>>>>> https://stat.ethz.ch/mailman/listinfo/r-help
>>>>>>>>> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>>>>>>>>> and provide commented, minimal, self-contained, reproducible code.
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>> ______________________________________________
>>>>>>> R-help at r-project.org mailing list
>>>>>>> https://stat.ethz.ch/mailman/listinfo/r-help
>>>>>>> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>>>>>>> and provide commented, minimal, self-contained, reproducible code.
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Jim Holtman
>>>>>> Cincinnati, OH
>>>>>> +1 513 646 9390
>>>>>>
>>>>>> What is the problem that you are trying to solve?
>>>>>>
>>>>>
>>>>
>>>
>>
>

More information about the R-help mailing list