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?
Your'e using an Oracle OLEDB provider. It connects to oracle databases. I think the Microsoft Jet driver can connect to an excel file.
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
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
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
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?
You need a dsn entry for each schema or different libnames for each schema.
yea,you are right
Thanks Reeza
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 ?
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
my windows is 64bit.that should not be the problem,huh?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.