topic Reading Excel files without extension? in Microsoft Integration with SAS

Reading Excel files without extension?

WendyT — Mon, 25 Jan 2010 15:56:57 GMT

Good morning folks – I need your advice.

I have a macro that reads a single worksheet out of a group of Excel files that change both in number and content on a regular basis.

Historically, these files have been located on a shared drive. When our laboratory document management system became available, I moved the files in there because they were accessible by the same filename using a UNC path.

Unfortunately, the document management system was recently changed, and direct access is now by URL. To make things more complicated, the files are now stored as a section title and number that has no extension (something like LAB_000000000). The original filename is stored in an Oracle table I can see, so I can get the storage name (dDocName).

I have successfully used a FILENAME URL statement to read a .csv file from the new system, but have not able to find any documentation or examples of FILENAME with LIBNAME when reading an Excel file under Windows. I am running a remote session to a server with SAS 9.2 (TS2M0), and SAS/ACCESS for Oracle and PC File Formats.

To see a single Excel file in our previous document management system, I used the following:

LIBNAME GETIT EXCEL "\\dmsprod2\labs\Project Manager\PM_lightfile_001.xlsx" access=readonly ;
DATA README ;
SET GETIT.'FINAL$'n ;
RUN ;
LIBNAME GETIT CLEAR ; RUN ;

In the new document management system, the file PM_lightfile_001.xlsx corresponds to something like this:
http://ecm.ouragency.com/ecm/idcplg?IdcService=GET_FILE&RevisionSelectionMethod=Latest&dDocName=LAB_000263478

However, I could not get past the extension problem to tell if the URL was a problem or not.

I then went back to the original drive method, and established copies of the same Excel file with and without the .xlsx extension. The version without the extension does not work (log below).

Does anyone have experience with this, or could you please point me to an example or two? Going back to text files is not an option, and I would prefer not to pull all these files back out of the document management system onto a drive.

Thanks for any advice or help you can give me.

Wendy T.

19 %LET NAMO = &data_req.\PM_lightfile_001.xlsx ;
20 libname GETIT excel "&NAMO" access=readonly ;
NOTE: Libref GETIT was successfully assigned as follows:
Engine: EXCEL
Physical Name: \\palfile\sjrx\ES\IRL\IRLDB\data_req\PM_lightfile_001.xlsx
21 DATA README ;
22 SET GETIT.'FINAL$'n ;
23 RUN ;

NOTE: There were 208 observations read from the data set GETIT.'FINAL$'n.
NOTE: The data set WORK.README has 208 observations and 19 variables.
NOTE: DATA statement used (Total process time):
real time 0.13 seconds
cpu time 0.10 seconds

24 LIBNAME GETIT CLEAR ;
NOTE: Libref GETIT has been deassigned.
24 ! RUN ;

25 %LET NAMO = &data_req.\PM_lightfile_001 ;
26 libname GETIT excel "&NAMO" access=readonly ;
ERROR: ERROR: File extension specified is invalid.
ERROR: Error in the LIBNAME statement.
27 DATA README ;
28 SET GETIT.'FINAL$'n ;
ERROR: Libname GETIT is not assigned.
29 RUN ;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.README may be incomplete. When this step was
stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.README was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds

30 LIBNAME GETIT CLEAR ;
WARNING: Libname GETIT is not assigned.
30 ! RUN ;

Re: Reading Excel files without extension?

sbb — Mon, 25 Jan 2010 17:08:16 GMT

It seems to me that now you must read an Oracle table, not an Excel workbook? How have other individual you work with addressed the need to retrieve "objects", given the system change?

Scott Barry
SBBWorks, Inc.

Re: Reading Excel files without extension?

WendyT — Mon, 25 Jan 2010 18:12:12 GMT

Scott-

So sorry I was unclear in my original post.

I am generating a list of filenames that conform to a pattern (PM_lightfile_*.xlsx) to feed a macro that uses that list to open an Excel file, read data from one worksheet, close the Excel file, and add the data to a SAS dataset (until it runs out of filenames).

The rest of the program then massages the data and preps it to be matched with other data I download directly from our in-house Oracle database. All of this takes place as scheduled jobs in the early morning.

I have no problems reading the Oracle table to get the list of storage filenames (dDocName) that correspond to the actual filenames (dOriginalName) I need.

An original filename like 'myfile.xlsx' would actually be stored as something like LAB_12345. If I put in http:/blah blah blah/LAB_12345 in a browser, it opens up the Excel file. If I put in http:/blah blah blah/myfile.xlsx in a browser, it says that the file is unknown. I have absolutely no idea how it happens, but the IR folks assure me that it's internal to the document management software.

Problem #1 is that the storage 'dDocName' (LAB_12345) has no extension, and I can't seem to find a way to get SAS to recognize the Excel file without the extension even though I'm explicitly specifying Excel in the LIBNAME.

Problem #2 is that I can't find if I can use a FILENAME URL substitution in the LIBNAME statement for Excel. Of course, if I can't find a workaround for the lack of extension on the storage filename, this becomes a moot point.

I'm the first person to deal with this, as I set up the UNC path to the old system as a drive mapping on my computer, and when the change was made, the drive would no longer reconnect. There are only a few of us that use the UNC path method to get groups of files, as we have and enforce consistent conventions for filenames. Other folks rely on metadata and the front-end interface to look for a single file.

I was hoping someone here might have run into this lack of extension before and solved the problem.

Thanks for any help you can give me...

Wendy T

Re: Reading Excel files without extension?

sbb — Mon, 25 Jan 2010 19:28:58 GMT

I expect you will need to revise your "fetch/open" process to be more like FTP, but possibly needing to use the URL Access Method instead, then create a local file with the appropriate file-extension.

Scott Barry
SBBWorks, Inc.

SAS Language Reference: Dictionary,
FILENAME Statement, URL Access Method
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a000223242.htm

Re: Reading Excel files without extension?

WendyT — Tue, 26 Jan 2010 16:24:27 GMT

Scott-

Thanks so much for the idea - I hadn't considered ftping files back from the document management system to a drive and reading from there.

Wendy T