Adventures & experiences in contemporary technology
Recently, I encountered the following kind of message when reading an Excel file into SAS using PROC IMPORT:
ERROR: Error opening XLSX file -> xxx-.xlsx . It is either not an Excel spreadsheet or it is damaged. Error code=8000101D
Requested Input File Is Invalid
ERROR: Import unsuccessful. See SAS Log for details.
Naturally, thoughts arise regarding the state of the Excel file when you see a message like this but that was not the case because the file opened successfully in Excel and looked OK to me. After searching on the web, I found that it was a file permissions issue. The actual environment that I was using at the time was entimICE and I had forgotten to set up a link that granted read access to the file. Once that was added, the problem got resolved. In other systems, checking on file system permissions is needed even if the message seems to suggest that you are experiencing a file integrity problem.
Recently, I encountered a reason to be wary about create shared Excel spreadsheets when one ballooned in size. It ended up growing to around 130 megabytes before I tried turning off sharing to see what happened and the size shrunk to under 200 kilobytes. From this, it would appear that the version control information was the cause of the explosion in file size. With that in mind, I set about to looking through the settings to see if there were any that might need optimisation. The default action is to keep thirty days of change tracking but I have this reduced to a single day in order not to be keeping too much. Quite how much, you need to retain is up to you but I will keeping an eye on things now that I have done this.
One handy thing that I didn’t realise that you could do with Excel until the last few months was the ability to share an open workbook between users and collate any changes that are made (it seems that a form of version control is behind this). From what I have seen, Excel seems to manage changes to shared spreadsheets rather well. When you save yours, it adds updates from other users and warns if any edits collide with one another. To activate it in Excel 2003, all that needs doing is for you to go to the Share Workbook entry on the Tools and tick the appropriate checkbox in the resulting dialogue box. In 2007 and 2010, look for the Share Workbook icon in the Review tab on the ribbon to get the same dialogue box popping up.
That’s not to say that it doesn’t have its restrictions though and I have found that the merging of cells is made unavailable but that can be sorted by unsharing and resharing the workbook when no one else is using it. As to why cell merger is switched off by sharing, I have a few ideas. Maybe, they couldn’t make it work reliably (can happen with large software development projects like the creation of a new version of Excel) or decided that it would have consequences for other users that are too inconvenient. Either way, we cannot merge cells in shared workbooks and that’s the way that things are for now. Some may not worry about this though since they reckon that cell merging is undesirable anyway; well, don’t go doing it in any spreadsheet that is likely to be read in by another program or you could cause trouble.
Recently, I had the opportunity to have a look at the Excel library engine again because I need to read Excel data into SAS. You need SAS Access for PC Files licensed for it to but it does simplify the process of getting data from spreadsheets into SAS. It all revolves around setting up a library pointing at the Excel file using the Excel engine. The result is that every worksheet in the file is treated like a SAS dataset even if there names contain characters that SAS considers invalid for dataset names. The way around that is to enclose the worksheet name in single quotes with the letter n straight after the closing quote, much in the same way as you’d read in text strings as SAS date values (’04MAR2010’d, for example). In order to make all of this more, I have added some example code below.
libname testxl excel 'c:\test.xls';
data test;
set testxl.'sheet1$'n;
run;
All of the above does apply to SAS on Windows (I have used it successfully in 9.1.3 and 9.2) but there seems to be a way of using the same type of thing on UNIX too. Again, SAS Access for PC Files is needed as well as a SAS PC Files server on an available Windows machine and it is the PCFILES engine that is specified. While I cannot say that I have had the chance to see it working in practice but seeing it described in SAS Online Documentation corrected my previous misimpressions about the UNIX variant of SAS and its ability to read in Excel or Access data. Well, you learn something new every day.