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!
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 >
Gabor Grothendieck <ggrothendieck <at> gmail.com> writes:> > odbcConnectionDbase("c:\\documents and settings\\egc\\desktop\\test.dbf")or use simple forward slashes, which are easier to read and work even under Windows. (Just for the record, looks it was not the problem) Dieter
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") > testerCASE 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
Evan Cooch wrote: [...]> 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")[...]> Suggestions? Apologies if this is easy, or (worse) and FAQ.Without test data, I can't say much. I don't use DBF very often anymore, but I do use ODBC, and I always use ODBC Administrator in the Control Panel to setup a DSN before using a data source. Something to try, anyway. I suspect you won't want to give any hints to R about "Dbase" if you have to do that... it should be treated as a generic data source. -- --------------------------------------------------------------------------- Jeff Newmiller The ..... ..... Go Live... DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live Go... Live: OO#.. Dead: OO#.. Playing Research Engineer (Solar/Batteries O.O#. #.O#. with /Software/Embedded Controllers) .OO#. .OO#. rocks...1k