I’m just going to lay it out there: sysadmins should use Microsoft Excel more.
I probably will be labeled a traitor and a heathen for this post. It’s okay, I have years of practice having blasphemous opinions on various IT religious beliefs. Do I know how to use the UNIX text tools like sed, awk, xargs, find, cut, and so on? Yes. Do I know how to use regular expressions? Yes. Do I know how to use Perl and Python to manipulate text, and do poor-man’s extract-transform-load sorts of things? Absolutely.
It’s just that I rarely need such complicated tools in my daily work. I often just have a short list of something that I need to turn into a bunch of one-off commands. And many times I’m sharing it with others of varying proficiency, so readability is key. As it turns out, Excel has some very worthwhile text manipulation. Couple that with the ability to import CSV and autofill it’s a pretty decent solution. Let me give you some examples.
First, we need some text to manipulate. In cells A1 through D1 we have Goats, Sheep, Clowns, and Fire. Some people have Alice & Bob, I have goats & sheep.
First, we can concatenate strings very easily in Excel, as well as insert new strings. This is very handy for building commands you can then paste into a CLI, especially for doing one-off sorts of things. We do this with the ampersand, ‘&’.
=C1&” eat “&B1&” that are on “&D1
=”puppet cert sign “&A1&”.domain.com”
Oh, you’re doing something that needs the text in all upper- or lower-case? No problem. We have UPPER() and LOWER() functions. Suck it, /usr/bin/tr.
=UPPER(C1)&” eat “&LOWER(B1)&” that are on “&UPPER(D1)
Maybe we have a list and we need the first or last few characters from each. There’s LEFT() and RIGHT(), which will return a certain number of characters from those sides of the string.
Perhaps you have a list of domain names, and want to grab the first part. We can use FIND() with LEFT() and RIGHT(). We can add or subtract 1 to get what we want.
Maybe we need to do some autofilling, perhaps for a quick way to take some snapshots through VMware’s PowerCLI. I had the list on the left, then incorporated it into a larger command, dragging down to autofill all the names. Copy & paste that into a PowerCLI window and you’re set. Ad-hoc PowerCLI commands on small lists is actually my #1 use case.
=”New-Snapshot -Name Pre-Patch -VM “&A30&” -Confirm:$false”
Autofill automatically adjusts cell references, too, so if you specified A1 and dragged down it’ll use A2, A3, A4, and so on. If that’s not what you want you can preface parts of the reference with a dollar sign, ‘$’, to make it a static reference. I made it completely static with $A$1, but you can do $A1 or A$1, too.
Excel knows how to autofill just about anything ending in a number or a letter sequence. If it doesn’t catch on with one, try selecting two cells, then filling down. And if it really doesn’t catch on just insert a new column, autofill there, then concatenate that column with your others. In a pinch I’ve built BIND DNS zone files in Excel this way.
I think you get the idea. There’s a good reference in the Excel help, too – hit F1 and then search for “text functions.” The “Text Functions (reference)” result will show more commands, like LEN() for string length, MID() for getting substrings from the middle of a cell, SUBSTITUTE() for replacing text, and so on.
Next time you are tempted to assemble a list of commands by hand save yourself time, keystrokes, and potential errors by doing it in Excel instead!
Here’s my sample workbook, too, if you want to look at these examples yourself. Have fun!