I don't use PowerShell for nearly everything I can, but I really like it. I feel like it's an easier tool than awk and sed for manipulating text files (I hate regexes), particularly CSV and tab-delimited files. It seems like I've done a lot of automating PowerShell scripts for that purpose this summer. It's probably the "wrong" tool for the job, but I understand the features and can whip up something serviceable in a half hour that converts from one layout to another, renames the headers, rearrange columns, whatever else.
$reader = [System.IO.File]::OpenText('C:\exports\data.txt')
$writer = New-Object System.IO.StreamWriter 'C:\exports\dataformatted.csv'
$writer.WriteLine('ReferenceCode,Institution,FirstName,LastName,Gender,EmailAddress,PrimaryPhone,AdditionalPhone,Mobilephone,SMSPhone')
for(;;) {
$line = $reader.ReadLine()
if ($null -eq $line) {
break
}
$data = $line.Split(",")
if ($data[17] -eq "Y"){
$data[6] = ""
$data[7] = ""
}
$data[1] = $data[1].replace("something","otherthing")
if ($data[2] -ne "Administrator") {
$writer.WriteLine('{0},{1},{2},{3},{4},{5},{6},{7},{8},{9}',
$data[0], $data[1], $data[2], $data[3], $data[4], $data[5],
$data[6], $data[7], $data[8], $data[8])
}
}
$reader.Close()
$writer.Close()
Being able to do this on a Mac or Linux system would make it easier for those guys, too. Huzzah.