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.
=LEFT(A1,2)
=RIGHT(C1,4)
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.
=LEFT(A17,FIND(“.”,A17))
=LEFT(A17,FIND(“.”,A17)-1)
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.
=A30&”=”&$A$1&”.domain.com”
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!
Comments on this entry are closed.
Hooray for the quick and dirty daily work tips; a sysadmin’s bread and butter.
Vlookup and Hlookup are also handy for merging data.
Hooray for good bread and butter sysadmin tips.
Check out vlookup() and hlookup() as well.
I use excel a lot. My Favorite function is probably VLOOKUP. Never knew about the & trick I’ve always used the written out CONCATENATE funcion, so thank you for that. But if I have serious text manipulation to do, I use TextWrangler (Mac only). Between the grepable multi-file find and replace, the ability to select boxes of text, the process lines containing, and the process duplicate lines it is my hands down favorite for serious text manipulation. But for Filling down, Excel is still tops.
I tried your excel trick and it worked beautifully. I am going to remember this one.
I totally do this same thing with SQL update and insert statements, one of our old DBAs showed me, and I never even thought to apply it to other scripting languages!
s/Microsoft Excel/a spreadsheet/
Any reasonably modern spreadsheet works just fine. I like Open|Libre-Office Calc, because it works just about everywhere, including all the places where Microsoft Excel works. And it’s dirt cheap.
Although, for simple text manipulation/creation, vim is often faster (if you remember about macros and the various selection modes).
Let’s say I have a toy problem — a directory of files to archive or otherwise process.
cd /path/to/some/dir
mkdir ../ARCHIVED
ls > /tmp/working
vim /tmp/working
:map ^V^P yypkImv ^V^]Ja../ARCHIVED/^V^]A.bak^V^]^V^M
(Seriously, if you use vim, that actually makes sense. Emacs users can come up with something with a few more parenthesis.)
Then I can put the cursor on a file I want to archive, and hit ^P for every file I want archived. I can define other macros for other actions for concurrent evaluation, or redefine my macro for subsequent actions.
When I’m done, I have a file of commands I can run, and an artifact to archive somewhere. And “:map” by itself will tell me what all of the macros I defined were, which can be included in that artifact archive.
However.
If I’m needing to get several other people who need to provide input, on a routine basis, and they aren’t the types to be happy with text files, the spreadsheet approach becomes very useful. I can put that same list of files in the spreadsheet, add a column, shade it light blue, and tell the other folks to put “A” in the blue column if they want that file archived.
Then I can make a tab “ARCHIVED_COMMANDS”, and for every row with an “A” in the action column, have something like (for row n):
=IF(Files.Bn=”A”;concatenate(“mv ‘”; An; “‘ ‘”; concatenate(An;Control.$B$2);”‘”)
Cut-and-paste by whatever means you prefer that formula to a bunch of rows, and you can save yourself some work, collaborate more effectively with folks who live in spreadsheet-land, and have working artifacts to archive.
Or you can write a few lines of perl/awk/sed/whatnot and do the same thing, but now your artifact doesn’t mean anything to the folks who don’t rejoice in your favorite scripting language. But then you don’t have the option of your manager turning it all into a nice little pie chart to use to show how awesome you are.
LibreOffice or even lowly Gnumeric can do this as well.
Hi Bob,
I am an editor with VMUG Compass, the VMware User Group’s quarterly digital publication. Each issue, we like to feature industry bloggers, and I was wondering if you’d be interested in participating in an upcoming issue? Please let me know at aschlosser@vmug.com.
Best,
Alexa