Unable to upload with right format of data from powershell object to csv

Question

question

DatturajPasarge-9824 asked May 12, '22 LimitlessTechnology-2700 answered May 18, '22

Unable to upload with right format of data from powershell object to csv

Hi Folks,

I am trying to fetch the disk space details using Get-WmiObject Win32_volume cmdlet, however able to load the complete data into the powershell object "$CapacityDetails " but unable to load the data in csv format file properly (using Script 1) . I am looking only one header to be generated for first server, instead of having column header for each every server in the loop.

Is it possible to add the whole data using "$Datatable = New-Object System.Data.DataTable" and then insert final records to CSV please? (Tried with another script ( 2nd script), but unable to load the data to $Datatable and then to csv)

Script 1

$ServersFile ="D:\HealthCheck\Diskspace\servers.txt"

$Hosts = import-csv $ServersFile

foreach ($Hostname in $Hosts){

[decimal]$thresholdspace = 10

$CapacityDetails+=Get-WmiObject Win32_volume -ComputerName $Hostname.HostName | select SystemName,Name,

@{n='Size' ;e={"{0:n2}" -f ($_.capacity/1gb)}},

@{n='FreeSpace';e={"{0:n2}" -f ($_.freespace/1gb)}},

@{n='PercentFree';e={"{0:n2}" -f ($.freespace/$.capacity*100)}} |Where-Object { $.Name -ne 'P:\' -and $.Name -NotLike '*' -and $_.Size -ne '0.00' } |FT

}

$CapacityDetails | Export-Csv -Path D:\HealthCheck\Diskspace\Freespace.csv -NoTypeInformation

$CapacityDetails

===============================================================

SCRIPT 2

$ServersFile ="D:\HealthCheck\Diskspace\servers.txt"

$Hosts = import-csv $ServersFile

$Datatable = New-Object System.Data.DataTable

$Datatable.Columns.Add("HostName", "System.String")
$Datatable.Columns.Add("DiskName", "System.String")
$Datatable.Columns.Add("Size", "System.String")
$Datatable.Columns.Add("FreeSpace", "System.String")

$Datatable.Columns.Add("PercentFree", "System.String")

foreach ($Hostnames in $Hosts){

[decimal]$thresholdspace = 10

$DiskDetails=Get-WmiObject Win32_volume -ComputerName $Hostnames.HostName | select SystemName, Name,

@{n='Size' ;e={"{0:n2}" -f ($_.capacity/1gb)}},

@{n='FreeSpace';e={"{0:n2}" -f ($_.freespace/1gb)}},

@{n='PercentFree';e={"{0:n2}" -f ($.freespace/$.capacity*100)}} |Where-Object { $.Name -ne 'P:\' -and $.Name -NotLike '*' -and $_.Size -ne '0.00' } |FT

if($DiskDetails.Length -ne 0)
{

foreach($Disk in $DiskDetails)
{

 $row = $Datatable.NewRow()
           
 $row.HostName = $Disk.SystemName  
 $row.DiskName = $Disk.Name        
 $row.Size = $Disk.Size
 $row.Freespace = $Disk.FreeSpace
 $row.PercentFree = $Disk.PercentFree
    
 $Datatable.Rows.Add($row)

 }

}

$Datatable.Rows | Export-Csv -Path D:\HealthCheck\Diskspace\Freespace.csv #-NoTypeInformation

$Datatable.Rows

Thanks
Dathuraj Pasarge

windows-server-powershell

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NewbieJones-6218 · May 12 at 03:44 PM

Please use the code sample 101010 to upload your code (and please us indents as appropriate).

0 ·

Answer 1 · 2022-05-12T15:41:47.757Z

NewbieJones-6218 answered May 12, '22

In script 1, you have a format-table in there which will break the pipeline and prevent Export-CSV from working correctly.

Same for script 2.

However if this doesn't fix it, consider the following snippet.

Initialise the results first, and export it as an object.
Then you can just pipe into Export-CSV.
It's not exactly the same task, but the principle should be the same.

 $workingDirectory="H:\" #change as appropriate
 $currentDate = date -uformat "%y%m%d-%H%M"
 $logfile = ($workingDirectory+"Deactivatelog-$currentDate.csv")
    
 $Results=@()
    
 $group="groupA"
    
 $users=Get-ADGroupMember -Identity $group |
     Get-ADUser -properties DisplayName, Enabled |
         Where-Object {$_.Enabled -eq "True" -and $_.DisplayName -like "Test*"} |
             Select-Object DistinguishedName, DisplayName, Enabled, @{name="group";expression={$group}}
                 
    
 If ($users) { # if not null
     ForEach ($account in $users) {
         Write-Host Disabling $account.DisplayName
         Disable-ADAccount -Identity $account.DistinguishedName
            
         $Results +=  New-Object -TypeName PSObject -property @{
             DisplayName=$account.DisplayName;
             Group=$account.group}                   
     }
        
     $Results | Export-CSV $logfile -noTypeInformation
    
 } Else {
   Write-Host "No Accounts found"  
 }

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-05-18T07:25:43.35Z

LimitlessTechnology-2700 answered May 18, '22

Hi there,

The main reason why you're seeing this is that Export-Csv expects an object or object[] through the pipeline and you're passing a formatted string instead. This is specified on MS Docs.

Do not format objects before sending them to the Export-CSV cmdlet. If Export-CSV receives formatted objects the CSV file contains the format properties rather than the object properties.

Instead of appending to a CSV which is quite inefficient, unless there is a specific need for this, what you will want to do is collect the results first and then export them.

--If the reply is helpful, please Upvote and Accept it as an answer–

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question