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.
Tag Archive for Excel
One reason why you cannot merge cells in Excel
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.
Reading data into SAS using the EXCEL and PCFILES library engines
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.
SYLK: File format is not valid
You can see the above message on attempting to open a CSV file in Microsft Excel. The cause is having “ID” as the very first characters in the file (case is important) because that causes a file type misidentification and CSV does not fit the application’s expectations for an SYLK format, an early spreadsheet file type used by Microsoft. More can be found here: http://support.microsoft.com/kb/323626.