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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.