ODBC Connection to an Excel file

[Leeman]

OKies, Noob here.

I am trying to connect to an excel file using ODBC in Windows XP.

My approach so far (without sucess) is this..

1.Open Administration Tools then Data Sources (ODBC) from Control
Panel
2.Select tab File DSN
3. Press Add button and scroll to "Microsoft Excel Drice
(*.xls,*.xlsx, *.xlsm, *.xlsb)" then press next

This takes me to a blank field that asks for the name of a datasource
I want to connect to. I select the browse button and go to a location,
then I type in the name of the data source e.g. "DataIsHere".

I select next and then finish.

A screen pops up (ODBC Microsoft Excel setup) with two greyed out
fields and a button to select workbook. I press the Select Workbook
button with Version 12.0 selectd also. The fields are still grey out,
i.e.Data Source Name and Description

I press OK

The File DSN field now shows a file DataIsHere.dsn

I press OK

The I go to Rattle, I select Source: ODBC

In DSN field I enter: DataIsHere

I press Enter and get an error message that the ODBC could not connect

The R window shows the following

> library(rattle)
> rattle()
Rattle timestamp: 2011-01-05 11:51:19
Error in sqlTables(crs$odbc) :
first argument is not an open RODBC channel
In addition: Warning messages:
1: package 'RODBC' was built under R version 2.12.1
2: In odbcDriverConnect("DSN=DataIsHere") :
[RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver
Manager] Data source name not found and no default driver specified
3: In odbcDriverConnect("DSN=DataIsHere") : ODBC connection failed
>

What am I doing wrong?

[Graham Williams]

Thanks for the error report.

I haven't tested the RODBC to Excel connection for a while. Might need some attention. I'll try to have a look.

You can also load an Excel file in through the Spreadsheet option on Windows. I've not tested that either for a while, but it used to work.

Regards,
Graham

--
You received this message because you are subscribed to the Google Groups "rattle-users" group.
To post to this group, send email to rattle...@googlegroups.com.
To unsubscribe from this group, send email to rattle-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rattle-users?hl=en.

[Graham Williams]

Just tested the loading of a .xls file using the Spreadsheet option of Rattle (not using a DSN). That worked after I fixed a bug. The data is loaded from the spreadhseet with a tab named "Sheet1" (the default first tab). A couple of screenshots attached.

I'll release a new rattle with the bug fix soon. Let me know if you want an update now and I'll email the latest package for Windows. 

Regards,
Graham