Friday, February 23, 2007

Command Line Spreadsheets

Here is a little trick I have known about since the days when MS-DOS was king. I figured, why not share this. Yes, it is geeky, and rarely needed, but what the heck?

If you ever need to put together a quick and dirty spreadsheet, but don't have any spreadsheet software on your computer, you can still create the file you need. While you can do this very easily in any text editor, such as Notepad, let's get real dirty and pretend you dont have a text editor either. Like Carl Maulden used to say in the old American Express commercials, "What will you do? What WILL you do?"

Here is what you do. In Windows, go to Start > Run and type in cmd in Windows NT, XP, or Vista. In DOS-based Windows, like Win95, 98, or ME, type in command. This will pop up a DOS or DOS-like command line with a C:\> prompt.

The first step is to type copy con filename.csv. Filename can be any valid file name. I used test2.csv for mine. Then hit Enter.

Next, type each row of your spreadsheet, one line at a time, dividing the columns with commas. End each row by hitting the Enter key. You can even use Excel forumulae by simply keeping track of which row and column correspond to the A1-style layout of Excel. My example here shows such formulae.

After you have typed the last row, hold down CTRL and while doing that, type the letter Z (Zee to us contrary Americans, Zed to the rest of the English-speaking world). This will put the end of file character on the last line (^Z), and return you to the C:\> prompt. You can now close the DOS environment by typing exit and hitting Enter. Be sure to notice which folder or directory you are in, so you can find your file back in Windows.
Now, you can send your spreadsheet to someone with Excel. They can either go find your file icon, and if CSV files are linked to Excel, just double click it. Otherwise, they just open Excel and select File > Open, and navigate to your file. Like magic, you have a spreadsheet. You can now format cells, create graphs, and do all of those wonderful spreadsheet things we all know and love.

No comments: