Import-CSV to custom object or Delete first line of .csv file

Hi peops,

I've come across a wee problem that I can't get my head around and I was hoping that someone here might be able to help.  I'm a complete newbie to powershell (and scripting generally), but am very excited about the possiblities it appears to be able to offer.

I've managed to create (plagiarised a bit from a web page here and purloined a snippet from a blog there - you know how it goes) a little script that uses Get-WMIObject to retrieve the total space and freespace of all of the hard disk volumes of all the servers (around 20'ish) in my environment (listed in a local file called Server.txt) into a custom object and then uses Export-CSV to write it and the date to a CSV file.

I'd like the script to read in the CSV file when it starts, if it exists, and then append the result to the end of the same CSV file.  If I could schedule it to run regularly, this would allow me as a server administrator to have just one CSV file that I could look at to (possibly using excel) track storage trends on all of my servers at once and enable the possibility of better "storage planning".  However, I've hit upon a problem.

I don't appear to be able to use Import-CSV to import the contents of the CSV directly into a custom object without using the -header parameter.

I can't use the -header parameter for Import-CSV because the column titles for the .CSV file will end up being incorrectly imported as a record of data unless I can find some way to just delete the first line from the file before importing it using Import-CSV.

I would like if possible to avoid having to "perform complex reformatting operations, field by field and record by record on the contents of the .CSV file prior to importing it" because it's not going to take that long for the file to become quite lengthy to read in (20odd servers - 3 or 4 volumes per server - I'm guessing that after a month of running it once a day it may end up taking longer to read-in and reformat the .CSV file than it will take to generate the new information to add to the end of the file.

 

Here is my code so far in between the '#####################'s

#######################################################

$erroractionpreference = "SilentlyContinue"
$objComputers = get-content "$ENV:UserProfile\Servers.txt"
$TrackerFile = "$Env:UserProfile\Desktop\DiskSpaceTracker.csv"
$smtpServer = "mailserver.domain.com"
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$emailFrom = "mailserver.domain.com"
$emailTo = "admin@domain.com"
$subject = "Free Space report:"

if (test-path $TrackerFile -PathType leaf) {$DiskSpaceTracker = Import-CSV -Path $TrackerFile -Header} #Import File into the tracker if the file exists
else {$TrackerCollection = @()} #and if it doesn't exist initialise a collector object that we'll use to create the file later

$body = @() #initialise an object to create the email body

foreach ($Computer in $objComputers)
{
$diskinfo=get-WmiObject Win32_LogicalDisk -filter drivetype=3 -computer "$Computer"
    if ($diskinfo | ? {$_.freespace/1gb -lt 500Gb})
    {
        foreach ($disk in $diskinfo)
{
        $d = Get-Date
        $body = $body + ("Computer Name:", $Computer, "-", "Disk: ", ($disk.volumename), "-", "Total Size:", ($disk.size/1Gb), "-", "Free Space:", ($disk.Freespace/1Gb),"`r")
        # Create Custom Object to hold the details for the current computer
        $myobj = "" | select Date,Computer,Volume_Name,Vol_Size,Vol_Free_Space,Vol_Percentfree
        $myobj.Date = $d
        $myobj.Computer = $Computer
        $myobj.Volume_Name = ($disk.volumename)
        $myobj.Vol_Size = ($disk.size/1Gb)
        $myobj.Vol_Free_Space = ($disk.Freespace/1Gb)
        $myobj.Vol_Percentfree = ( ($disk.Freespace) / ($disk.size) * 100)
        $TrackerCollection += $myobj  #Add the custom object to our collector

       
        Write-Host " " #
        Write-Host "Computer Name:" ($Computer)
        Write-Host "Total Size:" ($disk.size/1Gb)
        Write-Host "Free Space:" ($disk.Freespace/1Gb)   
}
}
    else
{
            Write-Host "Nothing to report for" $Computer
           
           
}
}


$smtp.Send($emailFrom,$emailTo,$subject,$body)
$DiskSpaceTracker += $TrackerCollection  #Add the collector to the tracker and then
$DiskSpaceTracker | Export-Csv "$Env:UserProfile\Desktop\DiskSpaceTracker.csv" -NoTypeInformation #save it back to the original file
Write-Host " Bish Bash Bosh - Done"
###########################################################

Does anyone have any ideas?

In case you're wondering the difference is between the formats of the output for the $DiskTrackerFile variable which we've Exported to CSV:-

"Date","Computer","Volume_Name","Vol_Size","Vol_Free_Space"
"02/04/2009 00:12:20","Server1","DATA","2047.99596786499","932.34595489502"
"02/04/2009 00:12:20","Server1","BLANK_DISK","2047.99729156494","2047.861743927"
"02/04/2009 00:12:20","Server1","SUPPORT","143.44841003418","143.345138549805"...

And the $DiskTrackerFile variable when we've ImportedFrom CSV:-
... Date           : 02/04/2009 00:07:22
Computer       : SERVER4
Volume_Name    : SYSTEM
Vol_Size       : 40.9682273864746
Vol_Free_Space : 28.3496551513672
Date           : 02/04/2009 00:07:22
Computer       : SERVER1
Volume_Name    : DATA
Vol_Size       : 2047.98963165283
Vol_Free_Space : 932.335079193115 ...

Oh and typical newbie forgot to introduce himself... 'Hi all the name's Clay."

I'm confused as to why you can't just leave the -header off the Import-Csv command?

Thanks Mr Green. It's ok I've cracked it now by doing a get-content instead of an import-csv... I don't get the data in the correct format to be able to just drop new objects on to the bottom of the file properly without performing a load of reprocessing on the data in the file... as shown above...

I'm glad you figured something out. It sounds like you still don't have an optimal solution. Here is what I did, let me know in what fashion it doesn't exactly model your situation then we can work on an optimal solution.

 

I started by making a file name C:\Temp\Test.csv. The contents of the file were:

One,Two,Three

1,2,3

4,5,6

7,8,9

 

Next I did these commands at the command line:

 

$foo = Import-Csv c:\temp\test.csv

$foo

One                                            two                                            three
---                                            ---                                            -----
1                                              2                                              3
4                                              5                                              6
7                                              8                                              9

 

$foo += '' | Select-Object One,Two,Three # Add a new item

$foo[3].One = 10
$foo[3].Two = 11
$foo[3].Three = 12 #gave the new item some contents

$foo

One                                            two                                            three
---                                            ---                                            -----
1                                              2                                              3
4                                              5                                              6
7                                              8                                              9
10                                             11                                             12

$foo | Export-Csv -NoTypeInformation -Path c:\temp\test.csv # dump the info back into the csv

$bar = Import-Csv c:\temp\test.csv # Load the info back into a different object

$bar

One                                            two                                            three
---                                            ---                                            -----
1                                              2                                              3
4                                              5                                              6
7                                              8                                              9
10                                             11                                             12

 

 

If I understand what you are doing, then that should hopefully replicate it.

 

 

 

 

Mr Green,

You, sir, are genius.

What I was missing was the means to add a new line directly to the tracker file and I was stumbling because I didn't realise that you cannot add PSCustom objects together in the same way as you can arrays - as in couldn't find a way to replicate the functionality that is there when you create a new custom object such as $foo = @() with an array created from an imported file.

My thanks.

The corrected Code is below - and working beautifully - all I need now is an advanced excel course to crunch the CSV

#######################################

$erroractionpreference = "SilentlyContinue"
$DiskSpaceTracker = $Null #Ensure value of variable has been cleared since last run
$T = Measure-Command {
$d = Get-Date
$FileServer = "\\Server.domain.net"
$objComputers = get-content "$FileServer\Scripts\Code\Servers.txt"
$TrackerFile = "$Fileserver\DiskSpaceTracker.csv"
$smtpServer = "MailServer.domain.net"
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$emailFrom = "spacecheck@ldomain.net"
$emailTo = "admin@domain.net"
$subject = "Freespace report"

if (test-path $TrackerFile -PathType leaf) {
                                            $DiskSpaceTracker = Import-CSV $TrackerFile
                                            $i = $DiskSpaceTracker.Length
                                            } #Import File Line by line into an Array
else    {
        $DiskSpaceTracker = "" | select-object Date,Computer,Volume_Name,Vol_Size,Vol_Free_Space,Vol_Percentfree# # Create a custom Collection Object with the correct headers
        }

$body = @() #initialise an object to create the email body

foreach ($Computer in $objComputers)
{
$diskinfo=get-WmiObject Win32_LogicalDisk -filter drivetype=3 -computer "$Computer"
    if ($diskinfo | ? {$_.freespace/1gb -lt 500Gb})
    {
        foreach ($disk in $diskinfo)
{
       
        $body = $body + ("Computer Name:", $Computer, "-", "Disk: ", ($disk.volumename), "-", "Total Size:", ($disk.size/1Gb), "-", "Free Space:", ($disk.Freespace/1Gb),"`r")
       
        #######################################
        # Create Custom Object to hold the details for the current computer
        #$myobj = "" | select-Object Date,Computer,Volume_Name,Vol_Size,Vol_Free_Space,Vol_Percentfree
        #######################################
        $DiskSpaceTracker += '' | Select-Object Date, Computer, Volume_Name, Vol_Size, Vol_Free_Space, Vol_Percentfree
        $DiskSpaceTracker[$i].Date = $d
        $DiskSpaceTracker[$i].Computer = $Computer
        $DiskSpaceTracker[$i].Volume_Name = ($disk.volumename)
        $DiskSpaceTracker[$i].Vol_Size = ($disk.size/1Gb)
        $DiskSpaceTracker[$i].Vol_Free_Space = ($disk.Freespace/1Gb)
        $DiskSpaceTracker[$i].Vol_Percentfree = ( ($disk.Freespace) / ($disk.size) * 100)
        $i++
             
        Write-Host " "
        Write-Host "Computer Name:" ($Computer)
        Write-Host "Total Size:" ($disk.size/1Gb)
        Write-Host "Free Space:" ($disk.Freespace/1Gb)   
}
}
    else
{
            Write-Host "Nothing to report for" $Computer          
           
}
}

$smtp.Send($emailFrom,$emailTo,$subject,$body)
$DiskSpaceTracker | Export-Csv $TrackerFile -NoTypeInformation #save it back to the original file
}
Write-Host " Bish Bash Bosh - Done in $T"