[R] -newbie | RODBC import query

[Gabor Grothendieck]

On 4/1/06, Evan Cooch <evan.cooch at cornell.edu> wrote:
> Greetings -
>
> After 20+ years of using SAS, for a variety of reasons, I'm using [R]
> for a bunch of things - while I'm getting a pretty good a handling
> [R] for script programming, and statistical analysis, I'm struggling
> with 'pulling data into [R]'. For reasons beyond my control, a number
> of the files I get sent to 'work with' are in Dbase format (*.dbf).
> For another host of reasons, I need to be able to read directly into
> [R] from these files (no using intermediate .CVS or delimited ASCII files).
>
> OK, so after a bit of reading, seems I need to use RODBC (I'm using
> [R] 2.2.1 for Windows, at the moment). But, I can't seem to figure
> out the basics. Suppose the file I need to 'work with' is
> test.dbf  So, I try the following:
>
>  library(RODBC);
>  import_dat <- odbcConnectDbase("c:\documents and
> settings\egc\desktop\test.dbf")

\ is the escape character so if you want a \ you must use \\ like this:

odbcConnectionDbase("c:\\documents and settings\\egc\\desktop\\test.dbf")

or do this:

odbcConnectionDbase(file.choose())




>
> OK, so far so good - well, at least no outright errors gets chunked
> out to the console. Now what? Here's where I get stuck. There is a
> table in the test.dbf file called TEST. But, the following
>
> tester <- sqlFetch(import_dat,"TEST")
>
> blows up - I get the following error message in the console:
>
> Error in odbcTableExists(import_dat, sqtable) :
>         'TEST': table not found on channel
>
> OK - so it doesn't seem to find the table TEST in test.dbf. I tried
> lower-case for TEST (i.e., test), but that doesn't seem to solve the
> problem. OK, so lets pretend I don't know what the table in test.dbf
> is called, and use sqlTables instead:
>
> table_list <- sqlTables(import_dat)
>
> When I then enter table_list in the console, I get
>
> [1] TABLE_CAT   TABLE_SCHEM TABLE_NAME  TABLE_TYPE  REMARKS
> <0 rows> (or 0-length row.names)
>
> Meaning, what? It almost seems that its telling me there is nothing
> in test.dbf. Well, there definitely is (I can open it up in Excel -
> shudder), but, perhaps it is unable to recognize whats there.
>
>
> Suggestions? Apologies if this is easy, or (worse) and FAQ.
>
> Thanks!
>
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
>