[R] Imported tables from Access 2007

[David Winsemius]

(I am attaching your original question so it will be easier for me and  
those who choose to comment further,)
>
>>> Hi all,
>>>
>>> I am very new to R and I'm trying to import data from Microsoft  
>>> Access. So
>>> far, I've managed to do so successfully using the following code:
>>>
>>> testdb <- file.path("c:\Databse.accdb")
>>
>> I am surprised that worked. I was under the impression won needed  
>> to double the back-slashes or use "/"'s. But then I don't use  
>> Access or odbc, so I may be completely off base.
>>
>>> channel2 <- odbcConnectAccess2007(testdb)
>>> data.table <- sqlFetch(channel2,"data")
>>>
>>> This successfully imports a table(?) called "data.table".
>>>
>>> But when I try to run basic stats or manipulate the data I've  
>>> imported, I
>>> can only do so when specifying "data.table", rather than the  
>>> variables
>>> names (like one can do when importing Excel data. For instance, I  
>>> need to
>>> specify:
>>
>> Please post the results of str(data.table)
>>
>> There will almost certainly be variable names. They may need to be  
>> quoted if you have spaces or other special characters in them.
>>
>> -- 
>> David.
>>
>>>
>>> var_3<-data.table[[1]]-data.table[[2]]

Generally one would want to create a new column within the dataframe:

data.table$var_3 <- data.table$'Fish (PCOrd)' - data.table$Location

>>>
>>> or
>>>
>>> lm.1<-lm(data.table[[1]]~data.table[[4]])

Your use of parentheses in the variable names may cause problems I am  
not skilled enough to work around. Such a practice is not typical R  
programming. I would have tried:

lm.1 <- lm( 'Fish (POrf)' ~ 'Treatment Location', data = data.table)

But when I hacked the column names on the lm help page I could not get  
names with parens and or space to work properly.  My advice would be  
that you make your variable names more in conformance to standard R  
practices, i.e no parens , dashes, or spaces in variable names.


>>>
>>> Any way I can change the import process so that R recognizes  
>>> variable
>>> names? Or at least change what I'm importing so that I can simply  
>>> use the
>>> variables names stored in Access? I've tried various combinations of
>>> "columnnames=TRUE" and "rownames=FALSE" with no success (or at least
>>> anything that makes sense).
>>>
>>> Thanks ahead of time for any input or advice...
>>>
>>> Ryan
>
On Mar 18, 2010, at 6:38 PM, Ryan Utz wrote:

> David,
>
> Thanks for responding. You were right; I misplaced a backspace (\)  
> with a
> forward space (/) in my posting. The c:\ doesn't actually work; in  
> my true
> code there is a forward space.
>
> Anyway, the results of str(data) appears as follows:
>
> 'data.frame':   90 obs. of  9 variables:
> $ Fish (PCOrd)          : Factor w/ 90 levels "Fish1","Fish10",..: 1  
> 2 3
> 4 5 6 7 8 9 10 ...
> $ Location              : Factor w/ 10 levels "16","18","24",..: 4 4  
> 4 10
> 10 4 4 4 4 4 ...
> $ Treatment             : Factor w/ 2 levels "RES","UN": 1 1 1 2 2 1  
> 1 1
> 1 1 ...
> $ Treatment Location    : Factor w/ 3 levels "IN","TOP","UN": 2 2 2  
> 3 3 2
> 2 2 2 2 ...
> $ Fish Length (cm)      : num  7.8 6 7.1 6.6 7.2 6.6 6.4 6 6.15  
> 5.7 ...
> $ Fish Weight (g)       : num  5.72 3.27 4.46 3.57 4.78 3.41 3.3 2.3  
> 2.42
> 2.4 ...
> $ Dried contents        : num  0.024 0.0349 0.017 0.0254 0.0456 ...
> $ Combusted contents    : num  0.00203 0.00282 0.00155 0.00226  
> 0.00353 ...
> $ Estimated content mass: num  6.66e-05 2.91e-03 7.45e-03 1.28e-02
> 5.46e-03 ...
>>
>
> Any thoughts now?
>
>

David Winsemius, MD
West Hartford, CT