I have a friend who runs a small mailing company. He has an upcoming project where he needs to send out a bunch of brochures to various people; in most cases, each envelope he sends out will contain multiple brochures – but not all will contain the same number of brochures. In other words, the postage will be different on each letter.
So he’s already built this great CSV file containing addresses and the needed postage for each address. Problem is, the postage software he uses can’t read the postage from the file – it can only read the address. So he needed to split his one file into seperate files – one output file for each postage rate.
His input file looks like this:
Name,Company,Address1,Address2,City,State,ZIP,Cost,Qty
And he needed his output file to look the same way. Doing this in VBScript isn’t a huge hassle… you read through the file, create a new output file for each rate class you encounter, and write lines out. But it’s not a super-short script, either. So I got to thinking – wouldn’t PowerShell be easier for this?
The beauty of PowerShell, and the thing to remember, is that you almost never have to parse text – you work with objects. Check this out:
$csv = Import-Csv "input.csv"
$rates = $csv | select cost -unique
foreach ($rate in $rates) {
$file = $rate.tostring() + ".csv"
$csv | where { $_.cost -eq $rate.cost } | Export-Csv $file
}
All I had to do was import the CSV file using Import-CSV. This creates a collection of objects, so I can access the Cost column as a property, rather than having to split the delimited string into an array. I then select all the rate classes by using Select-Object to grab unique instances of the cost property. For each rate class, I simply pass in the input CSV data, use Where-Object to filter for rows where the cost equals the current rate class, and send that to Export-CSV. This is an easy 5-line script. And yes, I probably could have done it as a one-liner – I’m not that ambitious. The point is that working with objects beats the heck out of working with text, and PowerShell’s cmdlets – like Select-Object and Where-Object – can do some seriously heavy lifting for you if you use them properly.
