On Writing Scheduled Tasks for Windows AT Command

Less painful ways to write scheduled tasks for the Windows at command.

• Use absolute paths to reference files or folder because at starts in a default folder, not the folder you are currently using.
• Use caret (^) to escape the redirection characters so that you can combine the output (stdout) and error (stderr) streams to a file.

Here's an example that combines those tips: at 12:00 C:\Temp\Test.cmd ^> C:\Temp\Test.log 2^>^&1.

It is also be useful to dump the at command's environment variables so that you can check if the variables are set correctly (e.g. PATH): at 12:00 cmd /c set ^> D:\Temp\Env.txt.

2013/12/10: With Task Scheduler, you don't need to use the caret to escape the cmd metacharacters, i.e. Program = cmd and Arguments = /c C:\Temp\Test.cmd > C:\Temp\Test.log 2>&1.

Flatten or Collapse Excel Multi-column Data Into One Dimension

You can flatten or collapse multi-column Excel data into one row or column using GnuWin utilities.

1. Copy the worksheet data into the clipboard.
2. Open a CMD window and enter this chain of commands: getclip | tr -s [:cntrl:] \n | putclip.
3. Paste the data back into your worksheet. You should get a column of data.

The getclip-putclip pair of programs gets and puts data in the system clipboard and is part of the CygWin package in GnuWin. tr translates control characters (e.g. TABs) to NEWLINEs and the -s option squeezes out repeated characters. This chain of commands works because each column of Excel data separated by a TAB character in the clipboard. Here are more examples of this pattern.

If you want to remove duplicates in your data, insert sort -u into the chain: getclip | tr -s [:cntrl:] \n | sort -u | putclip. For example, if you start with this input (note the trailing TABs in rows 1 and 2) ...

1  
2 4 
3 5 7
4 6 8
5 7 9

... you end up with the following:

1
2
3
4
5
6
7
8
9

If you want a single row output, replace all control characters with TABs: getclip | tr -s [:cntrl:] \t | putclip. In this case, you cannot include sort because it sorts lines of data and there is only one line in the output.

1 2 4 3 5 7 4 6 8 5 7 9 

Leading Semicolon Comments a Line in CMD FOR /F

I wondered why the following CMD script was not processing some lines ...

for /f %a in (test.txt) do @echo %a

... with this sort of test where the semicolon is a field separator. All the lines were processed when the field separator is a space, tab or comma.

x;y;z
;y;z

When I ran the script, the echo command is only called for the first line. It turns out that, by default, for /f regards any line starting with a semicolon to be a comment and ignores it. It's similar to using:

for /f "eol=;" %a in (test.txt) do @echo %a

Note that the Microsoft documentation doesn't state the default for the eol keyword.

See Also

• For /f - Loop through text
• Microsoft Windows XP - For

Name Log Files in a Consistent Format

I expect developers to program like I do, which didn't happen this week.

The task is to concatenate log files in a folder into a single file to import into a database. The Windows command to do the job is for /f %a in ('dir /b *.csv') do type %a >> dest.csv. (dir /b lists entries in a folder without any heading.) While testing using echo in place of type, I noticed that due to the way dir sorts file, the script would concatenate log files in this order: Log-02.csv, Log-03.csv, ..., Log.csv i.e. the first log file last. This order seems to correspond with the characters in the ASCII table.

(Aside: Windows Explorer lists Log.csv first because Explorer doesn't use ASCII sort order.)

Of course, just to get the job done, the work-around is to rename Log.csv to Log-01.csv before running the script.

It threw me that developers wouldn't name log files consistently.

SetLocal EnableDelayedExpansion in CMD Scripts

In a Windows CMD for loop, variables within the loop aren't, by default, resolved into a value for every iteration of the loop. Here's a script that should list all files in a folder.

@echo off
for /f %%a in (*) do (
  set test=%%a
  echo %test%
)

However, when you run the script, only the first file found is output (the variable test is only set in the first iteration of the loop). Turns out that I'm the n'th generation of CMD programmers to trip over this issue. It seems that CMD only processes the for (...) statement once, so the variable test is only set once. To get CMD to reprocess your variable in each iteration, put your code into a setlocal EnableDelayedExpansion ... endlocal block and delimit with exclamation marks any variables that CMD should reprocess in each iteration of the loop (hence the unintuitive name of the block).

@echo off
setlocal EnableDelayedExpansion
for %%a in (*) do (
  set test=%%a
  echo test=!test!
)
endlocal

I couldn't find an official MS documentation of EnableDelayedExpansion or the use exclamation marks for variables.

What's the time?

14 quick ways to find the current time on your computer.

Cmd.exe has two built-in commands for the date and time. You have to add the /t option when calling these commands otherwise you are prompted to set the system time:

> date /t
Wed 27/08/2008
> time /t
07:42 PM

GnuWin's date command prints the date, time and time zone:

> date
Wed Aug 27 19:43:23 AUS Eastern Standard Time 2008

You can use the POSIX module in Perl to get the current date and time:

> perl -e "use POSIX; print asctime(localtime());"
Wed Aug 27 19:44:21 2008

Python has a time module similar to Perl's:

> python -c "import time; print time.asctime()"
Wed Aug 27 19:48:07 2008

PHP's time and date functions return an array, which you can dump using the print_r() function:

> php -r "print_r(get_date());"
Array
(
    [seconds] => 49
    [minutes] => 34
    [hours] => 14
    [mday] => 30
    [wday] => 6
    [mon] => 8
    [year] => 2008
    [yday] => 242
    [weekday] => Saturday
    [month] => August
    [0] => 1220070889
)

Ruby has a Time class:

> ruby -e "print Time.now"
Wed Aug 27 19:45:32 +1000 2008

PowerShell has a get-date cmdlet:

> get-date
Wednesday, 27 August 2008 7:50:13 PM

Or use the .Net System.DateTime.Now property in PowerShell:

> [System.DateTime]::Now
Thursday, 28 August 2008 9:53:21 AM

Firefox can tell you the time using the Javascript Date() object. Enter the following statement in your browser's address bar:

javascript:Date()
Wed Aug 27 2008 20:11:27 GMT+1000 (AUS Eastern Standard Time)

MSIE6 has a similar object but the output is different from Firefox's:

javascript:Date()
Thu Aug 28 10:06:59 2008

Groovy (and Java) has a java.util.Date object which defaults to the current time:

new java.util.Date()
Result: Thu Aug 28 09:58:45 EST 2008

Match Multiple String Patterns

To find multiple string patterns in an input file or stream, these commands are equivalent:

• sed -n -e "/pattern1/p" -e "/pattern2/p". -n suppresses printing all input lines.
• sed -n -r -e "/pattern1|pattern2/p". -r enables extended regular expressions.
• grep -e "pattern1" -e "pattern2"..
• grep -E "pattern1|pattern2". -E enables extended regular expressions.
• findstr "pattern1 pattern2". You have to delimit the patterns in a single string argument. To find strings containing white spaces, you have to use the \s (whitespace) character class in your pattern.

GnuWin32 find and missing argument for exec

Reminder on how to use -exec action in GnuWin32 find command in Windows cmd.exe. For example, if you want to find a string, the format is:

find . -type f -exec grep <pattern> {} ;

If you do any of the following, you can get this cryptic error message: find: missing argument to `-exec'

• Put double-quote marks around the command:

  find . -type f -exec "grep <pattern> {} ;"

• Don't leave a space between braces and semi-colon:

  find . -type f -exec "grep <pattern> {};"

• Use Unix shell escape character:

  find . -type f -exec grep <pattern> {} \;

Finally, if all else fails and you lack time to investigate, use xargs:

find . -type f | xargs grep <pattern>

Obscure Cmd.exe Output Replacement (Back Tick)

With Unix shells such as bash and [t]csh, you can set the value of a variable to the result of a command using the back-tick operator (or output replacement). For example, LINES = `wc -l filename`, would set the variable LINES with the result of wc -l, which is the number of lines in filename. This technique is useful when you want pass the value of a computed variable to subsequent commands in a script.

Windows' cmd.exe also supports this feature, in a obscure way, using the for command: for /f %i in ('command') do set VARIABLE=%i. To reproduce the previous example in cmd.exe: for /f %i in ('wc -l filename') do set LINES=%i.

Notes:

• Use %%i in a script.
• Use single-quote marks to delimit a command. If you use double-quote marks, for treats the argument in parentheses as a string.

I saw this and other cmd.exe hacks somewhere but I didn't take a note of it. Grr. Remind myself to update this page when I find that site again.

More Uses of Getclip-Putclip

More uses of GnuWin32 / Cygutils tools getclip and putclip using this recipe: getclip | <command chain> | putclip.

• Copy m'th and n'th column of a table from a browser: cut -fm,n.
• Copy columns from Excel and replace tab character with space: tr \t " ".
• Capitalize letters: tr [:lower:] [:upper:]. (Duh! Enter Shift-F3 in Microsoft Word, thanks to Maria H.).
• Remove indentation from e-mail messages: sed "s/> //".
• Remove indentation from source code in Word document: sed -e "s/^ //" (5-May-2008).
• Join lines broken into multiple lines by e-mail clients: dos2unix | tr -d \n. On a Windows system, tr doesn't recognise CR-LF pairs for terminating a line, so you have to convert them to a Unix-style LF using dos2unix first (6-May-2008).
• Another way to join broken lines: tr -d \r\n using escape codes for carriage return and line feed, respectively (11-May-2008).
• Remove formatting from string: getclip | putclip. This is equivalent to Microsoft Word's Paste Special / Unformatted Text. Also to work-around an annoyance in Outlook 2003, were the Edit / Paste Special is disabled when you are responding to an HTML-formatted document (7-May-2008).
• Remove HTML / XML formatting from input: sed -e "s/<[^>]*>//g" (12-Jun-2008).

A second recipe is (for /f %i in ('getclip') do @command %i) | putclip if command cannot be used in a pipeline. Two examples are basename (return name of file in a path string) and dirname (return path string without file name).

2008-05-01: Don't simply list transformations and filters that can be done with GnuWin32 tools, but ones where existing applications (e.g. Excel, Firefox, Outlook or Word) don't have an easy way to achieve a particular action.

2012-06-25: Flatten or collapse Excel multi-column data.

Using Clipboard in the Command Line

GnuWin32 / Cygutils package has two tools for interacting with the Windows clipboard: getclip and putclip. The first copies text from the clipboard to standard output and the second copies text from standard input to the clipboard. These tools are useful when you want to process text from one Windows application before pasting the text into another application, in the following recipe: getclip | <filters> | putclip.

For example, I want to paste all DLL file names in a folder into a document:

1. Navigate to the required folder using 2xExplorer browser.
2. Type Alt+a to select all files.
3. Type Alt+c to copy all file names. 2xExplorer copies the absolute path for each file.
4. Start cmd.exe console.
5. In cmd.exe console, enter: getclip | cut -d\ -fn | grep dll$ | putclip. cut is GnuWin32 tool which selects a column of data given a column delimiter (-d\ defines backslash) and field number (-fn defines column n). grep filters the output to only list files with "dll" in their name.
6. Start editor.
7. Paste the text in the clipboard in destination document.

Of course, you can do the same using Excel:

1. Navigate to the required folder using 2xExplorer browser.
2. Type Alt+a to select all files.
3. Type Alt+c to copy all file names. 2xExplorer copies the absolute path for each file.
4. Start Excel.
5. Paste data in a worksheet column.
6. Select all cells by typing Shift+Space.
7. Open Convert Text to Columns Wizard by typing Alt+d+e.
8. Select Delimited data type by typing Alt+d.
9. Type Alt+n to go to page 2.
10. Select Other delimiter by typing Alt+o, then enter "\" for paths.
11. Type Alt+f run the wizard.
12. Start Auto Filter by typing Alt+d+f+f.
13. Move to filter column using the mouse (no keyboard shortcuts?) then select from the drop down list (Custom …).
14. Select ends width criteria, enter .dll, then press Enter.
15. Move cursor to required column and select it using Control+Space.
16. Copy column by typing Control+C.
17. Start editor.
18. Paste the text in the clipboard in destination document.

The Excel solution has many more steps than the getclip-putclip solution but Excel leads you through to a solution step-by-step. If you're familiar with GNU tools, then getclip-putclip recipe is faster to use and much more extensible.

2008-05-07. I should have remembered that the basename command would output the name of the file without the leading path string. See later article More Uses of Getclip-PutClip about how to use basename in a pipeline.

Strange GnuWin32 Invalid Argument Error Messages

When chaining GnuWin32 commands in Windows cmd.exe, you may encounter strange error messages like this:

> ls | grep
…
ls: write error: Invalid argument

The first command reports a write error but the error is really in the second command after the pipe symbol.

You may also encounter a similar write error if the wrong command is found in your PATH variable. For instance, Windows and GnuWin32 both have a find and sort command which support different command-line options, so depending on the order of directories listed in your PATH variable, one version or the other is used. If you enter the wrong command-line options for these commands, they won't start and cause the command earlier in the chain to report some sort of I/O error.

Poor Man's Which Command For Windows Cmd.exe

The Windows Command Processor (cmd.exe) doesn't seem to have an equivalent to a Unix shell's which command to find the directory in the PATH variable of a given executable. Here's a poor man's version using the for command:

>for /F %i in ("notepad.exe") do @echo %~$PATH:i
C:\WINDOWS\system32\notepad.exe
>for /F %i in ("gawk.exe") do @echo %~$PATH:i
C:\Program Files\GnuWin32\bin\gawk.exe

20-Mar-2008: Added as a macro using doskey:

>doskey which=for %i in ("$1") do @echo %~$PATH:i

Cmd.exe Environment Variables with Colon and Tilde

Some commands in Windows cmd.exe batch files have a leading tilde (~) character or a trailing colon-tilde (:~) pair of characters attached to environment variable names. What's the purpose of these characters?

Trailing Colon-Tilde Pair

You can find more about colon-tilde in the help using set /?. Briefly, you can …

• Slice a string in a variable: %NAME:~s,n where s is the start position (zero-offset) and n is the number of characters. If s is negative, then the offset starts from the right side minus 1 (i.e. -1 refers to the rightmost character). If n is negative, then length - n characters, are extracted.

• Replace a substring with another string in a variable: %NAME:s1=s2% where s1 is substring to be replaced and s2 is the replacement.

Leading Tilde

The leading tilde is used to decompose elements in a batch file parameter formatted as a path, such as the parent directory or file extension. The best reference is Frequently Asked Questions Regarding The Windows 2000 Command Processor, "How do I parse a file name parameter into its' constituent parts?" (sic). Note that you can only use a leading tilde for batch file parameters, not environment variables (!).

4-Mar-08. Another version of the reference is Frequently Asked Questions Regarding The Windows 2000 Command Processor. 09-Sep-02.

Windows Command Selected Text

If I inadvertently click in a Windows cmd window when a batch job is running, the job halts because the window blocks all output while it waits for me to make a selection. The fix, of course, is to just hit the return key and clear the selection. Now I notice that if I start highlighting a selection, the text in the title bar is prepended with Select.

Windows Cmd Variables and For-loop Command

Strange Syntax for Variable Definition

My quickie script to export Oracle tables defined the for command variable using two percent symbols, %%i, instead of the expected %i% in Windows command shell. Even more strange is that only one % is required when the for command is written interactively in the command shell.

Cmd Variables

To demonstrate and understand how variables are defined in cmd, try the following script in a batch file (i.e. save the commands in a file and run it) and interactively (i.e. type in each command). Note that homedrive is an pre-defined environment variable while i is not defined.

echo %homedrive
echo %i
echo %homedrive%
echo %i%
echo %%homedrive%
echo %%i%
echo %%homedrive%%
echo %%i%%

Here's the results of using a batch script:

> echo homedrive
homedrive
> echo i
i
> echo C:
C:
> echo
ECHO is on.
> echo %homedrive
%homedrive
> echo %i
%i
> echo %homedrive%
%homedrive%
> echo %i%
%i%

Here's what happens when you enter these commands one at a time:

> echo %homedrive
%homedrive
> echo %i
%i
> echo %homedrive%
C:
> echo %i%
%i%
> echo %%homedrive%
%C:
> echo %%i%
%%i%
> echo %%homedrive%%
%C:%
> echo %%i%%
%%i%%

When cmd reads a script in batch mode, it always consumes the leading % for each string as it looks for variables or has to escape a % (see %%i). If a variable is found, that string is always replaced, even if it is not defined (see echo %i%).

On the other hand, when cmd processes a command interactively, it only consumes the %'s in a string when that string is delimited by % (compare echo %homedrive and echo %homedrive%) and if that string maps to a variable name (compare echo %homedrive% and echo %i%). Also, % by itself is treated as a literal "%".

For Command

Back to the for command from the start of this article. In a batch script, a variable, %i, for the for command has to be entered as %%i so that cmd will replace %% with just %. In interactive mode, you only need %i because cmd does not regard this string as a variable. So here's the batch script version of a for command …

for %%i in (a, b, c) do echo %%i

… while here's the interactive version of a for command …

for %i in (a, b, c) do echo %i

The for command defines a variable only if a string starts with %. If you use %i%, then cmd replaces it with a value if i is defined (as expected) and you may encounter some other unexpected error depending on the value of i. However, if i is not defined, for command exits with this message:

%i% was unexpected at this time.

Another unexpected limitation of the for command is that a variable can be only one character long. If you try a variable name that is two or more characters long, you will get this …

for %xy in (a, b, c) do echo %xy
%xy was unexpected at this time.

Windows Cmd Oracle Export

A quickie: I had to dump all the data from an Oracle database and e-mail them to a colleague. Not being familiar with backup and restore for Oracle, I decided to just run exp for each of the tables. To get a list of tables in a schema, I executed this query in DbVisualizer, SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = '<schema>', and saved the output to a file, table.txt. Then in Windows cmd, I wrote this script into a file and ran it:

for /f %%i in (table.txt) do exp <connection string> TABLES=%%i FILE=%%i

Windows Hardlinks with Fsutil

Microsoft Internet Explorer 7 (MSIE7) doesn't open .xhtml files; instead it uses Firefox (probably because Firefox is my default browser). MSIE7 behaves that way regardless of whether I use the Open With context menu item in Explorer, open the file using its file browser or drag-and-drop that file into its window. On the other hand, Opera browser will open an .xhtml file. MSIE7's behaviour is a bit annoying because I go out of my way to test my web pages on different browsers.

I worked around this MSIE7 annoyance using NTFS hardlinks (similar to Unix hardlinks). When you create a file, a hardlink (or name) referring to this file is also created. You can create other hardlinks (or names) that refer to the same file using fsutil utility. A file is only deleted when the last hardlink is deleted.

To create a new hardlink called Name2 for an existing file called Name1 using fsutil, open a command shell and enter: fsutil hardlinks create Name2 Name1.

Windows MAC Addresses

There's at least three ways to get MAC addresses for Windows computers:

• Open Network Connections applet from the Control Panel, Device Manager or run ncpa.cpl, then look at the properties of each network adaptor. This method is rather hit-and-miss and depends on the information provided by the drivers. My Intel PRO/Wireless driver shows the MAC address in the Advanced property sheet but the Realtek NIC driver doesn't show the MAC address at all.
• In the command shell, enter ipconfig /all. ipconfig provides a lot of networking information and you have to look for the Physical Address field.
• In the command shell, enter getmac /v. getmac provides just information about network adaptors.

The last method, getmac -v, provides just the required information. If you use the /fo csv option, you can even get the output formatted for a spreadsheet or database.

Why is it useful to know MAC addresses? If you're setting up a wireless router, you can restrict the computers that can use the router by specifying a list of allowed MAC addresses. If your company uses a software license manager such as FlexLM, you may need to provide the MAC address of your users' computers to your software vendor to obtain node-locked licences.

2xExplorer DOS Command

You can start a DOS command in 2xExplorer by hitting the F10 key. However, if you simply hit Enter instead of typing a DOS command, a Cmd window opens in the folder currently displayed in 2xExplorer. Now I can avoid the annoying problem with the Windows cd command.