Technology Tales

Adding line breaks in Excel in a Citrix Windows session on a Mac

Today, I tried connecting to a client system using my Mac Mini. Everything went well, aside perhaps from some resizing, apart from something more crucial: adding a line break in a cell in an Excel spreadsheet. The usual combination of ALT+ENTER was not doing the needful. Instead, I needed to use CMD+OPTION+ENTER, as it would be on a Mac keyboard. Since I use a Windows one inside, it looks like this: WIN+ALT+ENTER. It was only by looking through the options for the Citrix Workspace app that came upon this after being prompted to do so by ChatGPT, itself not supplying the fully correct information that I needed. Now, all I need to do is remember to use the correct keyboard shortcut, and I am away.

A way to survey hours of daylight for locations of interest

A few years back, I needed to get sunrise and sunset information for a location in Ireland. This was to help me plan visits to a rural location with a bus service going nearby, and I did not want to be waiting on the side of the road in the dark on my return journey. It ended up being a project that I undertook using the Julia programming language.

This had other uses too: one was the planning of trips to North America. This was how I learned that evenings in San Francisco were not as long as their counterparts in Ireland. Later, it had its uses in assessing the feasibility of seeing other parts of the Pacific Northwest during the month of August. Other matters meant that such designs never came to anything.

The Sunrise Sunset API was used to get the times for the start and end of daylight. That meant looping through the days of the year to get the information, but I needed to get the latitude and longitude information from elsewhere to fuel that process. While Google Maps has its uses with this, it is a manual and rather fiddly process. Sparing use of Nomintim's API is what helped with increasing the amount of automation and user-friendliness, especially what comes from OpenStreetMap.

Accessing using Julia's HTTP package got me the data in JSON format that I then converted into atomic vectors and tabular data. The end product is an Excel spreadsheet with all the times in UTC. A next step would be to use the solar noon information to port things to the correct timezone. It can be done manually in Excel and its kind, but some more automation would make things smoother.

Solving error code 8000101D in SAS

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.

A cautionary tale regarding Excel spreadsheet sharing

Recently, I encountered a reason to be wary about creating 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. While the default action is to keep thirty days of change tracking, 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 be keeping an eye on things now that I have done this.

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. For example, 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 work, 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 their 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). To make all of this clearer, 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 appears 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.