[R] Problem getting characters into a dataframe

From: Chris Strachan <chris.strac_at_googlemail.com>
Date: Tue, 18 May 2010 21:47:48 +0100


Hi,

I am having a problem getting character (string) variables into a dataframe. I seem to have no problem with numeric fields, but character fields are appearing as a "1".

In my problem I need to retrieve some information from an SQL database using RODBC one line at a time and then update the respective row.

I have tried converting the object returned from the query into various classes but to no avail.

Here is a selection of the code:

I am updating table2 in this case which only has two rows. The query is executed for the row and stored in y. The contents of y are then meant to update the various columns.

> for (x in c(1:2)) { y <-
sqlQuery(EFXchannel,paste(getData,total2$TRAN_NUMBER[x]))

+ y <- as.list(y)

+ total2$TRN_DT[x] <- as.numeric(y[1]) +ISOdatetime(1970,1,1,1,0,0)

+ total2$CARDNUM[x] <- as.character(y[2])

+ total2$TRN_AMT[x] <- as.double(y[3])

+ total2$MER_CNTY_CD[x] <- as.numeric(y[4])

+ total2$SIC_CD[x] <- as.numeric(y[5])

+ total2$MER_NM[x] <- as.character(y[6])

+ print(y[6])

+ total2$TRN_POS_ENT_CD[x] <- as.character(y[7])

+ total2$RULE_HIT[x] <- as.numeric(y[8])}

                                     MER_NM

1 XXXXX RENT A CAR

                                     MER_NM

1 HOTEL XXXXX
> total2

   TRAN_NUMBER TRN_DT TRN_AMT MER_CNTY_CD SIC_CD MER_NM TRN_POS_ENT_CD RULE_HIT

1   720001861 1272544032     600         724   7512      1              
1        1

2   720001377 1272493155      86         250   7011      1              
1        1

>

As you can see MER_NM and TRN_POS_ENT_CD do not update.

For information this is the type of information returned by the query

> z <- sqlQuery(EFXchannel,paste(getData,720001377))

> z

                TRN_DT TRN_AMT MER_CNTY_CD 
SIC_CD                                   MER_NM TRN_POS_ENT_CD RULE

1 2010-04-28 23:19:15      86         250   7011 HOTEL 
XXXXX                                          K    1

> class(z)

[1] "data.frame"

Any assistance or alternate methodologies would be much appreciated.

Thanks,
Chris



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. Received on Tue 18 May 2010 - 20:50:54 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Tue 18 May 2010 - 21:00:22 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive