From: Chris Strachan <chris.strac_at_googlemail.com>

Date: Tue, 18 May 2010 21:47:48 +0100

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

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.
*