Didnt work.... getting unused argument error. On Sun, Sep 4, 2016 at 4:47 PM, Jim Lemon <drjimlemon at gmail.com> wrote:> I suppose you could try quote=TRUE > > Jim > > > On Sun, Sep 4, 2016 at 8:13 PM, Christofer Bogaso > <bogaso.christofer at gmail.com> wrote: >> Thanks Jim. But my data is like that and I have to live with that. Any >> idea on workaround. Thanks, >> >> On Sun, Sep 4, 2016 at 3:40 PM, Jim Lemon <drjimlemon at gmail.com> wrote: >>> Hi Christofer, >>> You have embedded commas in your data structure. This is guaranteed to >>> mess up a CSV read. >>> >>> Jim >>> >>> >>> On Sun, Sep 4, 2016 at 5:54 PM, Christofer Bogaso >>> <bogaso.christofer at gmail.com> wrote: >>>> Hi again, >>>> >>>> I was trying to read a subset of Data from a CSV file using below code >>>> as example : >>>> >>>> library(sqldf) >>>> >>>> Dat = structure(list(`col 1` = c(133261L, 133261L, 133262L, 133262L >>>> ), `col 2` = structure(1:4, .Label = c("aaa1", "aaa2", "bbb1, bbb", >>>> "bbb3, bbb"), class = "factor"), `col 3` = c(10.59, 10.56, 10.59, >>>> 10.56), `col 4` = c(10.59, 10.56, 10.59, 10.56), `col 5` = c(10.59, >>>> 10.56, 10.59, 10.56), `col 6` = structure(c(1L, 2L, 1L, 2L), .Label >>>> c("04-Jul-16", >>>> "05-Jul-16"), class = "factor")), .Names = c("col 1", "col 2", >>>> "col 3", "col 4", "col 5", "col 6"), class = "data.frame", row.names = c(NA, >>>> -4L)) >>>> Dat >>>> >>>> write.csv(Dat, "Dat.csv", quote = FALSE, row.names = FALSE) >>>> >>>> ReadName = '133261' >>>> read.csv.sql("Dat.csv", sql = "select * from file where 'col 1' = ReadName") >>>> >>>> Loading required package: tcltk >>>> Error in scan(file, what, nmax, sep, dec, quote, skip, nlines, na.strings, : >>>> line 1 did not have 7 elements >>>> >>>> This code generates above Error. Could you please help me with a >>>> pointer where something went wrong? My actual CSV file is quite huge >>>> so I cant read it as whole. However basic structure of my original >>>> file is similar as above "Dat" >>>> >>>> Thanks for your time. >>>> >>>> ______________________________________________ >>>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see >>>> 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.
Shouldn't get that with write.csv. Jim On Sun, Sep 4, 2016 at 9:29 PM, Christofer Bogaso <bogaso.christofer at gmail.com> wrote:> Didnt work.... getting unused argument error. > > On Sun, Sep 4, 2016 at 4:47 PM, Jim Lemon <drjimlemon at gmail.com> wrote: >> I suppose you could try quote=TRUE >> >> Jim >> >> >> On Sun, Sep 4, 2016 at 8:13 PM, Christofer Bogaso >> <bogaso.christofer at gmail.com> wrote: >>> Thanks Jim. But my data is like that and I have to live with that. Any >>> idea on workaround. Thanks, >>> >>> On Sun, Sep 4, 2016 at 3:40 PM, Jim Lemon <drjimlemon at gmail.com> wrote: >>>> Hi Christofer, >>>> You have embedded commas in your data structure. This is guaranteed to >>>> mess up a CSV read. >>>> >>>> Jim >>>> >>>> >>>> On Sun, Sep 4, 2016 at 5:54 PM, Christofer Bogaso >>>> <bogaso.christofer at gmail.com> wrote: >>>>> Hi again, >>>>> >>>>> I was trying to read a subset of Data from a CSV file using below code >>>>> as example : >>>>> >>>>> library(sqldf) >>>>> >>>>> Dat = structure(list(`col 1` = c(133261L, 133261L, 133262L, 133262L >>>>> ), `col 2` = structure(1:4, .Label = c("aaa1", "aaa2", "bbb1, bbb", >>>>> "bbb3, bbb"), class = "factor"), `col 3` = c(10.59, 10.56, 10.59, >>>>> 10.56), `col 4` = c(10.59, 10.56, 10.59, 10.56), `col 5` = c(10.59, >>>>> 10.56, 10.59, 10.56), `col 6` = structure(c(1L, 2L, 1L, 2L), .Label >>>>> c("04-Jul-16", >>>>> "05-Jul-16"), class = "factor")), .Names = c("col 1", "col 2", >>>>> "col 3", "col 4", "col 5", "col 6"), class = "data.frame", row.names = c(NA, >>>>> -4L)) >>>>> Dat >>>>> >>>>> write.csv(Dat, "Dat.csv", quote = FALSE, row.names = FALSE) >>>>> >>>>> ReadName = '133261' >>>>> read.csv.sql("Dat.csv", sql = "select * from file where 'col 1' = ReadName") >>>>> >>>>> Loading required package: tcltk >>>>> Error in scan(file, what, nmax, sep, dec, quote, skip, nlines, na.strings, : >>>>> line 1 did not have 7 elements >>>>> >>>>> This code generates above Error. Could you please help me with a >>>>> pointer where something went wrong? My actual CSV file is quite huge >>>>> so I cant read it as whole. However basic structure of my original >>>>> file is similar as above "Dat" >>>>> >>>>> Thanks for your time. >>>>> >>>>> ______________________________________________ >>>>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see >>>>> 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.
Try changing the separator to ; # write.table() instead of write.csv()> write.table(Dat, "Dat.csv", quote = FALSE, sep=";", row.names = FALSE) > readLines("Dat.csv")[1] "col 1;col 2;col 3;col 4;col 5;col 6" [2] "133261;aaa1;10.59;10.59;10.59;04-Jul-16" [3] "133261;aaa2;10.56;10.56;10.56;05-Jul-16" [4] "133262;bbb1, bbb;10.59;10.59;10.59;04-Jul-16" [5] "133262;bbb3, bbb;10.56;10.56;10.56;05-Jul-16" # Note read.csv2.sql() not read.csv.sql()> read.csv2.sql("Dat.csv", sql = "select * from file where 'col 1' = ReadName", header=TRUE)Error in sqliteSendQuery(con, statement, bind.data) : error in statement: no such column: ReadName In addition: Warning message: closing unused connection 3 (Dat.csv) ------------------------------------- David L Carlson Department of Anthropology Texas A&M University College Station, TX 77840-4352 -----Original Message----- From: R-help [mailto:r-help-bounces at r-project.org] On Behalf Of Jim Lemon Sent: Sunday, September 4, 2016 6:39 AM To: Christofer Bogaso Cc: r-help Subject: Re: [R] Error in reading subset of data from CSV file Shouldn't get that with write.csv. Jim On Sun, Sep 4, 2016 at 9:29 PM, Christofer Bogaso <bogaso.christofer at gmail.com> wrote:> Didnt work.... getting unused argument error. > > On Sun, Sep 4, 2016 at 4:47 PM, Jim Lemon <drjimlemon at gmail.com> wrote: >> I suppose you could try quote=TRUE >> >> Jim >> >> >> On Sun, Sep 4, 2016 at 8:13 PM, Christofer Bogaso >> <bogaso.christofer at gmail.com> wrote: >>> Thanks Jim. But my data is like that and I have to live with that. Any >>> idea on workaround. Thanks, >>> >>> On Sun, Sep 4, 2016 at 3:40 PM, Jim Lemon <drjimlemon at gmail.com> wrote: >>>> Hi Christofer, >>>> You have embedded commas in your data structure. This is guaranteed to >>>> mess up a CSV read. >>>> >>>> Jim >>>> >>>> >>>> On Sun, Sep 4, 2016 at 5:54 PM, Christofer Bogaso >>>> <bogaso.christofer at gmail.com> wrote: >>>>> Hi again, >>>>> >>>>> I was trying to read a subset of Data from a CSV file using below code >>>>> as example : >>>>> >>>>> library(sqldf) >>>>> >>>>> Dat = structure(list(`col 1` = c(133261L, 133261L, 133262L, 133262L >>>>> ), `col 2` = structure(1:4, .Label = c("aaa1", "aaa2", "bbb1, bbb", >>>>> "bbb3, bbb"), class = "factor"), `col 3` = c(10.59, 10.56, 10.59, >>>>> 10.56), `col 4` = c(10.59, 10.56, 10.59, 10.56), `col 5` = c(10.59, >>>>> 10.56, 10.59, 10.56), `col 6` = structure(c(1L, 2L, 1L, 2L), .Label >>>>> c("04-Jul-16", >>>>> "05-Jul-16"), class = "factor")), .Names = c("col 1", "col 2", >>>>> "col 3", "col 4", "col 5", "col 6"), class = "data.frame", row.names = c(NA, >>>>> -4L)) >>>>> Dat >>>>> >>>>> write.csv(Dat, "Dat.csv", quote = FALSE, row.names = FALSE) >>>>> >>>>> ReadName = '133261' >>>>> read.csv.sql("Dat.csv", sql = "select * from file where 'col 1' = ReadName") >>>>> >>>>> Loading required package: tcltk >>>>> Error in scan(file, what, nmax, sep, dec, quote, skip, nlines, na.strings, : >>>>> line 1 did not have 7 elements >>>>> >>>>> This code generates above Error. Could you please help me with a >>>>> pointer where something went wrong? My actual CSV file is quite huge >>>>> so I cant read it as whole. However basic structure of my original >>>>> file is similar as above "Dat" >>>>> >>>>> Thanks for your time. >>>>> >>>>> ______________________________________________ >>>>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see >>>>> 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 -- To UNSUBSCRIBE and more, see 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.
Florian Schwendinger
2016-Sep-05 17:58 UTC
[R] Error in reading subset of data from CSV file
There are several issues,
1. I think
ReadName = '133261'
read.csv.sql("Dat.csv", sql = "select * from file where 'col
1' = ReadName")
should be replaced by something like this
ReadName = '133261'
sql_cmd <- sprintf("select * from file where col_1='%s'",
ReadName)
sql_cmd
read.csv.sql("Dat.csv", sql = sql_cmd, sep="|")
2. I wouldn't use spaces in the column names.
3. Since you have in column 2 also your separator, you cloud switch the
separator to "|" or ";" if possible.
colnames(Dat) <- gsub("\\s+", "_", colnames(Dat))
colnames(Dat)
write.table(Dat, "Dat.csv", quote = FALSE, row.names = FALSE,
sep="|")
ReadName = '133261'
sql_cmd <- sprintf("select * from file where col_1='%s'",
ReadName)
sql_cmd
read.csv.sql("Dat.csv", sql = sql_cmd, sep="|")
So the following works at my pc, but if you cant change the separator
things will get more difficult.
colnames(Dat) <- gsub("\\s+", "_", colnames(Dat))
colnames(Dat)
write.table(Dat, "Dat.csv", quote = FALSE, row.names = FALSE,
sep="|")
ReadName = '133261'
sql_cmd <- sprintf("select * from file where col_1='%s'",
ReadName)
sql_cmd
read.csv.sql("Dat.csv", sql = sql_cmd, sep="|")
Best,
Florian
On 2016-09-04 13:29, Christofer Bogaso wrote:> Didnt work.... getting unused argument error.
>
> On Sun, Sep 4, 2016 at 4:47 PM, Jim Lemon <drjimlemon at gmail.com>
wrote:
>> I suppose you could try quote=TRUE
>>
>> Jim
>>
>>
>> On Sun, Sep 4, 2016 at 8:13 PM, Christofer Bogaso
>> <bogaso.christofer at gmail.com> wrote:
>>> Thanks Jim. But my data is like that and I have to live with that.
Any
>>> idea on workaround. Thanks,
>>>
>>> On Sun, Sep 4, 2016 at 3:40 PM, Jim Lemon <drjimlemon at
gmail.com> wrote:
>>>> Hi Christofer,
>>>> You have embedded commas in your data structure. This is
guaranteed to
>>>> mess up a CSV read.
>>>>
>>>> Jim
>>>>
>>>>
>>>> On Sun, Sep 4, 2016 at 5:54 PM, Christofer Bogaso
>>>> <bogaso.christofer at gmail.com> wrote:
>>>>> Hi again,
>>>>>
>>>>> I was trying to read a subset of Data from a CSV file using
below code
>>>>> as example :
>>>>>
>>>>> library(sqldf)
>>>>>
>>>>> Dat = structure(list(`col 1` = c(133261L, 133261L, 133262L,
133262L
>>>>> ), `col 2` = structure(1:4, .Label = c("aaa1",
"aaa2", "bbb1, bbb",
>>>>> "bbb3, bbb"), class = "factor"), `col
3` = c(10.59, 10.56, 10.59,
>>>>> 10.56), `col 4` = c(10.59, 10.56, 10.59, 10.56), `col 5` =
c(10.59,
>>>>> 10.56, 10.59, 10.56), `col 6` = structure(c(1L, 2L, 1L,
2L), .Label >>>>> c("04-Jul-16",
>>>>> "05-Jul-16"), class = "factor")),
.Names = c("col 1", "col 2",
>>>>> "col 3", "col 4", "col 5",
"col 6"), class = "data.frame", row.names = c(NA,
>>>>> -4L))
>>>>> Dat
>>>>>
>>>>> write.csv(Dat, "Dat.csv", quote = FALSE,
row.names = FALSE)
>>>>>
>>>>> ReadName = '133261'
>>>>> read.csv.sql("Dat.csv", sql = "select * from
file where 'col 1' = ReadName")
>>>>>
>>>>> Loading required package: tcltk
>>>>> Error in scan(file, what, nmax, sep, dec, quote, skip,
nlines, na.strings, :
>>>>> line 1 did not have 7 elements
>>>>>
>>>>> This code generates above Error. Could you please help me
with a
>>>>> pointer where something went wrong? My actual CSV file is
quite huge
>>>>> so I cant read it as whole. However basic structure of my
original
>>>>> file is similar as above "Dat"
>>>>>
>>>>> Thanks for your time.
>>>>>
>>>>> ______________________________________________
>>>>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and
more, see
>>>>> 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 -- To UNSUBSCRIBE and more, see
> 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.
>