thr3ads.net - R help - [R] Rmysql - dbWritetable [Oct 2010]

If this information is useful, please help other people find it:
Share via:

Nilza BARROS

2010-Oct-13 00:47 UTC

[R] Rmysql - dbWritetable

Dear Rusers,

I am trying to feed my database with data from a file.

But since my file (2010101000.txt) there`s no headers I am facing problem
because the
result data.frame is not separated my columns.

How could I set variables names for each columns in *dbWriteTable*? I have
tried the command below but I don`t know how to fill the *field.type*option.


dbWriteTable(con, "b20101010", "./2010101000.txt", overwrite
= T, sep "\t",row.names=c("V01", "V02",
"V03", "V04", "V05", "V06","
V07","V08",
"V09", "V10", "V11", "V12",
"V13", "V14"),field.type=????)

===Below what I have been used. I need to know how to set the column names
to xx (my dataframe).
> drv=dbDriver("MySQL")
> con <-
dbConnect(drv,dbname='buoy',user="xx",password="xxx")
> dbWriteTable(con, "b20101010", "./2010101000.txt",
overwrite = T, sep "\t", head = F)
[1] TRUE>
> *xx*<- dbGetQuery(con, "SELECT * FROM b20101010  ")
> xx
*  V1
*1 00859 2010 10 10 00 50  -41.51   6.83  -9999   1016  -9999 -9999.0
-9999.0 -9999.0
2 00859 2010 10 09 22 50  -41.51   6.81  -9999   1015  -9999 -9999.0 -9999.0
-9999.0
3 00707 2010 10 09 23 00  -34.04 -37.63  -9999   1025  -9999 -9999.0 -9999.0
-9999.0
4 00005 2010 10 09 23 00  -34.69 -18.89  -9999   1017    207     7.2   297.1
-9999.0
5 00859 2010 10 09 23 50  -41.51   6.81  -9999   1015  -9999 -9999.0 -9999.0
-9999.0
6 00005 2010 10 09 23 00  -34.69 -18.89  -9999   1017    207     7.2   297.1
-9999.0
7 00617 2010 10 09 23 20  -18.43 -23.09  -9999   1023  -9999 -9999.0 -9999.0
-9999.0
8 00707 2010 10 10 00 00  -34.03 -37.62  -9999   1025  -9999 -9999.0 -9999.0
-9999.0
9 00707 2010 10 10 01 00  -34.03 -37.62  -9999   1025  -9999 -9999.0 -9999.0
-9999.0
*> length(xx)
[1] 1
*
-- 
Abraço,
Nilza Barros

	[[alternative HTML version deleted]]

stephen sefick

2010-Oct-13 00:57 UTC

head link

[R] Rmysql - dbWritetable

I don't have much experience with SQL, but I suspect that it is
something like CHARACTER or NUMERIC... maybe.

On Tue, Oct 12, 2010 at 7:47 PM, Nilza BARROS <nilzabarros at gmail.com>
wrote:> Dear Rusers,
>
> I am trying to feed my database with data from a file.
>
> But since my file (2010101000.txt) there`s no headers I am facing problem
> because the
> result data.frame is not separated my columns.
>
> How could I set variables names for each columns in *dbWriteTable*? I have
> tried the command below but I don`t know how to fill the
*field.type*option.
>
>
> dbWriteTable(con, "b20101010", "./2010101000.txt",
overwrite = T, sep > "\t",row.names=c("V01",
"V02", "V03", "V04", "V05",
"V06"," V07","V08",
> "V09", "V10", "V11", "V12",
"V13", "V14"),field.type=????)
>
> ===Below what I have been used. I need to know how to set the column names
> to xx (my dataframe).
>
>> drv=dbDriver("MySQL")
>> con <-
dbConnect(drv,dbname='buoy',user="xx",password="xxx")
>> dbWriteTable(con, "b20101010", "./2010101000.txt",
overwrite = T, sep > "\t", head = F)
> [1] TRUE
>>
>> *xx*<- dbGetQuery(con, "SELECT * FROM b20101010 ?")
>> xx
>
> * ?V1
> *1 00859 2010 10 10 00 50 ?-41.51 ? 6.83 ?-9999 ? 1016 ?-9999 -9999.0
> -9999.0 -9999.0
> 2 00859 2010 10 09 22 50 ?-41.51 ? 6.81 ?-9999 ? 1015 ?-9999 -9999.0
-9999.0
> -9999.0
> 3 00707 2010 10 09 23 00 ?-34.04 -37.63 ?-9999 ? 1025 ?-9999 -9999.0
-9999.0
> -9999.0
> 4 00005 2010 10 09 23 00 ?-34.69 -18.89 ?-9999 ? 1017 ? ?207 ? ? 7.2 ?
297.1
> -9999.0
> 5 00859 2010 10 09 23 50 ?-41.51 ? 6.81 ?-9999 ? 1015 ?-9999 -9999.0
-9999.0
> -9999.0
> 6 00005 2010 10 09 23 00 ?-34.69 -18.89 ?-9999 ? 1017 ? ?207 ? ? 7.2 ?
297.1
> -9999.0
> 7 00617 2010 10 09 23 20 ?-18.43 -23.09 ?-9999 ? 1023 ?-9999 -9999.0
-9999.0
> -9999.0
> 8 00707 2010 10 10 00 00 ?-34.03 -37.62 ?-9999 ? 1025 ?-9999 -9999.0
-9999.0
> -9999.0
> 9 00707 2010 10 10 01 00 ?-34.03 -37.62 ?-9999 ? 1025 ?-9999 -9999.0
-9999.0
> -9999.0
> *> length(xx)
> [1] 1
> *
> --
> Abra?o,
> Nilza Barros
>
> ? ? ? ?[[alternative HTML version deleted]]
>
>
> ______________________________________________
> 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.
>
>


-- 
Stephen Sefick
____________________________________
| Auburn University? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
| Department of Biological Sciences? ? ? ? ?? |
| 331 Funchess Hall? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
| Auburn, Alabama? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
| 36849? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
|___________________________________|
| sas0025 at auburn.edu? ? ? ? ? ? ? ? ? ? ? ? ? ?? |
| http://www.auburn.edu/~sas0025? ? ? ? ? ?? |
|___________________________________|

Let's not spend our time and resources thinking about things that are
so little or so large that all they really do for us is puff us up and
make us feel like gods.? We are mammals, and have not exhausted the
annoying little problems of being mammals.

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? -K. Mullis

"A big computer, a complex algorithm and a long time does not equal
science."

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? -Robert Gentleman

Nilza BARROS

2010-Oct-16 23:31 UTC

head link

[R] [R-sig-DB] Rmysql - dbWritetable

Dear Spencer,

Thank you for your help. I read your email  and that`s the reason why I sent
an email to r-sig-db as you suggested.
 But I have just realized that you suggested the command below too. I`ve
tried it and that`s fine. I was waiting to solve my problem to reply the
email to r-help group. That`s what I am doing now! Thank you!!

x2010 <- read.table('2010101000.txt')
dbWriteTable(con, "b20101010", x2010, overwrite = T)
xx<- dbGetQuery(con, "SELECT * FROM b20101010  ")
*> dim(xx)
[1] 338  17
*
  row_names    V1   V2 V3 V4 V5 V6     V7     V8   V9   V10  V11  V12 V13
V14
1         1 82824 2010 10 10  0  0 -63.91  -8.76  102 -9999 1002 1012  50
10.3
2         2  5125 2010 10 10  0  0 -46.90 -19.60 1000 -9999  903 1015 117
1.8
3         3  9223 2010 10 10  0  0 -54.60 -16.40  284 -9999  980 1012 224
2.3
4         4     8 2010 10 10  0  0 -48.10 -15.90 1000 -9999  890  999 290
2.6
5         5  5133 2010 10 10  0  0 -43.80 -20.60 1000 -9999  902 1016 120
3.8
6         6  5147 2010 10 10  0  0 -41.50 -17.90  475 -9999  965 1020  65
4.8
    V15   V16
1 297.1 295.4
2 293.4 282.1
3 300.9 280.8
4 295.8 288.9
5 288.1 283.6
6 294.4 289.2


On Sat, Oct 16, 2010 at 12:16 AM, Spencer Graves <
spencer.graves@structuremonitoring.com> wrote:
> Hi, Nilza:
>
>
>       Did you receive my reply to your earlier post to R-Help, which looks
> to me identical to this (
> https://stat.ethz.ch/pipermail/r-help/2010-October/256012.html)?  I'd
like
> to know if you tried what I suggested there.  If you did, what were the
> results?  If not, why not?
>
>
>       PLEASE do read the posting guide "
>
www.R-project.org/posting-guide.html<http://www.r-project.org/posting-guide.html>"
> and provide commented, minimal, self-contained, reproducible code.  Your
> example is not completely self contained.  It might help if you include
> "sessionInfo()".  In my previous reply, there are help pages
documenting
> "dbWriteTable" in 10 different packages.  I've not used
"dbWriteTable" very
> much, but it might help to know which of these 10 packages you are using.
>
>
>
>       I've found that following the posting guide often helps me find a
> solution to my problem.  If I still can't solve the problem, this often
> helps me pose the question in a way more likely to generate an answer that
> actually helps me solve the problem.
>
>
>       I'm sorry if this is not any more helpful than my previous reply.
>       Spencer Graves
>
>
> On 10/15/2010 7:39 PM, Nilza BARROS wrote:
>
>   Dear Rusers,
>
> I am trying to feed my database with data from a file.
>
> But since my file (2010101000.txt) there`s no headers I am facing problem
> because the
> result data.frame is not separated my columns.
>
> How could I set variables names for each columns in *dbWriteTable*? I have
> tried the command below but I don`t know how to fill the
*field.type*option.
>
>
> dbWriteTable(con, "b20101010", "./2010101000.txt",
overwrite = T, sep > "\t",row.names=c("V01",
"V02", "V03", "V04", "V05",
"V06"," V07","V08",
> "V09", "V10", "V11", "V12",
"V13", "V14"),field.type=????)
>
> ===Below what I have been used. I need to know how to set the column names
> to xx (my dataframe).
>
>
> drv=dbDriver("MySQL")
> con <-
dbConnect(drv,dbname='buoy',user="xx",password="xxx")
> dbWriteTable(con, "b20101010", "./2010101000.txt",
overwrite = T, sep >
> "\t", head = F)
> [1] TRUE
>
> *xx*<- dbGetQuery(con, "SELECT * FROM b20101010  ")
> xx
>
> *  V1
> *1 00859 2010 10 10 00 50  -41.51   6.83  -9999   1016  -9999 -9999.0
> -9999.0 -9999.0
> 2 00859 2010 10 09 22 50  -41.51   6.81  -9999   1015  -9999 -9999.0
-9999.0
> -9999.0
> 3 00707 2010 10 09 23 00  -34.04 -37.63  -9999   1025  -9999 -9999.0
-9999.0
> -9999.0
> 4 00005 2010 10 09 23 00  -34.69 -18.89  -9999   1017    207     7.2  
297.1
> -9999.0
> 5 00859 2010 10 09 23 50  -41.51   6.81  -9999   1015  -9999 -9999.0
-9999.0
> -9999.0
> 6 00005 2010 10 09 23 00  -34.69 -18.89  -9999   1017    207     7.2  
297.1
> -9999.0
> 7 00617 2010 10 09 23 20  -18.43 -23.09  -9999   1023  -9999 -9999.0
-9999.0
> -9999.0
> 8 00707 2010 10 10 00 00  -34.03 -37.62  -9999   1025  -9999 -9999.0
-9999.0
> -9999.0
> 9 00707 2010 10 10 01 00  -34.03 -37.62  -9999   1025  -9999 -9999.0
-9999.0
> -9999.0
> *> length(xx)
> [1] 1
> *
>
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest GroupR-sig-DB@stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>
>
>

-- 
Abraço,
Nilza Barros

	[[alternative HTML version deleted]]

Maybe Matching Threads

Search for more possibly parallel threads

R help - Oct 2010 - Rmysql - dbWritetable

[R] Rmysql - dbWritetable

[R] Rmysql - dbWritetable

[R] [R-sig-DB] Rmysql - dbWritetable

Maybe Matching Threads