BookmarkSubscribeRSS Feed
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9


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?

10 REPLIES 10
DBailey
Lapis Lazuli | Level 10

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
Pyrite | Level 9 Tal
Pyrite | Level 9

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
Opal | Level 21

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

PG
PGStats
Opal | Level 21

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

PG
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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
Super User

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

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

yea,you are right Smiley Happy

Thanks Reeza

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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 ? Smiley Happy

PGStats
Opal | Level 21

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

PG
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1781 views
  • 1 like
  • 4 in conversation