Random Scripts in Various Languages

In the last few weeks of work, I've come across several different languages to perform different tasks. They were useful, so I've included them below.

PowerShell

The first one is PowerShell. We used it to extract data for a customer. It's a temporary measure - their business intelligence server will be able to do this for them soon. The idea was to automate calling some SQL queries on a server and exporting the results as CSV. Of interest is that I set the culture to get a specific datetime format (I know, I could get the same effect in SQL). Here is some cleansed parts of the script:

$startDate = "11 Oct 2012"
$days = "1"
$prefix = "declare @StartDate datetime, @EndDate datetime
set @StartDate = DATEADD(HH, -10, '$startDate')
set @EndDate = DATEADD(DD, $days, @StartDate)"

$culture = [System.Globalization.CultureInfo]::InvariantCulture.Clone()
$culture.DateTimeFormat.ShortDatePattern = "yyyy-MM-dd"
$culture.DateTimeFormat.LongTimePattern = "HH:mm"
[System.Threading.Thread]::CurrentThread.CurrentUICulture = $culture
[System.Threading.Thread]::CurrentThread.CurrentCulture = $culture

Write-Output "Extract $days day(s) worth of data from ${startDate}:"

Write-Host "Extracting Performance Log data..."
$query = "$prefix

select Column_Names etc."
.....
$data = Custom-Call-SQL $query
Write-Host "Exporting CSV file..."
$data | Export-CSV PerformanceData.csv -encoding ascii -notype
.....

To run a PowerShell script, put it in a file named something like "script.ps1", open a Powershell prompt (should be under Accessories in the Start menu), and enter the script file and path.

AutoHotkey

The next one was an unusual (to me) use for AutoHotkey. I say unusual, because I didn't think AutoHotkey would be great at working with large files, but it was. It combines the whole contents of several files from sub-folders "Day 2" to "Day 7" into one master file. The reason I went to AutoHotkey is that the files were too big for Notepad++. Here is the script:

#NoEnv
#SingleInstance force
SendMode Input
SetWorkingDir %A_ScriptDir%

OutputFile = PerformanceData.csv
Loop, 6
{
  Day := A_Index+1
  From = Day %Day%\%OutputFile%
  CombineLines(From, OutputFile)
}
MsgBox, Finished copying content to %OutputFile%.
Return

CombineLines(fromfile, tofile)
{
  ;; The "to" file is open for the whole loop for efficiency when done this way.  Loop, Read, %fromfile%, %tofile%
  {
    FileAppend, %A_LoopReadLine%`n
  }
}

To run an AutoHotkey script like this one, copy the contents into a file like "script.ahk" and double click it (assuming you have AutoHotkey installed).

Unix Find and Grep

The next one is from my friend Michael Gleeson. It's a Unix command, and it searches the files in a directory for the <string>, and prints the file and matching line to standard output. It is:

find . -type f -print0 | xargs -0 grep <string>

Regular Expressions

The second last one is for (Perl compatible) regular expressions. Notepad++ was updated to use Perl compatible regular expressions as of version 6. Notepad++ can use regular expressions for "Find and Replace". Doing so is a great feature - it gives you great flexibility to not only find bits of text that match specific patterns, but to also intelligently rearrange or replace those bits of text. I used it recently to change date formats in some of the files I generated with the above scripts. The find text was "(\d{2})/(\d{2})/(\d{4})", and the replace text was "\2-\1-\3". The parentheses in the find text create variables you can use in the replacement text. In our case, we have created three variables. They are named \1, \2 and \3. In the replace text, I've swapped the variables around, because I am converting American Month-Day-Year format to an Australian Day-Month-Year format.


Performing a find and replace in Notepad++ with the above terms replaces "02/27/1900" with "27-02-1900", etc.

See also a post on Mark's Speechblog for more on Notepad++'s powerful find and replace (pre version 6). Also, I'm told that a Unix command, sed (stream editor) can do similar things, and a lot more (see here for a great page about sed).

Visual Basic

The last one is from Visual Basic. I use this in an Excel file, to find a single file. It opens a standard file selector dialog. It's a function, so you call it when you want a file. The function returns a Boolean indicating whether a file was successfully selected, and it puts the file name into the ByRef argument. Here it is:

Function GetAFileName(ByRef FileName As String) As Boolean
' Actually browse for a file to import.
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogOpen)
    fd.AllowMultiSelect = False
    GetAFileName = (fd.Show = -1)
    If GetAFileName Then
        FileName = fd.SelectedItems(1)
    End If
    Set fd = Nothing
End Function

Have you got any random scripts of your own? Please share them below.