In one of my previous blog posts, I had a CSV worth of content in which I wanted to change a specific field of data.

I was racking my brain on how I could best approach this and while working on this, I kept getting a better and cleaner result, but every step approached the issue a bit different, even though the output was as required.

Requirements

I have a CSV file which contains the following headers

1
2
3
4
5
6
7

MachineName
User
Location
Vendor
Model
SerialNumber
PurchaseDate

As mentioned in my earlier blog post, I needed to make sure that my PurchaseDate was in a specific format, so it would be processed correctly by Excel.

I wanted to get an Excel file which had all the above data and either a new column with the correct date format or have PurchaseDate values overridden by the proper value.

Ideally I would have an extra column which would calculate the actual age of the machine, by comparing the PurchaseDate to Today, but I already know a nice way to do this in Excel, so that’s just to play around with.

The basics

Ok, first things first, let’s get some data and declare some variables:

1
2
3
4

$Computers = Import-Csv -Path .\LongList.csv

$template = 'dd-MM-yyyy'
$culture = [System.Globalization.CultureInfo]::InvariantCulture

Simple right?

I want to output the details to NewList.csv , so I need to keep that in mind. Every try will eventually get the following added to the end of it

1

| Export-Csv -NoTypeInformation .\NewList.csv

First try

I had thought to use all data from my first array of objects $Computers and put all the relevant data in to a new object.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

$warranties = @()


foreach ($computer in $computers) {
    $correct_date = [DateTime]::ParseExact($($computer.PurchaseDate),$template,$culture)
    $properties = @{
        MachineName = $computer.MachineName
        User = $computer.User
        Location = $computer.Location
        Vendor = $computer.Vendor
        Model = $computer.Model
        SerialNumber = $computer.SerialNumber
        PurchaseDate = (Get-Date $correct_date -Format d)
    }
    $warranty = New-Object -TypeName PSObject -Property $properties
    $warranties += $warranty
}
$warranties

I start off by creating a blank array called $warranties, which I will later fill up with all required data.

Then I create a new PSObject for each machine in my list called $warranty, which I add to the $warranties array using the +=  assignment operator . It’s rather clunky,  but hey, it gets the job done!

Second shot

But wait, why don’t I just add a new column to my current array and just use that column instead?

1
2
3
4
5
6
7

foreach ($computer in $computers) {
    $correct_date = [DateTime]::ParseExact($($computer.PurchaseDate),$template,$culture)

    $computer | Add-Member -MemberType NoteProperty -Name CorrectDate -Value $correct_date
    $computers += $computer
}
$computers

Wow, that looks a lot cleaner and I don’t even need to define all the previous columns. While in my case this is preferred, there are circumstances when you have TOO much data in your original CSV file and in that case using the First Take solution can be used to clean up the data.

Third time’s a charm

I’m on the right track  now, making my script cleaner, easier and still functional. Just one small tweak should do it:

1
2
3
4
5
6
7

foreach ($computer in $computers) {
    $correct_date = [DateTime]::ParseExact($($computer.PurchaseDate),$template,$culture)

    $computer | Add-Member -MemberType NoteProperty -Name PurchaseDate -Value (Get-Date -Date $correct_date -Format d) -Force
    $computers += $computer
}
$computers

Again using the Add-Member Cmdlet to add a property to my $computer object, but this time I give it the Name of an already existing property, using the -Force parameter to override the current value.

Awesome!

Bonus points up ahead!

As mentioned before, ideally I would also like to have a column added which displays the current age of a machine. In order to do this you need to simply compare today’s date to the PurchaseDate.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

foreach ($computer in $computers) {
    $correct_date = [DateTime]::ParseExact($($computer.PurchaseDate),$template,$culture)

    $span = [DateTime]::Now - (Get-Date -Date $correct_date)
    $Age = New-Object DateTime -ArgumentList $Span.Ticks

    $computer | Add-Member -MemberType NoteProperty -Name PurchaseDate -Value (Get-Date -Date $correct_date -Format d) -Force
    $computer | Add-Member -MemberType NoteProperty -Name Age -Value "$($Age.Year -1) years, $($Age.Month -1) months, $($Age.Day -1) days"
    $computers += $computer
}
$computers

Here I calculate the amount of ticks between Now and PurchaseDate which gives me my age. For some strange reason it increments all values by 1 though, which means that when using this value, you need to subtract 1.

Don’t believe me, give it a shot, but this gives me the information required.

Last but not least, perhaps some people would like to know the formula for in Excel, in case they prefer this:

1

=DATEDIF([@[PurchaseDate]],TODAY(),"Y")&" Years, "&DATEDIF([@[PurchaseDate]],TODAY(),"YM")&" Months, "&DATEDIF([@[PurchaseDate]],TODAY(),"MD")&" Days"

Happy Scripting! 🙂