proc import hangs when reading .xlsx file

[WesBarris]

Here is some sample code that I use to read in an Excel file:

proc import datafile="T:\X21AB100\X2100LIXI.xlsx" out=work.lixi dbms=excelcs replace;

run;

proc print data=lixi;

title 'Test';

run;

Every once-in-a-while the proc import hangs when trying to read a file.  No error message is displayed.

It just hangs until I either kill the SAS window or end the "pcfservice" process.  If I copy the .xlsx file

to a new name and change the pathname in the code, it works.  Once this code hangs on a .xlsx

file no amount of copying or renaming will make sas read that same file out of the same directory.

Is there a way to debug this?  I do not have this file open in Excel while I am trying to run this code.

[art297]

The following might be applicable: http://support.sas.com/kb/40/409.html

[WesBarris]

I have found this article:

http://support.sas.com/kb/35/064.html

It appears that mapped network drives are not supported when using the pcfilesserver which aparently one uses when importing data from .xlsx files.  I tried changing "T:" to the UNC equivalent and the import function worked.  The think that I don't understand is why drive letters work most of the time.  I would have thought that if drive letters were not supported in the pcfilesserver then they would never work.  In my case they work most of the time but sometimes cause SAS to hang.

Does everyone else use UNC paths when importing data from .xlsx files?

[Tom]

Not just when using PC file servers.  I prefer UNC's all the time.  They are much more portable from machine to machine and user to user.

The only time you need to map drives is if you are running an old DOS program.  Wordperfect 5.1 anyone?

[art297]

Tom:  Do you REALLY have to select a Canadian company to pick on?

Wes: A nice and free tool for identifying UNCs was mentioned in the following SAS-L post: http://listserv.uga.edu/cgi-bin/wa?A2=ind0803b&L=sas-l&D=0&P=3585

[Oleg_L]

I have the same problem even with UNC paths.

Oleg.

[Oleg_L]

We have had a track with tech support on this problem several months ago. They have suggested to install some packs. But no success.

Finally, tech support wrote that they have made  tests on their machines and everything works fine.

I have this issue with xls files. I did not imported xlsx files yet.

I am running EG 4.3 (SAS 9.2) on Windows Server x64.

Regards,

Oleg.

[shivas]

Hi,

Try replacing dbms=excel.Hope this helps.

proc import datafile="T:\X21AB100\X2100LIXI.xlsx" out=work.lixi dbms=excel replace;

run;

Thanks,

Shiva

[WesBarris]

I contacted SAS support about this problem.  They said a number of things can cause this.  The first fix we tried appears to have fixed the problem.  The fix was to install the new 2010 ODBC drivers from the link below:

http://www.microsoft.com/download/en/details.aspx?id=13255

After installing this I have not had proc import hang... yet.

[Oleg_L]

We installed these drivers some months ago. No success. Proc import still hangs.

Oleg.

[Ksharp]

Can you use Libname + ODBC to import these xlsx files ?

[Oleg_L]

I can use libname pcfiles but it also hangs every once-in-a-while.