BookmarkSubscribeRSS Feed
kewong
Obsidian | Level 7

Hi everyone,

I'm a new SAS user and running into a problem when trying to import an excel file. I'm using SAS 9.1 and import excel 1997-03 file.   Below is my code and the error message I am receiving:

Proc import out = work.abc

                 datafile="P:\pathway\test.xls"

                 dbms=excel replace;

sheet="Sheet1";

run;

ERROR: Connect: Could not find installable ISAM.

ERROR: Error in the LIBNAME statement.

Connection Failed.  See log for details.

Has anyone come across this before? Any potential solutions? I know I can save the file in a CSV format but I would rather import one XLS with 10 tabs, then 10 separate CSV files.

Appreciate the help and thanks in advance!

9 REPLIES 9
art297
Opal | Level 21

One quick thing you could try: assuming you have a more recent version of excel than the one used to create the file, open the file in Excel, resave it, and try to import it again.

Another approach you could try is to import the file using the XLS engine.

kewong
Obsidian | Level 7

Thanks Arthur - appreciate the help! 

I tried re-saving the file and importing using the XLS engine - both options still gave the error mentioned above.

art297
Opal | Level 21

What version of Excel do you have and can you post the file here?

kewong
Obsidian | Level 7

I have Microsoft Excel 2007 - I can't provide you with the actual excel file due to confidentiality.  That being said, this error shows up with ANY excel file.  Even as simple as:

Name   Score

a              2

b              5

b              6

art297
Opal | Level 21

After opening the file in Excel did you try to save it as a newer version of Excel and/or when you saved it did the extension change from xls to xlsx?  According to both SAS and MS, resaving the file should correct the problem.

Can you import the attached file?

kewong
Obsidian | Level 7

I save the file I was using to XLS extension (Microsoft 1997-2003 version) - it did not change from xls to xlsx.

I ran the codes using the file you attached:

Proc import out = work.abc

                 datafile="C:\Documents and Settings\kwong3\Desktop\teststuff.xls"

                 dbms=excel replace;

sheet="Sheet1";

run;

AND

libname xls excel 'C:\Documents and Settings\kwong3\Desktop\teststuff.xls';

Error still pops up.  Thank you for the continued advice, Arthur.  As frustrating as this is, I guess I can work around with saving my XLS to CSV files for now...

art297
Opal | Level 21

I would submit this one to SAS tech support.  If they can find the solution, please post it back here.

kewong
Obsidian | Level 7

Below is the response I got from SAS Tech Support:

This may be caused by a registry key not being present or not registered.   Here is how you deal with this normally.

Usage Note 11012: Error: Trying to establish connection and ISAM errors when importing and exporting PC File Formats

   

When you attempt to import or export PC File Formats such as Microsoft Excel or Microsoft Access files, the following error messages might appear:

Error trying to establish connection.

Could not find installable ISAM.

The error message can appear even when the latest Microsoft Data Access Components (MDAC) have been installed. They appear because the registry entries listed in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines are not pointing to the correct locations. The most common problem is with the Msexcl40.dll where it is pointing to C:\Windows rather than C:\winnt or vice versa.

Other dlls being pointed to incorrectly include msexch40.dll, msrd2x40.dll, msrd3x40.dll, msltus40.dll, mspbde40.dll, mstext40.dll, and msxbde40.dll.

For more information, consult Microsoft Knowledge Base articles 283881 and 209805 at http://www.microsoft.com.

This can also be caused by the MSEXCL40.DLL not being registered. You can check it as follows:

1.Make sure that Msexcl40.dll exists in the system or system32 folder.

2.Open regedit. Make sure that the correct path to Msexcl40.dll is located at:

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

3.If both conditions exist, re-register the DLL using regsrv32.exe:

   “regsrv32 c:\winnt\system32\Msexcl40.dll”

  

  (From Start>Run or command prompt.  You may need to find the location of regsrv32 before running.)

If 1 or 2 are false, you will need to install (or re-install) Office (or at the very least Excel).

art297
Opal | Level 21

Kewong: and did that correct the problem?

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
  • 9 replies
  • 2442 views
  • 3 likes
  • 2 in conversation