SAS ACCESS to OLE DB

[Tal]

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?

[DBailey]

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

[Tal]

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

[PGStats]

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";

PG

[PGStats]

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";

PG

[Tal]

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?

[Reeza]

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

[Tal]

yea,you are right

Thanks Reeza

[Tal]

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 ?

[PGStats]

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...

PG

[Tal]

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