topic Re: SAS ACCESS to OLE DB in SAS Procedures

SAS ACCESS to OLE DB

Tal — Wed, 30 Jan 2013 21:23:23 GMT

hi guys,

trying to access excel through ole db as i dont have the access to pc files

libname test oledb provider="ORAOLEDB.ORACLE.1"

properties=("data source"='C:\Users\xy\Desktop\zd.xls')

provider_string=" Excel 8.0";

the error message is : tns:could not resolve the connect specifier specified

Any idea what should be changed here?

Re: SAS ACCESS to OLE DB

DBailey — Wed, 30 Jan 2013 22:14:46 GMT

Your'e using an Oracle OLEDB provider. It connects to oracle databases. I think the Microsoft Jet driver can connect to an excel file.

Re: SAS ACCESS to OLE DB

Tal — Thu, 31 Jan 2013 02:31:40 GMT

i actually found lot of articles on google with the above code saying that it should connect me to excel but nothing i tried so far is working

Re: SAS ACCESS to OLE DB

PGStats — Thu, 31 Jan 2013 03:05:42 GMT

It works for me with this syntax:

libname db OLEDB INIT_STRING=

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\xy\Desktop\zd.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=0';Persist Security Info=True";

Re: SAS ACCESS to OLE DB

PGStats — Thu, 31 Jan 2013 03:17:06 GMT

You can read more formats (including .XLSX) with this :

libname db OLEDB INIT_STRING=

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\xy\Desktop\zd.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=0';Persist Security Info=True";

Re: SAS ACCESS to OLE DB

Tal — Thu, 31 Jan 2013 04:13:37 GMT

will try tomorrow.

Thanks

have you guys also tried to connect through SAS to more schemas in Oracle and access the tables/libraries at the same time?

I tried doing so today and i can only access the tables of one schema at a time.

Any thoughts on this to share with me please?

Re: SAS ACCESS to OLE DB

Reeza — Thu, 31 Jan 2013 04:39:55 GMT

You need a dsn entry for each schema or different libnames for each schema.

Re: SAS ACCESS to OLE DB

Tal — Thu, 31 Jan 2013 14:29:09 GMT

yea,you are right

Thanks Reeza

Re: SAS ACCESS to OLE DB

Tal — Thu, 31 Jan 2013 14:35:43 GMT

hey PG

so i copied your syntax but then when i try to ACCESS the file through this library i get errors

libname db OLEDB INIT_STRING="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\xx\Desktop\yyy.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=0';Persist Security Info=True";

proc print data=db.yyy;run;

ERROR: Invalid data set name db.daily.report_jan2_8.

ERROR: File WORK.DAILY.DATA does not exist.

ps.so i think the " db.yyy" was wrong and i did try to access any of the sheets in this excel file

db.'sheet1$'n and it did not work again

Error:

ERROR: Describe error: IColumnsInfo::GetColumnInfo failed. : The Microsoft Access database

engine could not find the object 'sheet1$'. Make sure the object exists and

that you spell its name and the path name correctly. If 'sheet1$' is not a

local object, check your network connection or contact the server administrator.

What i am doing wrong now ?

Re: SAS ACCESS to OLE DB

PGStats — Thu, 31 Jan 2013 16:29:48 GMT

I can see the sheets in SAS Explorer and a command like:

proc print data=db.'Sheet1$'n; run;

gives the expected result. I wonder why the error message you got refers to Microsoft Access database...

Re: SAS ACCESS to OLE DB

Tal — Thu, 31 Jan 2013 18:29:25 GMT

my windows is 64bit.that should not be the problem,huh?