Importing excel file using libname statement

Reply
Occasional Contributor
Posts: 6

Importing excel file using libname statement

[ Edited ]

I wanted to import sheet from excel (xlsx, SAS 9.4) named "TS" using libname  statement, but I got such message:

 


No cell rectangle in range
Requested Input File Is Invalid
ERROR: File EXCELLIB.TS.DATA does not exist.

 

When I use proc import, it works. Also it works when I am changing the sheet name like "TSO". I have other  sheets named AE/IE etc. for which libname works without any problem. Could you please help me to find where from the issue can  come (it is from code, from excel etc. )

Senior User
Posts: 1

Re: Importing excel file using libname statement

Can you share an example of the libname statement that is generating the error?

Occasional Contributor
Posts: 6

Re: Importing excel file using libname statement

[ Edited ]

Sure, please:

 

LIBNAME gx xlsx ".D:\Desktop\Anahit\XX.xlsx" ;

 

data df;
set gx.TS;
run;

 

Just want to mention that this code work perfectly with other sheets of the  same file.

PROC Star
Posts: 7,800

Re: Importing excel file using libname statement

Submit the problem to SAS technical support. I was able to replicate the problem using a simple workbook with three worksheets named TSO, TS and SO. The three worksheets were identical with only two variables x and y named on the first row, followed by values on the 2nd and 3rd rows.  Worked perfectly for TSO and SO, but received the same error message you got when trying to read the TS worksheet.

 

Art, CEO, AnalystFinder.com

 

PROC Star
Posts: 7,800

Re: Importing excel file using libname statement

Unfortunately, the problem appears to be intermittent. The first time I ran the code I got the exact same error message as you did.

 

I had run it on SAS UE using VMWare on Chrome.

 

Interestingly, I got the error message, the log showed that the other two files had imported correctly, but the output menu didn't appear, so I wasn't able to look at the files.

 

Since then, I've been unable to replicate the problem. I even cold booted my system, but now everything imports correctly.

 

In short, this one is confusing! I'd still raise it with Technical Support as our both getting the same error message, to me, is enough evidence that something is wrong.

 

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 6

Re: Importing excel file using libname statement

Hello Art,

 

I found the solution. I removed all hidden names from excel using visual basic and it worked. So problem was related to excel,not SAS. Thank you.

PROC Star
Posts: 7,800

Re: Importing excel file using libname statement

Glad you found a solution but, on the other hand, I'm not sure what the problem is/was.

 

When I first ran your code on a workbook that I created (which didn't have any hidden names), I got the same error as you did.

 

However, that only happened the first time and, since then (importing the same workbook), I haven't been able to replicate the problem. The workbook keeps importing correctly ,, even though I haven't changed it.

 

Art, CEO, AnalystFinder.com

 

 

 

Occasional Contributor
Posts: 6

Re: Importing excel file using libname statement

Before finding the  solution (in my case) , I had written to SAS Technical Support and here their answer:

 

Hello Anahit,

 

When I attempt to replicate this with on the file provided it works as expected:

 

4    libname xl xlsx "C:\testing\7612261489\data.xlsx";

NOTE: Libref XL was successfully assigned as follows:

      Engine:        XLSX

      Physical Name: C:\testing\7612261489\data.xlsx

 

5    data newx;

6    set xl.TS;

7    run;

 

NOTE: The import data set has 26 observations and 10 variables.

NOTE: There were 26 observations read from the data set XL.TS.

NOTE: The data set WORK.NEWX has 26 observations and 10 variables.

NOTE: DATA statement used (Total process time):

      real time           0.02 seconds

      cpu time            0.01 seconds

 

I suspect two possible issues. First, Excel can sometimes have issues with import/export when sheet names use only two or three characters. This is usually not an issue for the XLSX engine you are using. However the core issue is rooted in how ranges, sheet names, and cell references are stored and accessed. It is possible for a range to become mismatched from its sheet or cell(s) and thus cause an error such as this. Second possible issue would be some minor corruption in the original Excel file. When the file is modified or recreated the corruption is corrected and thus the error is resolved. Unfortunately neither case is something we can diagnosis from withing SAS.

 

Now I do have one known issue where this error is reported but it has not been reported in your specific situation. This error is noted in SAS Note 60543 http://support.sas.com/kb/60543 and is fixed in the hot fix referenced in that note. It would not be a bad idea to apply that hot fix to your installation.

 

Ask a Question
Discussion stats
  • 7 replies
  • 353 views
  • 0 likes
  • 3 in conversation