Proc Import

[kewong]

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!

[art297]

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]

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]

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

[kewong]

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]

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]

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]

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

[kewong]

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]

Kewong: and did that correct the problem?