WMIC /format - Invalid XSL format (or) file name.

You may see an error when using WMIC /format switch, as in the following example where I wanted to write a list of the installed patches on my computer to a CSV file:

wmic /output:"d:\patches.csv" qfe list full /format:csv

But I got this error: Invalid XSL format (or) file name.

Solution

Copy the XSL files as follows:

from
C:\Windows\system32\wbem\en-us\*.xsl
to
C:\Windows\system32\wbem

Once you've done that, when you run the WMIC command with the /format switch, it will work. I have successfully tested this on a Windows 7 computer.


Reference
https://stackoverflow.com/questions/9673057/wmic-error-invalid-xsl-format-in-windows7

List the members of an Active Directory group

From a Windows computer would you like to see a list of the members of a particular Active Directory (network) group? In this article we'll explore how to do this and how to extract and rearrange the usernames into a conventional vertical list.

List the members of a group

Open a command window - press Win-R, type CMD and press Enter

Enter the command: NET GROUP "your group name" /domain

You should replace "your group name" with the name of the group you wish to see the membership of. Remember that the double quotes " " are important, often Active Directory group names have spaces in them, using the quotes is essential for the group name to be recognised. 

Here's an example:

The result is OK. The users in the group are listed under the dotted line. The only thing that's not good is that they are listed in three columns. This makes it difficult to easily extract these usernames for other uses. For example, you may wish to send an e-mail to everyone in this group or display this list in a report. This problem is especially acute when there are many hundreds of group members.

Create a vertical list of usernames

The aim is to create a list as follows:

RIPLEY

DALLAS

ASH

LAMBERT

KANE

PARKER

BRETT

This is much easier to work with in Excel or Outlook.

I've written a small script using AutoIt. Before you go further, ensure you have AutoIt installed on your computer. It's an excellent scripting language because after you create your script you can compile it to an EXE and use it standalone, without having to install anything on the computer where you use it. More information on AutoIt can be found here:
https://mgxp.blogspot.ch/2013/05/autoit-scripting-language-for-windows.html

The script I've written is called Membership. It runs the above NET GROUP command, the output is written to a text file. That file is read by the script, the names are extracted and written to a new text file in order vertically. 

Source Code

#cs ----------------------------------------------------------------------------
 Membership.au3
 AutoIt Version: 3.3.14.2
 Author:        Michael Gerrard, mgxp.blogspot.ch, February 2018

 Script Function:
 List users who are the members of an AD group to a file.

#ce ----------------------------------------------------------------------------

$group      = "your group name"  ;change to a valid AD group name!
$title      = "Membership"
$inputFile  = @ScriptDir & "\membership.tmp"
$outputFile = @ScriptDir & "\membership.txt"
$n          = 8 ;line counter (starts at 8)
$u          = 1 ;user counter

; Run the NET GROUP command to list the users
RunWait(@ComSpec & ' /c NET GROUP "' & $group & '" /domain > ' & $inputFile, @ScriptDir, @SW_MINIMIZE)

; Open the input file and read the lines
$file  = FileOpen($inputFile, 0) ;open read only
$aFile  = FileReadToArray($file)
If @error Then  ;An error occurred reading the current script file
    MsgBox(16, $title, "There was an error reading the file.")
 Exit
EndIf
FileClose($file)

; Step through the lines and output the usernames to the output file
$file = FileOpen($outputFile, 2) ;create a new file
While $aFile[$n] <> "The command completed successfully." ;loop through the lines

 $aSplit = StringSplit($aFile[$n], " ") ;split the line into users
 For $u = 1 To $aSplit[0] ;step through the possible users

  $ws = StringIsSpace($aSplit[$u]) ;some users are white spaces
  If $ws = 0 Then ;if not white space
   FileWriteLine($file, $aSplit[$u]) ;write the user to the output file
  EndIf
 Next ;loop again to find another user

 $u = 1  ;reset to 1
 $n = $n + 1 ;increment the loop
Wend

FileClose($file)
FileDelete($inputFile) ;delete the temporary input file
Run("notepad " & $outputFile, @ScriptDir)
Exit

Copy and paste the above code into a text file.

Save the text file as Membership.au3

The first line in the code is where a variable is defined for the group name:
$group = "your group name"
Change the text in double quotes to the name of the your group.


Run the script
As long as you have AutoIt installed you can either run the Membership.au3 file or compile it to an EXE for standalone use.

Remember to change the variable $group to the group name of your choice before running or compiling the script.

NOTE: For the script to work it should be in a folder with a path structure without spaces. For example, if it's in D:\VeryImportant\Files that will work fine. If it's in D:\Very Important\Files then it will not work.

When you run the script it will open Notepad with the usernames in a vertical list like this:

From Notepad you can copy/paste the list to Excel, Word or where ever. Also, it is possible from Excel PowerView to query this text file, this can be useful if you wish to match it to other data you may have in a corporate database.


Conclusion
It's a real shame that the NET GROUP command doesn't have the ability to list the names vertically to start with. However, using AutoIt it's possible to get around this and make a simple but effective solution. I hope this is of help to you.

7-Zip 7za command line zip tool

7-zip is an alternative to the native Windows zip function, WinZip or similar program for compressing files. 7-zip is free of charge, reliable and supports many formats. I wrote an article about it back in 2010, here's the link: https://mgxp.blogspot.ch/2010/06/review-7-zip.html

7-zip is primarily a Windows application, with a graphical user interface. Normally that's fine and often it's enough to manage your compressed files. However, sometimes it would be nice to automate the compressing of files. Maybe you want to backup files by just double clicking a file? In this case you can use 7za.exe, a command line program.

This article is an introduction to the 7za.exe command line tool. We'll have a look at how to download it and as an example we'll see how we can use it to backup files to a removable drive.

Download
Browse to https://www.7-zip.org/
Download and install the 7-zip Windows program.

On the same web page, on the Downloads page look for '7z Library, SFXs for installers, Plugin for FAR Manager' - click to download the 7zXXX_extra.7z file.

7z is the native compressed format of 7-zip, you can open it using the 7-zip Windows program you just installed. Extract the files to a folder. In the folder you'll find a file called 7za.exe. You don't need all the other files, you can just copy this one 7za.exe file and use this for your command line projects.


Example: Simple Backup
In this example I have a local D: drive with a folder called 'source' where I have some important files I want to backup. I have a USB flash drive that is drive E.

1. Insert a USB flash drive
2. Copy the 7za.exe file to the USB flash drive (for example)
3. Open a command window (press Win-R, type CMD and press Enter)
4. At the command prompt change to the USB drive where you put the 7za.exe file (in our example I will type E: [Enter] because my USB drive is drive E)

7za u -r "backup" "d:\source\*.*"       [Enter]

Example: backing up files from the local D: drive to a file called "backup" on the E: (USB drive)

The above command will take all the files in D:\source, compress and copy those files to a called backup.7z on my USB flash drive (drive G: in my case). Later if I update files in D:\source I can use the same command to update the backup.7z file. 

Let's have a closer look at the commnad line parameters we used: 

u 

This means to update, only those files that have changed will be added/updated in the 7z file. 

-r

This means to be recursive, it will copy all files from the source folder and sub-folders. 

"backup" 

In our example this will create a compressed archive file. All the files from the source folder will go inside this one compressed file. By default 7za will use its native 7za format, which is recommended. 

"d:\source\*.*" 

The files to backup, combined with the -r parameter it means that any folders/files below this folder will also be included. 

Command File (batch file) 

To make your life easier you can put the command line into a file and save it with the 7za.exe file on your USB drive. Whenever you need to backup those files just double click the command file. 

Open Notepad and enter the following two lines: 

7za u -r "backup" "d:\source\*.*" 

pause 

Save the file as 7zaBackup.cmd 

At any time you can double click 7zaBackup.cmd to run your backup. 

Help 

At the command prompt you can type:

7za -h   [Enter]

...to get a list of parameters. More help is available at https://www.7-zip.org

Reference 

If you are looking for some help with creating self-extracting archives, please see my previous articles specifically on this more advanced subject: 

PART 1 - https://mgxp.blogspot.com/2010/07/create-self-extracting-archive-exe.html 

PART 2 - https://mgxp.blogspot.com/2013/01/create-self-extracting-archive-exe-part.html 

An alternative for backing up files using the command line is RoboCopy. Click the following link to find many articles about this powerful tool: https://mgxp.blogspot.com/search/label/Robocopy

When deleting files - Source Path Too Long

In Windows, when deleting folders/files have you seen this error message:

Source Path Too Long
The source file name(s) are larger than is supported by the file system. Try moving to a location which has a shorter path name, or try renaming to shorter name(s) before attempting this operation.

Windows 8.1 screen shot - it varies depending on your version of Windows.

If copying not deleting?

If you received a similar message when copying files, please see a previous article I wrote on how you may use RoboCopy to help in that scenario:

http://mgxp.blogspot.ch/2015/10/when-copying-files-source-path-too-long.html


Why?
It's due to a limitation of 256 characters of the folder and file name length combined.


Solution
To delete a folder that contains many sub-folders and files with very long file names, there are a few solutions out there including some third-part tools. You could also use the SUBST command to shorten the path but that may not be successful depending on the length of the files/folders you have. However, I've discovered that it's possible to use RoboCopy, the free command line tool that comes with Windows to do the job perfectly!

RoboCopy is for copying files or synchronising folders. I've written a few articles on this tool already - click here for more information. RoboCopy can understand and work with file paths longer than 256 characters. RoboCopy allows you to synchronise folder 'A' with folder 'B'. Imagine that folder 'A' is empty, there are no files in it - if we synchronise that folder with a folder that does have files (folder 'B') then that folder will also be empty. That's the solution. The following is an example of how this works in practice.

Example

I have a folder called 'Folder with very long names'. Inside this folder I have many nested sub-folders with long names. Inside those sub-folders I have many files with very long names. When I try to delete the folder (and sub-folders/files) I get the 'Source Path Too Long' error. 

At the same level of the folder I want to delete 'Folder with very long names', create a new folder called 'Empty':

The folder called 'Empty' will have nothing in it (it should be empty!). 

Open a command window (press Win+R and type CMD [Enter])

At the command prompt change to the same drive/folder where you have the folders 'Empty' and 'Folder with very long names'. 

At the command prompt type the following command:

ROBOCOPY Empty "Folder with very long names" /MIR  [Enter]

The contents of the folder 'Empty' will be mirrored (/MIR parameter) to the folder 'Folder with very long names'. Because the folder 'Empty' has nothing in it, the contents of the destination folder 'Folder with very long names' will end up with nothing in it! Perfect! Now you can remove (delete) the folder yourself without any trouble.

Conclusion

It's very annoying that Windows doesn't properly support folder and file names longer than 256 characters. It's as if they added the functionality and forgot to update all the tools. Even if you try the command line DEL command you can't delete a folder with very long folder/file names. Using RoboCopy is hassle free, it works. Again it has come to the rescue because it can also solve this issue for copying files. 

Command line tools for Windows - from Uwe Sieber

I came across a very useful set of command line tools for use with Windows today:

http://www.uwe-sieber.de/english.html

These commands could be used with AutoIt Scripts. The Drive Tools sections looks especially promising. It's always good to find some new command lines programs, especially standalone exe files!

When copying files - Source Path Too Long

In Windows, when copying files have you seen this error message:

Source Path Too Long

The source file name(s) are larger than is supported by the file system. Try moving to a location which has a shorter path name, or try renaming to shorter name(s) before attempting this operation.

Windows 8.1 screen shot - it varies depending on your version of Windows.

It's due to a limitation of 256 characters of the folder and file name length combined. 

The simplest solution I've found to this issue is to use RoboCopy to copy the files. It ignores the 256 character limit and just copies everything, no questions asked!

From the command line (CMD):
ROBOCOPY [source] [destination] /s  

For more help please have a look at the following article about RoboCopy:

http://mgxp.blogspot.ch/2015/01/robocopy-backup-and-file.html

Robocopy - backup and file synchronisation PART 2

In my first article about Robocopy (click here) I gave an example of how to use the Robocopy command and how to make a simple command file (batch file) to backup (synchronise) a folder.

In this article we will look at how we can automate the use of Robocopy further. This time we'll use AutoIt to control the parameters used and how our backup runs. What's the advantage of using AutoIt over a command file? You can do lots of clever things with AutoIt that would be painful to do with a mere command file.

AutoIt is a free scripting language for Windows. It is easy to learn and creates standalone EXE files. For more on AutoIt click here.


Example Scenario
I want to do the following:

• Backup files to a USB external hard drive (storage that can be detached from the computer is best in case computer dies your backup won't die with it).
• A simple solution where I can double click a file and it will backup, no prompts.
• I don't want to overwrite my backup each time I run it. Imagine if I make a mistake, run the backup, my mistake would be backed up. I need a solution for this.
• A log or report of what's been backed up.
• It should be efficient and fast, only changed files backed up.

Of course your needs maybe different but I hope that at least this article will give you a few ideas as to how you can use Robocopy and AutoIt together to do some amazing things! 

My AutoIt and Robocopy Example Solution

I wrote a script call RoboBackup.au3. I wrote it using AutoIt in the custom SciTE editor available from AutoIt's website.

In the above example scenario I state that I'd like to be able to run the backup multiple times and not overwrite each time. To do this I decided to make my script manage three backups (by default). What this means is that I'll have three folders; backup1, backup2, backup3. Each time the backup script is run it will copy files to each folder consecutively (one by one). Because it's using Robocopy not all the files will be copied each time, only those changed since. However, it won't be since the last backup, but since three backups ago. That's a compromise on my part otherwise the script might've become more complex, I want to keep it as simple as possible. Anyway, the general concept is that you can have a maximum of three backups at any one time. This should cover most situations. 

In the above screen shot you'll see the code as I see it when I writing it. On the left hand side you can see the line numbers. I'll explain line by line what the script does but first a few basics, here's how the colour coding works:

Green - comments, text to explain what the script is doing. Therefore lines 1 to 11 is just for information. 

Black - variables, they also start with a dollar sign $

Blue - functions like IF.

Orange - operators like <>=

Red - text

Yellow - internal 'macros', these start with @ and are like system variables.

--- Line 15

So that the script 'knows' which backup folder (1, 2 or 3) to use there has to be an INI configuration file to hold these settings. Line 15 creates a variable $ini with the name of the file: RoboBackup.ini. The @ScriptDir means the current folder.

--- Line 16

$number is the number of backups folders you want to use. By default there are three. This can be changed though, if you put a different number in the ini file, that number will be read and used instead of 3. 

--- Line 17

$count is used to keep count of which folder was backed up last.

--- Line 19 

Folder to backup - this script will backup one folder but all sub-folders are included. You can specify the folder name in the ini file or it will use the My Documents folder.

--- Line 20
In case the folder to backup has spaces in it, double quotes are added. This is important because the $source variable will be used on the Robocopy command line where any spaces without double quotes " " would cause trouble.

--- Line 21 
The destination is Backup1, Backup2, etc. The number on the end comes from the ini, the $count variable. This means you'll maintain multiple backups.

--- Line 22
The log file is named as RoboBackupLOGx.txt where "x" is the number ($count) of the backup. If you are using the default settings there will only be three log files created. Each time the backup runs the old log file with the same name will be overwritten. I've done it this way so there are not lots of log files created. However, you might prefer to have more comprehensive logging than the simple three log file approach I've used. For example, you could change this line to create a log that has the date in it, meaning that a log file will be created every time the script is run and it will remain until you delete it (it will not be overwritten unless you run the script twice on the same day):

$log = @ScriptDir & "\RoboBackupLOG" & @YEAR & @MON & @MDAY & "_" & $count & ".txt"

The above would create a file name like this: RoboBackupLOG20150803_1.txt
I've put the date backwards, year-month-day, to ensure that all the logs will line up when you look at them in explorer. The last digit is the count, it'll tell you which backup folder to look in.

--- Lines 25 and 26

These lines increment the $count. When the $count is greater than the $number, it resets. This is for naming the backup folders 1, 2, 3 and then going back to 1 and starting again.

--- Lines 29 and 30

This is actually one line. At the end of line 29 you'll see an underscore "_" in AutoIt this means to start a new line. The only purpose here is for readability as it's a long command line so I wanted to split it to two lines. Just imagine it is one long line though. You can see that this is the Robocopy command itself! It's run using the AutoIt function RunWait(). It opens a command window and runs a command. In my script I've done this but minimised the command window. It's nice like that because you can check it if you want. However, if you prefer you can hide it altogether. 

--- Lines 32 to 36

Comments, an explanation of the command line parameters for Robocopy that I used. For line 36, regarding the RoboCopy /XF command, this is to stop RoboCopy coping the desktop.ini file. The Desktop.ini sometimes has a command inside it to show a different folder name. This is the case for My Documents and it can cause confusion. Not including the Desktop.ini means that the folders appear with their real names.

--- Line 39

Open Notepad and display the log file. This will happen at the end and shows you what has happened during this backup. It's the usual output from Robocopy. 

--- Line 40

Exit just closes the script. You don't really need this but usually put it just to show where it ends.

My RoboBackup Script Source Code for Copy/Paste
Here's the same script as shown above in the screen shot. But this time it's text so you can copy/paste it and use it. Feel free you change it :-)

#cs ----------------------------------------------------------------------------

RoboBackup

 AutoIt Version: 3.3.10.2
 Author:         Michael Gerrard

 Script Function:
 Create three backups of the same data using the RoboCopy mirror function

#ce ----------------------------------------------------------------------------


; Set variables
$ini   = @ScriptDir & "\RoboBackup.ini"
$number   = IniRead($ini, "Settings", "Number", "3")  ; number of backups
$count   = IniRead($ini, "Settings", "Count", "1")   ; count of backups (where we are now)

$FolderToBackup = IniRead($ini, "Settings", "FolderToBackup", @MyDocumentsDir)
$source   = '"' & $FolderToBackup & '"'
$dest   = '"' & @ScriptDir & '\backup' & $count & '"'
$log   = @ScriptDir & "\RoboBackupLOG" & $count & ".txt"

; Increment the count
If $count >= $number Then $count = 0
IniWrite($ini, "Settings", "Count", $count + 1)

; Run RoboCopy
RunWait(@ComSpec & " /c ROBOCOPY " & $source & " " & $dest & _
" /MIR /R:0 /LOG:" & $log & " /TEE" & " /XF desktop.ini ", @ScriptDir, @SW_MINIMIZE)

; /MIR = mirror
; /R:0 = retry zero times
; /LOG: = record to a log file
; /TEE = output to the screen too
; /XF desktop.ini = don't copy the desktop.ini file (avoids getting the My Documents folder name)

; Display the log file
Run("notepad " & $log)
Exit

Here's what to do:

• Select the above text, press Ctrl-C to copy it. Switch to your text editor (SciTE) and press Ctrl-V to paste. 
• Make sure you have AutoIt installed. 
• Compile and run the script. 

RoboBackup.ini
The RoboBackup.ini is just a text file. You can create this yourself in Notepad or in any case you'll see it is created by the script the first time you run it. I'll look like this:

[Settings]

count=2

number=3

FolderToBackup=C:\YourData

The count is the variable the script reads and it will change every time you run the script. In my example above it says "2" so the latest backup is number 2. Next it'll be 3 and then 1, etc. This line is generated automatically by the script. 

The number line is optional. I have it set to 3, the default. But you can change this to 4 or 8, whatever. This is the number of backup copies you'd like.

The FolderToBackup line is also optional. By default it'll backup your My Documents folder. If you want to backup something else you can specify the folder here. All sub-folders below it will be included in the backup.

Where to put the files

To use the script put the compiled RoboBackup.exe and the RoboBackup.ini files in the destination drive. For example, if you have a USB flash memory drive or a USB hard disk drive, make a folder called backup on it, copy the files RoboCopy.exe (and RoboBackup.ini) into that folder. Run the script from that folder, it'll create the Backup1, Backup2, etc, folders below that point. This is a nice way of working because it means you can unplug your removable backup drive and put it somewhere safe. The next time you want to backup, plug it in and run RoboBackup.exe. 

IMPORTANT: Disk Space 

The main purpose of this script is to make a number of backup copies. This is so that you don't overwrite your backup each time, that you have a chance to recover files from a previous backup. However, to make this all work you do need to be aware of the disk space required for the backup. I've not included any disk space checks in the script (maybe in version 2!!! ;-)). Therefore look at what you want to backup and make sure your backup drive has enough space to accommodate multiple copies of that data. Also factor in that the data will grow in size as you use it. 

Conclusion

RoboCopy is a powerful command line tool for file/folder synchronisation on its own. Combined with AutoIt you can make a script that provides much more control. Here I've provided just one example and I hope it's given you ideas for how it can be adapted to solve your backup requirements. Often the simplest form of backup is best. Imagine with this, restoring is easy, just copy the files. Also for the backup itself it's easy with few requirements, just one standalone exe file (your script, RoboCopy is included as part of Windows by default) and enough disk space. This is a free solution too!

Disclaimer

The script here is just an example. I do not provide it with any guarantee. I'm not advocating this script be used for anything specific, this article is just to demonstrate what can be done. Use at your own risk! 

Reference

AutoIt 

https://www.autoitscript.com

My first RoboCopy article explaining the Robocopy command itself:

http://mgxp.blogspot.ch/2015/01/robocopy-backup-and-file.html

RoboCopy at Wikipedia
https://en.wikipedia.org/wiki/Robocopy

List installed Windows updates using the command line

To find out which updates have been installed in Windows (7, 8, etc) you can look in Control Panel but if you need a list you can share with someone (for diagnostics help perhaps?) it's not so obvious what to do. Here are a couple of ways of doing this using the command line.

Open the command line - press Win-R, type CMD and press Enter


Method 1
Type the following command:
wmic qfe    [Enter]

You'll see a list of updates that are installed on the computer. They have KB reference numbers, these refer to the Microsoft Knowledge Base articles they are fixing. 

To put this list into a file so you can easily e-mail it, use this command:

wmic qfe  >  kb.txt   [Enter]

A file called kb.txt will be created in the current folder. The prompt shows you which folder this is, typically C:\Users\username\. You can open this file with Notepad or e-mail it.

Method 2

There's an alternative command you can use:

systeminfo   [Enter]

It's really nice because it gives you lots of details about your computer.

WARNING! If you are sharing these details with others be careful, potentially some information could be used by hackers to attack your computer.

Again if you want this data in a file just add the greater than sign and a filename:

systeminfo > systeminfo.txt  [Enter]

Also...
Look at C:\Windows\WindowsUpdate.log, this file contains a list of installed updates.

Conclusion

I hope this little tip has been helpful :-)

Combine two CSV files into one using the Command Line (batch file)

Introduction
Batch files are still useful! Often we need to perform some routine tasks over and over. There are macros, scripting languages (AutoIt) and other solutions - but what about using batch files? In this article I will explain how recently I needed to combine a couple of CSV files regulary.

IMPORTANT: This article is only a guide, the steps in it are specific to my requirements but I hope the principals maybe of use to you!


Scenario
The BI (Business Intelligence) software we use at work output files as CSV (Comma Separated Values). This is great for opening in Excel and using pivot tables with. However, the BI software outputs only one year of budget figures at once. If you want to compare figures from year to year you must run a report for each year you need. You can then add these files together to make one big Excel worksheet. This procedure is not difficult, you can copy/paste the data from one CSV file to the other. However, wouldn't it be great if you could just run a batch file for this to be done for you, automatically?


Think and Plan
Before I did anything I had a good think about what I wanted to do and how I was going to do it. You need to understand each step in the process and think of a way to automate that step in the batch file.

I opened the CSV file in Notepad and took a look at it. The first three lines were title text telling me what the report is - this can be removed as it's not needed. Line four is the header, the column titles. This is important so I must keep it. This means I will need to find a way to delete the first three lines of text from a text file.

Imagine the second CSV file, this also has the first three lines of text to be deleted. But also line four, the header is not needed in the second CSV file because it will be added to the first CSV. This means I also need to delete not three but four lines from the second CSV file.

The last thing to do will be to add the two files together and to check everything is fine.

I decided to rename the CSV files to a.csv and b.csv. That way the batch file will just refer to those file names and in the future I will not have to keep editing the batch file to change the names again.


Delete lines from a text file
The MORE command outputs the contents of a text file. It has a switch /E that means it outputs everything but the first x number of lines. Using this we can create a new CSV file without the first three lines.

MORE /E +3 a.csv > tempa.csv
MORE /E +4 b.csv > tempb.csv

The above code worked for me. The first line removes the first three lines (rows) from the a.csv file. It creates a new file called tempa.csv

The second MORE command removes the first four lines from the b.csv file. A file called tempb.csv is created.


Combine two files
COPY tempa.csv + tempb.csv tempCombined.csv

The above command will copy the two files tempa.csv and tempb.csv into one new file tempCombined.csv.


Delete a blank line
There is a little problem with the tempCombined.csv file - a blank line appears in the middle between the sets of data. If you used the tempCombined.csv file in Excel you'd have a blank line which would show up when using pivot tables. We need to delete the blank line...

TYPE tempCombined.csv | FINDSTR /v "^$" > Combined.csv

The TYPE command outputs the file and the "|" pipes the output into the FINDSTR command. This looks for the string "^$", it means blank line. Output the result to Combined.csv, done!


The Finished Batch File

@echo off
echo.
echo Combine two CSV report files into one
echo.

echo Remove titles rows...
MORE /E +3 a.csv > tempa.csv
MORE /E +4 b.csv > tempb.csv
 
echo.
echo Add the CSV files together
COPY tempa.csv + tempb.csv tempCombined.csv
 
echo.
echo Delete blank rows
TYPE tempCombined.csv | FINDSTR /v "^$" > Combined.csv
 
echo.
echo Delete any previous temporary working files
del temp*.csv
 
echo.
echo Finished!

Copy and paste the above into Notepad and save as Combine2CSV.cmd.


Conclusion
I save my reports as a.csv and b.csv, double click the Combine2CSV.cmd file and it makes Combined.csv! Open that in Excel and it's ready to insert pivot tables. This saves me some time and hassle. I hope it will help you too or at least parts of this article may be of help in what you want to do.

Delete the first lines from a text file using the command line

Introduction
I often use CSV (Comma Separated Values) files that unfortunately have title text on the first three rows - I don't need this. I just need the header row which always starts at row four. In Excel of course I can just delete the unwanted rows but what if I want to do this from the command line? It might be useful if I would like to automated working with CSV files.


Instructions
Open a command line window (CMD).
Type the following command:
MORE /E +3 original.csv > new.csv  [Enter]

The original.csv has three first lines that I want to delete.

The new.csv does not have the first three lines.

Explanation

The +3 parameter means that it will delete the first three lines of text from the file. You can change this to however many lines (rows) you want to delete. The 'original.csv' is your original source file with the lines that need to be deleted. The > means to output to a new file called new.csv. Of course you can use different file names, the names I've used are just examples.

NetWare Command Line Tools

Here is something for those who'd like to reminisce about the days of NetWare! Back in 2003 I won a T-Shirt for these two tips on command lines for NetWare. They can still be found on Novell Cool Solutions. Of course no one is using NetWare any more but anyway, here they are for posterity :-)



Managing Trustee Directory Rights From the Command Line
The RIGHTS command (SYS:Public) has a parameter that's not easy to find documented anywhere.
RIGHTS /group=

For example:
RIGHTS SYS:Public ALL /group=power

('power' is the group object name, you can specify the context if you wish)




Adding Trustee Rights From the Command Line
From the DOS prompt type:
RIGHTS /name=

For example:
RIGHTS SYS:PUBLIC R F /name=.TEST.achmecorp

(TEST = container name)

Wget - FTP command line download

Introduction
A year and a half ago I was looking into a way to automate the downloading of a file from FTP and if the download was interrupted, I wanted some way of resuming the download where it had left off. This is important in situations where you have low or unreliable internet bandwidth. If you are downloading a large file and you are disconnected from the internet for some reason, you don't want to start your download again from the beginning.

Previously I had been using the AutoIt built-in function InetGet(). It uses Internet Explorer's download feature and that works fine in perfect conditions, but if there's one slight interruption, it stops. Crucially there's no resume feature available for InetGet().

Another major requirement was that the solution be free of any cost, so I only looked for open source and freeware products.

At first I did some research on the web that told me that resuming downloads requires the owner of the FTP site to have switched on the capability to resume. This was not what I wanted to hear, that is something out of my control. But I didn't fully believe it either, dedicated FTP software like WS_FTP will resume downloads. Of course a Windows GUI tool was not useful to me, I wanted a command line tool so that I could create an automated download process.

Wget

I came across Wget, an open source program, originally a tool from the Unix/Linux world, a Windows command line version is available and works extremely well! It has a 'continue' feature, this will continue (resume) downloads if broken.

It's a single exe file of about 400K. You can download it from here:

http://nebm.ist.utl.pt/~glopes/wget/
(updated the above URL 27/05/2015)

To use Wget you need to open a command window CMD. Like most command line tools you can get a list of options/parameters at the command line itself, here's what you enter:

wget --help

You can also find a list of command line options/parameters here:

http://www.gnu.org/software/wget/manual/wget.html

Here's an example of using Wget to download a single file from an FTP site where a user name and password is required:

wget -c --ftp-user=michael --ftp-password=abc123 ftp://192.168.0.3/file.zip

What does it all mean? The command wget is followed by four parameters, each with a space between them. The above should be entered as one continuous line (not two lines as it may appear above). Here's an explanation of the parameters I used:

-c

This stands for 'continue', you could use --continue instead of -c, both do the same thing. This will mean that if the download of this file is interrupted, you can issue exactly the same command again later and it'll continue the download where it left off.

--ftp-user=michael

If the FTP site you are accessing requires authentication then you can specify the user name this way. Here I put the user name "michael", this is just an example.

--ftp-password=abc123

This is how to specify the password, the "abc123" is only an example password.

ftp://192.168.0.3/file.zip

Put the FTP address, path and file name. The above is just an example.

The following shows what it would look like for real if you used the above command line (click them for larger images):

The following is a video tutorial by jimmyr.com (not me!) and it's pretty good at showing you the basics:

 

Proxy

Wget has a lot of features to help you download. One I wanted and at first I couldn't find much information on, was downloading from an FTP site when your FTP goes through a proxy. If this is the case, before you run your Wget command line, set the command line (DOS) variable ftp_proxy. For example: 

set ftp_proxy=10.1.1.1:8080

Of course "10.1.1.1:8080" is just an example, you'd need to put whatever your proxy address and port number is here. 

Limit Bandwidth Used

The following is a useful parameter for Wget, especially in low bandwidth scenarios:

--limit-rate=

You can use this to limit the bandwidth used by Wget when downloading. For example:
--limit-rate=6k

The 6k means 6 kilobytes per second, not kilobits per second as you might expect. Kilobits (Kbps) is the usual way of measuring bandwidth. As there are 8 bits to every byte, you can calculate what this is in kilobits by dividing kilobits by 8 to get the number of kilobytes for the limit rate parameter.

Here are some examples:

4k means 32Kbps
6k means 48Kbps
8k means 64Kbps
16k means 128Kbps
32k means 256Kbps
64k means 512Kbps
128k means 1024kbps (1Mbps)

Limiting the bandwidth used doesn't mean Wget will download at that speed exactly, it is the maximum, it won't use any more bandwidth than this limit.

When Wget runs I don't think it hogs all the bandwidth in any case, but this feature really is useful in ensuring it doesn't hog the bandwidth, especially if your internet line has to be used by other services at the same time.

By the way, if you want to check how long it takes for a particular size file to download at a particular speed (kbps), take a look at this website:
http://www.numion.com/calculators/Time.html


Conclusion
Wget is an excellent command line tool. I went on to use it in a number of programs I wrote using AutoIt. Using AutoIt you can create a seamless wrapper for Wget and control what parameters are sent to it depending on the circumstance. Wget has proven reliable, fast and versatile. The above is just one thing it can do, it also supports downloading multiple files and folders, HTTP, HTTPS, as well as FTP. 

Currently Wget doesn't support SFTP, although to do this from the command line, take a look at another great free utility for Windows: WinSCP.

Wget works the same on Windows as it does on Linux. This can be useful if you use Linux at any time, for example, Ubuntu has Wget installed as standard. You can use it with the same command line parameters as described above, it works exactly the same. More interesting features can be found in the manual, a link is listed below.

Links

Wget.exe standalone download:

http://nebm.ist.utl.pt/~glopes/wget/

Wget.exe + DLLs download:

http://gnuwin32.sourceforge.net/packages/wget.htm

(this is the same as the Wget above but you need some DLLs to be in the same folder for it to run)

Wget manual:

http://www.gnu.org/software/wget/manual/wget.html

Wget video tutorial:
http://www.youtube.com/watch?v=jQDyr6_KkCQ

Lots more details about Wget:

http://en.wikipedia.org/wiki/Wget

WinWget - a Windows GUI interface for Wget:

http://www.cybershade.us/winwget/

AutoIt Windows scripting:
http://www.autoitscript.com/

WinSCP (SFTP Windows command line)
http://winscp.net/

Download speed / time / file size calculator:
http://www.numion.com/calculators/Time.html