[R] -newbie | RODBC import query

[Prof Brian Ripley]

This all depends on the ODBC drivers, and hence on the exact version of 
the non-R software installed.

Note that read.dbf (in package foreign) provides an alternative approach
(but again depends on someone's reading of '.dbf format').


On Mon, 3 Apr 2006, ronggui wrote:

> I just try and it works well for dbase III and dBase IV format
> database,but if the database is dBase II format,some error  comes.I
> hope this help.
>> library(RODBC)
>> import_dat <- odbcConnectDbase(file.choose())
>> (table_list <- sqlTables(import_dat))
>  TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
> 1   C:/temp        <NA>        new      TABLE    <NA>
>> tester <- sqlFetch(import_dat,"new")
>> tester
>    CASE LOC F_SIZE
> 1      3   1      3
> 2      8   1      4
> 3      9   1      2
> 4     10   1      3
> 5     11   1      3
>
>> version
>         _
> platform i386-pc-mingw32
> arch     i386
> os       mingw32
> system   i386, mingw32
> status
> major    2
> minor    2.1
> year     2005
> month    12
> day      20
> svn rev  36812
> language R
>
>
> 2006/4/2, Evan Cooch <evan.cooch at cornell.edu>:
>> 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")
>>
>> 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
>>
>
>
> --
> »ÆÈÙ¹ó
> Deparment of Sociology
> Fudan University
>
>

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595