thr3ads.net - R help - [R] -newbie | RODBC import query [Apr 2006]

If this information is useful, please help other people find it:
Share via:

Evan Cooch

2006-Apr-01 17:05 UTC

[R] -newbie | RODBC import query

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!

Gabor Grothendieck

2006-Apr-01 17:14 UTC

head link

[R] -newbie | RODBC import query

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
>

Dieter Menne

2006-Apr-02 14:04 UTC

head link

[R] -newbie | RODBC import query

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

ronggui

2006-Apr-03 13:25 UTC

head link

[R] -newbie | RODBC import query

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

Jeff Newmiller

2006-Apr-04 15:45 UTC

head link

[R] -newbie | RODBC import query

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

Maybe Matching Threads

Search for more seemingly similar threads

R help - Apr 2006 - -newbie | RODBC import query

[R] -newbie | RODBC import query

[R] -newbie | RODBC import query

[R] -newbie | RODBC import query

[R] -newbie | RODBC import query

[R] -newbie | RODBC import query

Maybe Matching Threads