ODBC works fine in MS Excel, but not in R
odbc, r, rodbc
Solution
Not sure if this is the most correct way to go about this, but this worked for me.
- Go to the spreadsheet in excel with the data imported.
- Click on Data -> Connections
- Double click on the query
- Click on the Definition tab
Copy the Connection string and paste it into R inside the `odbcDriverConnect` function:
myConn <-odbcDriverConnect("DRIVER=SQL Server;SERVER=fooServer;UID=foo_viewer;PWD=1pityDfoo!;APP=Microsoft Office 2010;WSID=foocomputername;DATABASE=DTS")
You can also copy the Command text and paste it into R inside the `sqlQuery` function:
mydata <- sqlQuery(myConn, "SELECT DTS.dts_id, DTS.dts_no, DTS.unit_code, DTS.originator, DTS.doc_type_id, DTS.doc_date, DTS.subject, DTS.remarks, DTS.status, DTS.is_confidential, DTS.created_by, DTS.date_created, DTS.updated_by, DTS.date_updated, DTS.timestamp FROM DTS.dbo.DTS DTS")
Problem
I've set up my ODBC driver so that MS Excel can import the data into a spreadsheet just fine. However, when I try to establish the connection with R, using ``` ch <- odbcConnect(leprosyDHISdb, uid = leprosyDHISid, pwd = leprosyDHISpw) ``` Then I get the error ``` Warning messages: 1: In odbcDriverConnect("DSN=dhis2;UID=dhis2_viewer;PWD=*********") : [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified 2: In odbcDriverConnect("DSN=dhis2;UID=dhis2_viewer;PWD=*********") : ODBC connection failed ``` What could make odbc fail for R, but not for Excel?