Issue with csv formating

[dbutch1976]

Hi all,

Having a simple issue but can't seem to figure it out. Rather than having my output all on the correct line it's coming out like this:

Datastore,Capacity,FreeSpaceGB,UsedSpaceGB,utilization
Storage1
3023.650391
492.1201172
2531.530273
83.72430494
Storage 2
31.37207031
31.32519531
0.046875
0.149416342

Naturally this makes it impossible to read. Why is the information not aligning with the column headers? Here's the script:

"Datastore,Capacity,FreeSpaceGB,UsedSpaceGB,utilization" > C:\output\Storage.csv
$datastores = Get-Datastore
foreach ($datastore in $datastores) {
# Get the capacity and free space of the datastore
$capacity = $datastore.CapacityGB
$freeSpace = $datastore.FreeSpaceGB
# Calculate the used space in GB
$usedSpace = $capacity - $freeSpace
# Calculate the utilization percentage
$utilization = ($usedSpace / $capacity) * 100
write-host $($datastore.Name) $capacity $freeSpace $usedSpace $utilization
#Write Output to csv
$output =  $($datastore.Name),$capacity,$freeSpace,$usedSpace,$utilization
$output >> C:\output\Storage.csv
}

[LucD]

This line

$output = $($datastore.Name), $capacity, $freeSpace, $usedSpace, $utilization

is interpreted as an array (due to the commas), and those are printed row by row, each row on a new line.

You can use a string with all the variables, and there the commas will be interpreted as literal characters.

$output = "$($datastore.Name), $capacity, $freeSpace, $usedSpace, $utilization"

------------------------------

Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

------------------------------

[Bart van den Donk]

You script is a combination of the "old way" batch kind of working (using > and >>) and working with objects the "new way" powershell like.
To get your scripting skills up to speed you can search for "Powershell in a month of lunches".
The greatest difference is that objects are like living data. A string is not just a string it has the ability to replace charachters itself, give the length from itself. This works in Powershell because it is an active object oriented prompt. Totally different from the old CMD.

# Define the output CSV file
$outputFile = "C:\output\Storage.csv"

# Initialize an empty array to store output data
$results = @()

# Retrieve all datastores
$datastores = Get-Datastore

# Loop through each datastore
foreach ($datastore in $datastores) {
    # Get the capacity and free space of the datastore
    $capacity = $datastore.CapacityGB
    $freeSpace = $datastore.FreeSpaceGB
    
    # Calculate the used space in GB
    $usedSpace = $capacity - $freeSpace
    
    # Calculate the utilization percentage
    $utilization = if ($capacity -ne 0) { ($usedSpace / $capacity) * 100 } else { 0 }
    
    # Create an object for output
    $result = [PSCustomObject]@{
        Datastore   = $datastore.Name
        CapacityGB  = [math]::Round($capacity, 2)
        FreeSpaceGB = [math]::Round($freeSpace, 2)
        UsedSpaceGB = [math]::Round($usedSpace, 2)
        Utilization = [math]::Round($utilization, 2)
    }
    
    # Add the result to the array
    $results += $result
}

# Export results to CSV
$results | Export-Csv -Path $outputFile -NoTypeInformation -Force

Write-Host "Datastore information exported to $outputFile successfully."

[Bart van den Donk]

Seems like you combine old batch way 

> and >>

With the new object way where a string is active data which can do things you ask it to like

"aghjg".length

I would suggest searching for "Powershell in a month of lunches" to get into the groove of objects.

ChatGPT can also help:

# Define the output CSV file
$outputFile = "C:\output\Storage.csv"

# Initialize an empty array to store output data
$results = @()

# Retrieve all datastores
$datastores = Get-Datastore

# Loop through each datastore
foreach ($datastore in $datastores) {
    # Get the capacity and free space of the datastore
    $capacity = $datastore.CapacityGB
    $freeSpace = $datastore.FreeSpaceGB
    
    # Calculate the used space in GB
    $usedSpace = $capacity - $freeSpace
    
    # Calculate the utilization percentage
    $utilization = if ($capacity -ne 0) { ($usedSpace / $capacity) * 100 } else { 0 }
    
    # Create an object for output
    $result = [PSCustomObject]@{
        Datastore   = $datastore.Name
        CapacityGB  = [math]::Round($capacity, 2)
        FreeSpaceGB = [math]::Round($freeSpace, 2)
        UsedSpaceGB = [math]::Round($usedSpace, 2)
        Utilization = [math]::Round($utilization, 2)
    }
    
    # Add the result to the array
    $results += $result
}

# Export results to CSV
$results | Export-Csv -Path $outputFile -NoTypeInformation -Force

Write-Host "Datastore information exported to $outputFile successfully."

[dbutch1976]

Thanks for the tip RE "Powershell in a month of lunches", I'll check that out. The script is also working perfectly, thanks!

[LucD]

I thought your question was about why your code formatted the results in that way.

"Why is the information not aligning with the column headers?"

Also, if the answers provided by ChatGPT are the new reality in this forum, I think I'm out.

------------------------------

Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

------------------------------

[dbutch1976]

Sorry LucD, I think they removed the 'mark answer as helpful' option, and I wanted to give credit for both answers. But you are correct, your answer directly answered my original question, so I have marked that as the best answer.

[Bart van den Donk]

It had no intention to promote ChatGPT.
ChatGPT sometimes simply gives wrong answers (hallucinating).
I also learn from that.
I try to approach everything that comes our way from a broader perspective.
My apologies for the confusion.

[Fborges555]

BVDD.

 Seriously ??????? CGPT???????